Dela via

Förstå och lösa blockeringsproblem

  • Artikel

gäller för:Azure SQL DatabaseSQL-databas i Fabric

Artikeln beskriver blockering i Azure SQL Database och Fabric SQL Database och visar hur du felsöker och löser blockering.

Syfte

I den här artikeln refererar termen anslutning till en enda inloggad session i databasen. Varje anslutning visas som ett sessions-ID (SPID) eller session_id i många DMV:er. Var och en av dessa SPID:er kallas ofta för en process, även om det inte är en separat processkontext i vanlig mening. I stället består varje SPID av de serverresurser och datastrukturer som krävs för att betjäna begäranden om en enda anslutning från en viss klient. Ett enskilt klientprogram kan ha en eller flera anslutningar. När det gäller Azure SQL Database finns det ingen skillnad mellan flera anslutningar från ett enda klientprogram på en enda klientdator och flera anslutningar från flera klientprogram eller flera klientdatorer. De är atomiska. En anslutning kan blockera en annan anslutning, oavsett källklient.

Information om hur du felsöker dödlägen finns i Analysera och förhindra dödlägen i Azure SQL Database och Fabric SQL Database.

Anteckning

Det här innehållet fokuserar på Azure SQL Database. Azure SQL Database baseras på den senaste stabila versionen av Microsoft SQL Server-databasmotorn, så mycket av innehållet liknar det även om felsökningsalternativ och verktyg kan skilja sig åt. Mer information om blockering i SQL Server finns i Förstå och lösa problem med SQL Server-blockering. Fabric SQL Database delar många funktioner med Azure SQL Database. Mer information om prestandaövervakning finns i Övervaka SQL-databas i Microsoft Fabric.

Förstå blockering

Blockering är en oundviklig och avsiktlig egenskap hos alla relationsdatabashanteringssystem (RDBMS) med låsbaserad samtidighet. Blockering i en databas i Azure SQL Database sker när en session har ett lås på en specifik resurs och en andra SPID försöker hämta en konfliktlåstyp på samma resurs. Vanligtvis är tidsramen för vilken den första SPID:en låser resursen liten. När den ägande sessionen släpper låset är den andra anslutningen sedan fri att hämta sitt eget lås på resursen och fortsätta bearbetningen. Det här beteendet är normalt och kan inträffa många gånger under en dag utan någon märkbar effekt på systemets prestanda.

Varje ny databas i Azure SQL Database har den läsa incheckade ögonblicksbilder databasinställningen (RCSI) aktiverad som standard. Blockering mellan sessioner som läser data och sessioner som skriver data minimeras under RCSI, som använder radversioner för att öka samtidigheten. Blockering och dödlägen kan dock fortfarande inträffa i databaser i Azure SQL Database eftersom:

  • Frågor som ändrar data kan blockera varandra.
  • Frågor kan köras under isoleringsnivåer som ökar blockeringen. Isoleringsnivåer kan anges i programanslutningssträngar, frågehintareller SET-uttryck i Transact-SQL.
  • RCSI kan inaktiveras, vilket gör att databasen använder delade (S) lås för att skydda SELECT-instruktioner som körs under den läsincheckade isoleringsnivån. Detta kan öka blockering och dödlägen.

isoleringsnivå för ögonblicksbilder är också aktiverad som standard för nya databaser i Azure SQL Database. Isolering av ögonblicksbilder är ytterligare en radbaserad isoleringsnivå som ger konsekvens på transaktionsnivå för data och som använder radversioner för att välja rader som ska uppdateras. Om du vill använda ögonblicksbildisolering måste frågor eller anslutningar uttryckligen ange transaktionsisoleringsnivån till SNAPSHOT. Detta kan bara göras när ögonblicksbildisolering är aktiverat för databasen.

Du kan identifiera om RCSI och/eller ögonblicksbildisolering är aktiverade med Transact-SQL. Anslut till databasen i Azure SQL Database och kör följande fråga:

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

Om RCSI är aktiverat returnerar kolumnen is_read_committed_snapshot_on värdet 1. Om ögonblicksbildisolering är aktiverat returnerar kolumnen snapshot_isolation_state_desc värdet ON.

Varaktigheten och transaktionskontexten för en fråga avgör hur länge låsen hålls och deras effekt på andra frågor. SELECT-instruktioner körs under RCSI-hämtar inte delade (S) lås på data som läseoch blockerar därför inte transaktioner som ändrar data. För INSERT-, UPDATE- och DELETE-instruktioner hålls låsen under utförandet av frågan, både för datakonsistens och för att tillåta att frågan rullas tillbaka om det behövs.

För frågor som körs inom en explicit transaktionbestäms typen av lås och den tid låsen hålls av typen av fråga, transaktionsisoleringsnivån och om låshints används i frågan. En beskrivning av låsning, låstips och transaktionsisoleringsnivåer finns i följande artiklar:

När låsning och blockering kvarstår till den punkt där det finns en skadlig effekt på systemets prestanda beror det på någon av följande orsaker:

  • En SPID innehåller lås på en uppsättning resurser under en längre tid innan de släpps. Den här typen av blockering löser sig över tid men kan orsaka prestandaförsämring.

  • En SPID innehåller lås på en uppsättning resurser och släpper dem aldrig. Den här typen av blockering löser inte sig själv och förhindrar åtkomst till de berörda resurserna på obestämd tid.

I det första scenariot kan situationen vara mycket flytande eftersom olika SPID:er orsakar blockering på olika resurser över tid, vilket skapar ett rörligt mål. Dessa situationer är svåra att felsöka med hjälp av SQL Server Management Studio för att begränsa problemet till enskilda frågor. Den andra situationen resulterar däremot i ett konsekvent tillstånd som kan vara enklare att diagnostisera.

Optimerad låsning

Optimerad låsning är en ny databasmotorfunktion som drastiskt minskar låsminnet och antalet lås som krävs samtidigt för skrivningar. Optimerad låsning använder två primära komponenter: transaktions-ID (TID) låsning (används även i andra radversionsfunktioner) och lås efter kvalificering (LAQ). Det kräver ingen extra konfiguration.

Den här artikeln gäller för närvarande beteendet för databasmotorn utan optimerad låsning.

För mer information och för att lära dig var optimerad låsning finns tillgänglig, se Optimerad låsning.

Applikationer och blockering

Det kan finnas en tendens att fokusera på justering på serversidan och plattformsproblem vid blockeringsproblem. Men att bara uppmärksamma databasen kanske inte leder till en lösning och kan absorbera tid och energi som är bättre inriktad på att undersöka klientprogrammet och de frågor som skickas. Oavsett vilken synlighetsnivå programmet exponerar för de databasanrop som görs kräver ett blockeringsproblem ofta både kontroll av de exakta SQL-instruktioner som skickas av programmet och programmets exakta beteende när det gäller frågeavbokning, anslutningshantering, hämtning av alla resultatrader och så vidare. Om utvecklingsverktyget inte tillåter explicit kontroll över anslutningshantering, frågereducering, tidsgräns för frågor, resultathämtning och så vidare kanske blockeringsproblem inte kan lösas. Den här potentialen bör undersökas noggrant innan du väljer ett programutvecklingsverktyg för Azure SQL Database, särskilt för prestandakänsliga OLTP-miljöer.

Var uppmärksam på databasens prestanda under design- och byggfasen av databasen och programmet. I synnerhet bör resursförbrukning, isoleringsnivå och transaktionssökvägslängd utvärderas för varje fråga. Varje fråga och transaktion ska vara så enkel som möjligt. God disciplin i anslutningshantering måste utövas. Utan den kan programmet verka ha acceptabel prestanda vid ett lågt antal användare, men prestandan kan försämras avsevärt när antalet användare skalar uppåt.

Med rätt program- och frågedesign kan Azure SQL Database stödja tusentals samtidiga användare på en enda server, med liten blockering.

Not

Mer vägledning för programutveckling finns i Felsöka anslutningsproblem och andra fel och tillfälliga felhantering.

Felsöka blockeringar

Oavsett vilken blockeringssituation vi befinner oss i är metoden för felsökning av låsning densamma. Dessa logiska separationer är det som avgör resten av sammansättningen av den här artikeln. Konceptet är att hitta huvudblockeraren och identifiera vad frågan gör och varför den blockerar. När den problematiska frågan har identifierats (dvs. vad som håller lås under den längre perioden) är nästa steg att analysera och avgöra varför blockeringen sker. När vi har förstått varför kan vi sedan göra ändringar genom att göra om frågan och transaktionen.

Steg i felsökning:

  1. Identifiera huvudblockeringssessionen (huvudblockerare)

  2. Hitta frågan och transaktionen som orsakar blockeringen (vad som håller lås under en längre period)

  3. Analysera/förstå varför den långvariga blockeringen inträffar

  4. Lösa blockeringsproblem genom att designa om frågor och transaktioner

Nu ska vi dyka ner i att diskutera hur man identifierar den huvudsakliga blockeringssessionen med en lämplig datainsamling.

Samla in information om blockeringar

För att motverka svårigheten att felsöka blockeringsproblem kan en databasadministratör använda SQL-skript som ständigt övervakar tillståndet för låsning och blockering i databasen i Azure SQL Database. För att samla in dessa data finns det i princip två metoder.

Den första är att köra frågor mot dynamiska hanteringsobjekt (DMOs) och lagra resultaten för jämförelse över tid. Vissa objekt som refereras i den här artikeln är dynamiska hanteringsvyer (DMV:er) och vissa är dynamiska hanteringsfunktioner (DMF:er). Den andra metoden är att använda XEvents för att samla in vad som körs.

Samla in information från DMV:er

Använda DMV för att felsöka blockering har som mål att identifiera SPID (sessions-ID) i början av blockeringskedjan och SQL-satsen. Leta efter drabbade SPIDs som blockeras. Om någon SPID blockeras av en annan SPID undersöker du SPID som äger resursen (den blockerande SPID). Blockeras den där ägarens SPID också? Du kan gå igenom kedjan för att hitta huvudblockeraren och sedan undersöka varför den behåller låset.

Kom ihåg att köra vart och ett av dessa skript i måldatabasen i Azure SQL Database.

  • Kommandona sp_who och sp_who2 är äldre kommandon för att visa alla aktuella sessioner. DMV-sys.dm_exec_sessions returnerar mer data i en resultatuppsättning som är enklare att köra frågor mot och filtrera. Du hittar sys.dm_exec_sessions i kärnan av andra frågor.

  • Om du redan har identifierat en viss session kan du använda DBCC INPUTBUFFER(<session_id>) för att hitta det sista uttrycket som skickades av sessionen. Liknande resultat kan returneras med funktionen sys.dm_exec_input_buffer dynamisk hantering (DMF), i en resultatuppsättning som är enklare att fråga efter och filtrera, vilket ger session_id och request_id. Om du till exempel vill returnera den senaste frågan som skickats av session_id 66 och request_id 0:

SELECT * FROM sys.dm_exec_input_buffer (66,0);

  • Se kolumnen blocking_session_id i sys.dm_exec_requests. När blocking_session_id = 0 blockeras inte en session. Även om sys.dm_exec_requests endast visar begäranden som körs för närvarande visas alla anslutningar (aktiva eller inte) i sys.dm_exec_sessions. Skapa den här gemensamma kopplingen mellan sys.dm_exec_requests och sys.dm_exec_sessions i nästa fråga.

  • Kör den här exempelfrågan för att hitta frågor som körs aktivt och deras aktuella SQL-batchtext eller indatabufferttext med hjälp av sys.dm_exec_sql_text eller sys.dm_exec_input_buffer DMV:er. Om data som returneras av fältet text i sys.dm_exec_sql_text är NULL körs inte frågan för närvarande. I så fall innehåller fältet event_info i sys.dm_exec_input_buffer den sista kommandosträngen som skickades till SQL-motorn. Den här sökfrågan kan också användas för att identifiera sessioner som blockerar andra sessioner, inklusive en lista över session_ids som är blockerade per session_id.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;
  • Kör den här mer detaljerade exempelfrågan, som tillhandahålls av Microsoft Support, för att identifiera chefen för en blockeringskedja för flera sessioner, inklusive frågetexten för de sessioner som ingår i en blockeringskedja.
WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id 
    )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)
    UNION ALL
    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;

SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];
  • Referens sys.dm_os_waiting_tasks som finns i SQL:s tråd-/uppgiftslager. Detta returnerar information om vilken SQL-väntetyp som begäran för närvarande upplever. Precis som sys.dm_exec_requestsreturneras endast aktiva begäranden av sys.dm_os_waiting_tasks.

Note

Mer information om väntetyper, inklusive aggregerad väntestatistik över tid, finns i DMV-sys.dm_db_wait_stats. Denna DMV returnerar aggregerad väntestatistik endast för den aktuella databasen.

  • Använd sys.dm_tran_locks DMV för information i detalj om vilka lås som har placerats av sökfrågor. Den här DMV:en kan returnera stora mängder data i en produktionsdatabas och är användbar för att diagnostisera vilka lås som för närvarande finns.

På grund av INNER JOIN på sys.dm_os_waiting_tasksbegränsar följande fråga resultatet från sys.dm_tran_locks endast till för närvarande blockerade begäranden, deras väntestatus och deras lås.

SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = '<table_name>';
  • Med DMV:er ger lagring av frågeresultat över tid datapunkter som gör att du kan granska blockering under ett angivet tidsintervall för att identifiera kvarstående blockering eller trender.

Samla in information från Extended Events

Förutom den tidigare informationen är det ofta nödvändigt att samla in en spårning av aktiviteterna på servern för att noggrant undersöka ett blockeringsproblem i Azure SQL Database. Om en session till exempel kör flera instruktioner i en transaktion representeras endast den sista instruktionen som skickades. Ett av de tidigare uttalandena kan dock vara orsaken till att lås fortfarande hålls kvar. Med en spårning kan du se alla kommandon som körs av en session i den aktuella transaktionen.

Det finns två sätt att samla in spårningar i SQL Server. Extended Events (XEvents) och Profiler Traces. Men SQL Server Profiler är inaktuell spårningsteknik som inte stöds för Azure SQL Database. Extended Events är den nyare spårningstekniken som ger mer mångsidighet och mindre effekt på det observerade systemet, och dess gränssnitt är integrerat i SQL Server Management Studio (SSMS).

Se dokumentet som förklarar hur du använder guiden utökade händelser ny session i SSMS. För Azure SQL-databaser tillhandahåller SSMS dock en undermapp för utökade händelser under varje databas i Object Explorer. Använd en sessionsguide för utökade händelser för att samla in följande användbara händelser:

  • Kategorifel:

    • Uppmärksamhet
    • Rapporterat_fel
    • Varningsmeddelande om utförande

  • Kategorivarningar:

    • Saknad_sammanfogningspredikat

  • Kategoriexekvering

    • Rpc_slutförd
    • Rpc_starting
    • Sql_omgång_avslutad
    • Sql_batch_starting

  • Kategori deadlock_monitor

    • databas_xml_deadlock_rapport

  • Kategorisession

    • Befintlig_anslutning
    • Logga in
    • Utloggning

Obs

Detaljerad information om dödlägen finns i Analysera och förhindra dödlägen i Azure SQL Database och Fabric SQL Database.

Identifiera och lösa vanliga blockeringsscenarier

Genom att undersöka den tidigare informationen kan du fastställa orsaken till de flesta blockerande problem. Resten av den här artikeln handlar om hur du använder den här informationen för att identifiera och lösa några vanliga blockeringsscenarier. Den här diskussionen förutsätter att du har använt blockeringsskripten (refererade tidigare) för att samla in information om de blockerande SPID:erna och har samlat in programaktivitet med hjälp av en XEvent-session.

Analysera blockeringsinformation

  • Granska utdata från DMV:erna sys.dm_exec_requests och sys.dm_exec_sessions för att fastställa huvudena för blockeringskedjorna med hjälp av blocking_these och session_id. Detta identifierar mest tydligt vilka begäranden som blockeras och vilka som blockerar. Titta närmare på de sessioner som är blockerade och blockerande. Finns det en gemensam eller rot av blockeringskedjan? De delar sannolikt en gemensam tabell och en eller flera av de sessioner som ingår i en blockeringskedja utför en skrivåtgärd.

  • Granska DMV-utdata från sys.dm_exec_requests och sys.dm_exec_sessions för information om SPID:erna i början av blockeringskedjan. Leta efter följande fält:

    • sys.dm_exec_requests.status
      Den här kolumnen visar status för en viss begäran. En vilande status anger vanligtvis att SPID har slutfört körningen och väntar på att programmet ska skicka en annan fråga eller en batch. När statusen för körning eller bearbetning visas, innebär det att SPID för närvarande bearbetar en fråga. Följande tabell innehåller korta förklaringar av de olika statusvärdena.
    Status Betydelse
    Bakgrund SPID kör en bakgrundsaktivitet, såsom deadlock-detektering, loggskrivning eller kontrollpunkt.
    Sova SPID körs inte för närvarande. Detta indikerar vanligtvis att SPID väntar på ett kommando från programmet.
    Löpning SPID körs för närvarande på en schemaläggare.
    Körbar SPID finns i den körbara kön för en schemaläggare och väntar på att få schemaläggarens tid.
    Upphängd SPID väntar på en resurs, till exempel ett lås eller en spärr.

    • sys.dm_exec_sessions.open_transaction_count
      Det här fältet anger antalet öppna transaktioner i den här sessionen. Om det här värdet är större än 0 ligger SPID inom en öppen transaktion och kan innehålla lås som förvärvas av någon instruktion i transaktionen.

    • sys.dm_exec_requests.open_transaction_count
      På samma sätt visar det här fältet antalet öppna transaktioner i den här begäran. Om det här värdet är större än 0 ligger SPID inom en öppen transaktion och kan innehålla lås som har förvärvats av något uttalande i transaktionen.

    • sys.dm_exec_requests.wait_type, wait_timeoch last_wait_type
      Om sys.dm_exec_requests.wait_type är NULL väntar begäran för närvarande inte på något och värdet för last_wait_type anger den senaste wait_type som begäran påträffade. Mer information om sys.dm_os_wait_stats och en beskrivning av de vanligaste väntetyperna finns i sys.dm_os_wait_stats. Värdet wait_time kan användas för att avgöra om begäran går framåt. När en fråga mot tabellen sys.dm_exec_requests returnerar ett värde i kolumnen wait_time som är mindre än värdet wait_time från en tidigare fråga av sys.dm_exec_requestsindikerar detta att det tidigare låset har hämtats och släppts, och nu väntar på ett nytt lås (förutsatt att wait_timeär icke-noll). Detta kan verifieras genom att jämföra wait_resource mellan sys.dm_exec_requests utdata, som visar resursen som begäran väntar på.

    • sys.dm_exec_requests.wait_resource Det här fältet anger resursen som en blockerad begäran väntar på. I följande tabell visas vanliga wait_resource format och deras innebörd:

    Resurs Format Exempel Förklaring
    Bord DatabaseID : ObjectID : IndexID TAB: 5:261575970:1 I det här fallet är databas-ID 5 exempeldatabasen pubs och objekt-ID 261575970 är tabellen titlar och 1 är det klustrade indexet.
    Sida DatabaseID:FileID:PageID SIDA: 5:1:104 I det här fallet är databas-ID 5 is pubs, fil-ID 1 den primära datafilen och sidan 104 är en sida som tillhör tabellen rubriker. Om du vill identifiera vilken object_id sidan tillhör använder du funktionen för dynamisk hantering sys.dm_db_page_infooch anger DatabaseID, FileId, PageId från wait_resource.
    Nyckel DatabaseID:Hobt_id (Hash-värde för indexnyckel) NYCKEL: 5:72057594044284928 (3300a4f361aa) I det här fallet är databas-ID 5 pubsoch Hobt_ID 72057594044284928 motsvarar index_id 2 för object_id 261575970 (tabell med rubriker). Använd sys.partitions-katalogvyn för att associera hobt_id till en viss index_id och object_id. Det finns inget sätt att återställa indexnyckelns hash till ett specifikt nyckelvärde.
    Rad DatabaseID:FileID:PageID:Slot(rad) RID: 5:1:104:3 I det här fallet är databas-ID 5 pubs, fil-ID 1 är den primära datafilen, sidan 104 är en sida som tillhör tabellen rubriker och fack 3 anger radens position på sidan.
    Kompilera DatabaseID:FileID:PageID:Slot(rad) RID: 5:1:104:3 I det här fallet är databas-ID 5 pubs, fil-ID 1 är den primära datafilen, sidan 104 är en sida som tillhör tabellen rubriker och fack 3 anger radens position på sidan.
    • sys.dm_tran_active_transactions Den sys.dm_tran_active_transactions DMV innehåller data om öppna transaktioner som kan kopplas till andra DMV:er för en fullständig bild av transaktioner som väntar på incheckning eller återställning. Använd följande fråga för att returnera information om öppna transaktioner som är anslutna till andra DMV:er, inklusive sys.dm_tran_session_transactions. Överväg en transaktions aktuella tillstånd, transaction_begin_timeoch andra situationsdata för att utvärdera om det kan vara en blockeringskälla.
    SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
, transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                WHEN 2 THEN 'Read-only transaction'
                                                WHEN 3 THEN 'System transaction'
                                                WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow     
, transaction_state  = CASE tat.transaction_state    
            WHEN 0 THEN 'The transaction has not been completely initialized yet.'
            WHEN 1 THEN 'The transaction has been initialized but has not started.'
            WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
            WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
            WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
            WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
            WHEN 6 THEN 'The transaction has been committed.'
            WHEN 7 THEN 'The transaction is being rolled back.'
            WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name, request_status = r.status
, azure_dtc_state = CASE tat.dtc_state 
                    WHEN 1 THEN 'ACTIVE'
                    WHEN 2 THEN 'PREPARED'
                    WHEN 3 THEN 'COMMITTED'
                    WHEN 4 THEN 'ABORTED'
                    WHEN 5 THEN 'RECOVERED' END
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count  
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;

    • Andra kolumner

      De återstående kolumnerna i sys.dm_exec_sessions och sys.dm_exec_request kan också ge insikt i roten för ett problem. Deras användbarhet varierar beroende på omständigheterna i problemet. För exempelvis kan du avgöra om problemet endast inträffar från vissa klienter (värdnamn), på vissa nätverksbibliotek (net_library), när den senaste batchen som skickades av en SPID var last_request_start_time i sys.dm_exec_sessions, hur länge en begäran hade körts med start_time i sys.dm_exec_requests, och så vidare.

Vanliga blockeringsscenarier

Tabellen nedan mappar vanliga symtom till deras troliga orsaker.

Kolumnerna Waittype, Open_Tranoch Status refererar till information som returneras av sys.dm_exec_request. Andra kolumner kan returneras av sys.dm_exec_sessions. Kolumnen "Löser?" anger om blockeringen löses på egen hand eller om sessionen ska avlivas via kommandot KILL. Mer information finns i KILL.

Scenario Väntetyp Open_Tran Status Löser? Andra symtom
1 IKKE NULL >= 0 Körbar Ja, när frågan är klar. I sys.dm_exec_sessionsökar kolumnerna reads, cpu_timeoch/eller memory_usage över tid. Förfrågans varaktighet är hög när den har slutförts.
2 NOLL >0 sova Nej, men SPID kan dödas. En uppmärksamhetssignal kan visas i den utökade händelsesessionen för denna SPID, vilket indikerar att en frågetidsgräns har nåtts eller att en fråga har avbrutits.
3 NOLL >= 0 Körbar Nej. Löses inte förrän klienten hämtar alla rader eller stänger anslutningen. SPID kan avlivas, men det kan ta upp till 30 sekunder. Om open_transaction_count = 0 och SPID låser sig medan transaktionsisoleringsnivån är standard (READ COMMITTED) är detta en sannolik orsak.
4 Varierar >= 0 Körbar Nej. Löses inte förrän klienten avbryter frågor eller stänger anslutningar. SPID:er kan dödas, men det kan ta upp till 30 sekunder. Kolumnen hostname i sys.dm_exec_sessions för SPID längst fram i en blockeringskedja är densamma som en av SPID:erna den blockerar.
5 NOLL >0 återställning Ja. En uppmärksamhetssignal kan visas i sessionen Extended Events för denna SPID, vilket indikerar att en timeout eller avbrytning av fråga har inträffat, eller att ett återställningskommando har utfärdats.
6 NOLL >0 sova Så småningom. När Windows fastställer att sessionen inte längre är aktiv bryts Azure SQL Database-anslutningen. Värdet last_request_start_time i sys.dm_exec_sessions är betydligt tidigare än nuvarande tid.

Detaljerade blockeringsscenarier

  1. Blockering orsakad av en sökfråga som vanligtvis körs med lång exekveringstid

    Lösning: Lösningen på den här typen av blockeringsproblem är att leta efter sätt att optimera frågan. I själva verket kan den här klassen av blockeringsproblem bara vara ett prestandaproblem och kräva att du fortsätter med det som sådant. Information om hur du felsöker en specifik fråga som körs långsamt finns i Så här felsöker du långsamma frågor på SQL Server-. Mer information finns i Övervaka och justera prestanda.

    Rapporter från Query Store i SSMS är också ett starkt rekommenderat och värdefullt verktyg för att identifiera de dyraste förfrågningarna och mindre optimala körningsplaner. Läs även Query Performance Insight.

    Om frågan endast utför SELECT-åtgärder kan du köra -instruktionen under ögonblicksbildisolering om den är aktiverad i databasen, särskilt om RCSI har inaktiverats. Som när RCSI är aktiverat kräver frågor som läser data inte delade (S) lås under isoleringsnivå för ögonblicksbilder. Dessutom ger ögonblicksbildisolering konsekvens på transaktionsnivå för alla instruktioner i en explicit transaktion med flera instruktioner. Ögonblicksbildisolering kan redan vara aktiverat i databasen. Ögonblicksbildisolering kan också användas med frågor som utför ändringar, men du måste hantera uppdateringskonflikter.

    Om du har en tidskrävande fråga som blockerar andra användare och inte kan optimeras kan du överväga att flytta den från en OLTP-miljö till ett dedikerat rapporteringssystem, en synkron skrivskyddad replik av databasen.

  2. Blockering orsakad av en sovande SPID som har en obekräftad transaktion

    Den här typen av blockering kan ofta identifieras av en SPID som ligger i viloläge eller väntar på ett kommando, men vars transaktionskapslingsnivå (@@TRANCOUNT, open_transaction_count från sys.dm_exec_requests) är större än noll. Detta kan inträffa om programmet drabbas av en tidsgräns för frågan, eller om en avbrytning utfärdas utan att också utfärda det nödvändiga antalet ROLLBACK- och/eller COMMIT-instruktioner. När en SPID tar emot en tidsgräns för frågan eller ett avbrott avslutas den aktuella frågan och batchen, men återställs inte automatiskt eller genomför transaktionen. Programmet ansvarar för detta eftersom Azure SQL Database inte kan anta att en hel transaktion måste återställas på grund av att en enskild fråga avbryts. Förfrågningens tidsgräns eller avbrytning visas som en ATTENTION-signalhändelse för SPID i sessionen för utökade händelser.

    Om du vill demonstrera en explicit transaktion som inte har genererats utfärdar du följande fråga:

    CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

    Kör sedan den här frågan i samma fönster:

    SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

    Utdata från den andra frågan anger att transaktionskapslingsnivån är en. Alla lås som införskaffats i transaktionen hålls kvar tills transaktionen har bekräftats eller återställts. Om program uttryckligen öppnar och genomför transaktioner kan ett kommunikationsfel eller annat fel lämna sessionen och dess transaktion i ett öppet tillstånd.

    Använd skriptet tidigare i den här artikeln baserat på sys.dm_tran_active_transactions för att identifiera transaktioner som för närvarande inte har genererats i instansen.

    lösningar:

    • Dessutom kan den här klassen av blockeringsproblem också vara ett prestandaproblem och kräva att du fortsätter med det som sådant. Om frågekörningstiden kan minskas, skulle ingen tidsgräns för frågan eller avbrytning inträffa. Det är viktigt att programmet kan hantera timeout-scenarier eller avbryta scenarier om de skulle uppstå, men du kan också ha nytta av att undersöka frågans prestanda.

    • Program måste hantera transaktionskapslingsnivåer korrekt, eller så kan de orsaka ett blockerande problem efter att frågan har avbrutits på det här sättet. Överväga:

      • I felhanteraren för klientprogrammet kör du IF @@TRANCOUNT > 0 ROLLBACK TRAN efter eventuella fel, även om klientprogrammet inte tror att en transaktion är öppen. Det krävs en sökning efter öppna transaktioner eftersom en lagrad procedur som anropades under batchen kunde ha startat en transaktion utan klientprogrammets vetskap. Vissa villkor, till exempel att avbryta frågan, förhindrar att proceduren körs förbi den aktuella instruktionen, så även om proceduren har logik för att kontrollera IF @@ERROR <> 0 och avbryta transaktionen körs inte den här återställningskoden i sådana fall.
      • Om anslutningspooler används i ett program som öppnar anslutningen och kör några frågor innan anslutningen släpps tillbaka till poolen, till exempel ett webbaserat program, kan det hjälpa att tillfälligt inaktivera anslutningspooler tills klientprogrammet ändras för att hantera felen på rätt sätt. Om du inaktiverar anslutningspoolen orsakar en frisläppning av anslutningen en fysisk frånkoppling av Azure SQL Database-anslutningen, vilket resulterar i att servern återställer alla öppna transaktioner.
      • Använd SET XACT_ABORT ON för anslutningen eller i lagrade procedurer som påbörjar transaktioner och inte rensar upp efter ett fel. I händelse av ett körningsfel avbryter den här inställningen alla öppna transaktioner och returnerar kontrollen till klienten. Mer information finns i SET XACT_ABORT.

    Obs

    Anslutningen återställs inte förrän den återanvänds från anslutningspoolen, så det är möjligt att en användare kan öppna en transaktion och sedan släppa anslutningen till anslutningspoolen, men den kanske inte återanvänds på flera sekunder, under vilken tid transaktionen skulle förbli öppen. Om anslutningen inte återanvänds avbryts transaktionen när anslutningen överskrider tidsgränsen och tas bort från anslutningspoolen. Därför är det optimalt för klientprogrammet att avbryta transaktioner i felhanteraren eller använda SET XACT_ABORT ON för att undvika den här potentiella fördröjningen.

    Försiktighet

    Efter SET XACT_ABORT ONkörs inte T-SQL-instruktioner efter en instruktion som orsakar ett fel. Detta kan påverka det avsedda flödet av befintlig kod.

  3. Blockering orsakad av en SPID vars motsvarande klientprogram inte hämtade alla resultatrader till slut

    När du har skickat en fråga till servern måste alla program omedelbart hämta alla resultatrader fullständigt. Om ett program inte hämtar alla resultatrader kan lås lämnas kvar i tabellerna och blockera andra användare. Om du använder ett program som transparent skickar SQL-instruktioner till servern måste programmet hämta alla resultatrader. Om den inte gör det (och om den inte kan konfigureras för att göra det) kanske du inte kan lösa blockeringsproblemet. För att undvika problemet kan du begränsa program med dåligt uppförande till en rapport eller en beslutsstödsdatabas, separat från huvuddatabasen för OLTP.

    Effekten av det här scenariot minskas när läsecheckade ögonblicksbilder är aktiverade på databasen, vilket är standardkonfigurationen i Azure SQL Database. Läs mer i avsnittet Förstå blockering i den här artikeln.

    Not

    Se vägledning för omprövning av logik för program som ansluter till Azure SQL Database.

    Resolution: Programmet måste skrivas om för att hämta alla rader i resultatet fullständigt. Detta utesluter inte användningen av OFFSET och FETCH i ORDER BY-satsen för en fråga för att utföra sidnumrering på serversidan.

  4. Blockering orsakad av en session i återgångsläge

    En fråga om dataändring som avslutas eller avbryts utanför en användardefinierad transaktion rullas tillbaka. Detta kan också inträffa som en bieffekt av att klientnätverkssessionen kopplas från eller när en begäran väljs som ett offer för dödläge. Detta kan ofta identifieras genom att observera utdata från sys.dm_exec_requests, vilket kan tyda på ROLLBACK-kommandot, och kolumnen percent_complete kan visa förloppet.

    Tack vare accelererad databasåterställning introducerades 2019 bör långa återställningar vara sällsynta.

    Resolution: Vänta tills SPID har rullat tillbaka de ändringar som gjorts.

    Undvik den här situationen genom att inte utföra stora batchskrivningsåtgärder eller skapa eller underhålla index under upptagna timmar i OLTP-system. Utför om möjligt sådana åtgärder under perioder med låg aktivitet.

  5. Blockering orsakad av en överbliven anslutning

    Om klientapplikationen fångar upp fel eller om klientarbetsstationen startas om, kanske nätverkssessionen med servern inte avbryts direkt under vissa förhållanden. Från Azure SQL Database-perspektivet verkar klienten fortfarande finnas och eventuella lås som hämtas kan fortfarande behållas. Mer information finns i Så här felsöker du överblivna anslutningar i SQL Server.

    Resolution: Om klientprogrammet har kopplats från utan att behöva rensa resurserna på rätt sätt kan du avsluta SPID med hjälp av kommandot KILL. Kommandot KILL tar SPID-värdet som indata. Om du till exempel vill döda SPID 99 utfärdar du följande kommando:

    KILL 99

Relaterat innehåll