Dela via

Förstå och lösa blockeringsproblem i Azure SQL Database

  • Artikel

Gäller för:Azure SQL Database

Mål

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

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.

Kommentar

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.

Förstå blockering

Blockering är en oundviklig och avsiktlig egenskap i ett hanteringssystem för relationsdatabaser (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. Detta är normalt beteende 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 inställningen för skrivskyddade ögonblicksbilder (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ågetips eller SET-instruktioner 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ån 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 is_read_committed_snapshot_on returnerar kolumnen värdet 1. Om ögonblicksbildisolering är aktiverat snapshot_isolation_state_desc returnerar kolumnen värdet PÅ.

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

För frågor som körs inom en explicit transaktion bestäms typen av lås och varaktighet för vilka låsen hålls av typen av fråga, transaktionsisoleringsnivån och om låstips 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 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 ytterligare konfiguration.

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

Mer information och information om var optimerad låsning är tillgänglig finns i Optimerad låsning.

Program och blockering

Det kan finnas en tendens att fokusera på justering på serversidan och plattformsproblem vid blockeringsproblem. Men uppmärksamhet som endast ägnas databasen kan inte leda 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 blockerande problem dock 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 utvecklingsverktyg inte tillåter explicit kontroll över anslutningshantering, frågeavbokning, tidsgräns för frågor, hämtning av resultat och så vidare kan det hända att 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. Ett bra område för anslutningshantering måste utövas, utan det 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.

Kommentar

Mer vägledning för programutveckling finns i Felsöka anslutningsproblem och andra fel med Azure SQL Database och Azure SQL Managed Instance och hantering av tillfälliga fel.

Felsöka blockering

Oavsett vilken blockeringssituation vi befinner oss i är metoden för felsökning av låsning densamma. Dessa logiska separationer är vad som kommer att diktera 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 gå in och diskutera hur du hittar huvudblockeringssessionen med en lämplig datainsamling.

Samla in blockeringsinformation

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

Att referera till DMV:er för att felsöka blockering har som mål att identifiera SPID (sessions-ID) i huvudet på blockeringskedjan och SQL-instruktionen. Leta efter offer SPIDs som blockeras. Om någon SPID blockeras av en annan SPID undersöker du SPID som äger resursen (den blockerande SPID). Blockeras även ägarens SPID? 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 kärnan i andra frågor.

  • Om du redan har en viss session identifierad kan du använda DBCC INPUTBUFFER(<session_id>) för att hitta den sista instruktionen som skickades av en session. Liknande resultat kan returneras med funktionen sys.dm_exec_input_buffer för dynamisk hantering (DMF), i en resultatuppsättning som är enklare att köra frågor mot 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);

  • Referera till blocking_session_id kolumnen i sys.dm_exec_requests. När blocking_session_id = 0 blockeras inte en session. Även om sys.dm_exec_requests listor endast begärs som körs för närvarande visas alla anslutningar (aktiva eller inte) i sys.dm_exec_sessions. Skapa på 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 text fältet sys.dm_exec_sql_text i är NULL körs inte frågan för närvarande. I så fall event_info innehåller fältet sys.dm_exec_input_buffer för den sista kommandosträngen som skickades till SQL-motorn. Den här frågan kan också användas för att identifiera sessioner som blockerar andra sessioner, inklusive en lista över session_ids blockeras 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.

Kommentar

Mer information om väntetyper, inklusive sammanställd 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 mer detaljerad information om vilka lås som har placerats av frå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 INRE KOPPLING på sys.dm_os_waiting_tasksbegränsar följande fråga utdata från sys.dm_tran_locks endast till för närvarande blockerade begäranden, deras väntestatus och 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 tillhandahåller lagring av frågeresultat över tid datapunkter som gör att du kan granska blockering under ett angivet tidsintervall för att identifiera bestå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. 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. SQL Server Profiler är dock inaktuell spårningsteknik som inte stöds för Azure SQL Database. Extended Events är den nyare spårningstekniken som möjliggör mer mångsidighet och mindre påverkan 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 Förlängda 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:

    • Observera!
    • Error_reported
    • Execution_warning

  • Kategorivarningar:

    • Missing_join_predicate

  • Kategorikörning:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • Kategori deadlock_monitor

    • database_xml_deadlock_report

  • Kategorisession

    • Existing_connection
    • Inloggning
    • Utloggning

Kommentar

Detaljerad information om dödlägen finns i Analysera och förhindra dödlägen i Azure 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 blockeringsdata

  • 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 tydligast vilka begäranden som blockeras och vilka som blockerar dem. Titta närmare på de sessioner som blockeras och blockeras. Finns det en vanlig eller rot i 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 utdata från DMV:erna sys.dm_exec_requests och sys.dm_exec_sessions för information om SPID:erna längst upp i 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 batch. En körnings- eller körningsstatus anger att SPID för närvarande bearbetar en fråga. Följande tabell innehåller korta förklaringar av de olika statusvärdena.
    Status Innebörd
    Bakgrund SPID kör en bakgrundsaktivitet, till exempel identifiering av dödläge, loggskrivare 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.
    Uppehåll 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 är 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 är 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.wait_type, wait_time, och last_wait_type
      sys.dm_exec_requests.wait_type Om är NULL väntar inte begäran för närvarande på något och last_wait_type värdet anger det sista wait_type som begäran påträffades. 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 fortsätter. När en fråga mot sys.dm_exec_requests tabellen returnerar ett värde i wait_time kolumnen som är mindre än wait_time värdet från en tidigare fråga i sys.dm_exec_requestsanger detta att det tidigare låset hämtades och släpptes och nu väntar på ett nytt lås (förutsatt att det inte är noll wait_time). Detta kan verifieras genom att wait_resource jämföra mellan sys.dm_exec_requests utdata, som visar den resurs 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 betydelse:

    Resurs Format Exempel Förklaring
    Register DatabaseID:ObjectID:IndexID TAB: 5:261575970:1 I det här fallet är databas-ID 5 exempeldatabasen och objekt-ID:t för pubar 261575970 är tabellen rubriker och 1 är det klustrade indexet.
    Sida DatabaseID:FileID:PageID SIDA: 5:1:104 I det här fallet är databas-ID 5 pubar, fil-ID 1 är den primära datafilen och sidan 104 är en sida som tillhör tabellen rubriker. Om du vill identifiera object_id sidan tillhör använder du funktionen för dynamisk hantering sys.dm_db_page_info och skickar in DatabaseID, FileId, PageId från wait_resource.
    Key DatabaseID:Hobt_id (Hash-värde för indexnyckel) NYCKEL: 5:72057594044284928 (3300a4f361aa) I det här fallet är databas-ID 5 pubar, Hobt_ID 72057594044284928 motsvarar index_id 2 för object_id 261575970 (tabell med rubriker). sys.partitions Använd katalogvyn för att associera hobt_id till en viss index_id och object_id. Det går inte att ta bort indexnyckelns hash till ett specifikt nyckelvärde.
    Row DatabaseID:FileID:PageID:Slot(row) RID: 5:1:104:3 I det här fallet är databas-ID 5 pubar, 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(row) RID: 5:1:104:3 I det här fallet är databas-ID 5 pubar, 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_transactionsSys.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_time, och 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 av ett problem. Deras användbarhet varierar beroende på omständigheterna i problemet. Du kan till exempel avgöra om problemet bara 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 i start_time och sys.dm_exec_requestsså vidare.

Vanliga blockeringsscenarier

Tabellen nedan mappar vanliga symtom till deras troliga orsaker.

Kolumnerna Waittype, Open_Tran och 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 kommer att lösas på egen hand eller om sessionen ska avlivas via KILL kommandot eller inte. Mer information finns i KILL (Transact-SQL).

Scenario Väntetyp Open_Tran Status Löser? Andra symtom
1 INTE NULL >= 0 Körbar Ja, när frågan är klar. I sys.dm_exec_sessions, reads, cpu_time, och/eller memory_usage kommer kolumnerna att öka med tiden. Frågans varaktighet är hög när den är klar.
2 NULL >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 tidsgräns för frågor eller avbrutna frågor har inträffat.
3 NULL >= 0 Körbar Nr. Löses inte förrän klienten hämtar alla rader eller stänger anslutningen. SPID kan dödas, men det kan ta upp till 30 sekunder. Om open_transaction_count = 0 och SPID låser sig medan transaktionsisoleringsnivån är standard (READ COMMMITTED) är detta en sannolik orsak.
4 Det varierar >= 0 Körbar Nr. Löses inte förrän klienten avbryter frågor eller stänger anslutningar. SPID kan avlivas, men kan ta upp till 30 sekunder. Kolumnen hostname i sys.dm_exec_sessions för SPID i huvudet på en blockeringskedja kommer att vara samma som en av de SPID som den blockerar.
5 NULL >0 Rollback Ja. En uppmärksamhetssignal kan visas i sessionen Extended Events för denna SPID, vilket indikerar att en tidsgräns för frågor eller avbrutna frågor har inträffat, eller att en återställningsuttryck har utfärdats.
6 NULL >0 Sova Så småningom. När Windows NT 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 mycket tidigare än den aktuella tiden.

Detaljerade blockeringsscenarier

  1. Blockering som orsakas av en fråga som körs normalt med lång körningstid

    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 för prestanda.

    Rapporter från Query Store i SSMS är också ett starkt rekommenderat och värdefullt verktyg för att identifiera de dyraste frågorna, suboptimala körningsplaner. Läs även avsnittet Intelligenta prestanda i Azure-portalen för Azure SQL-databasen, inklusive Query Performance Insight.

    Om frågan endast utför SELECT-åtgärder kan du överväga att köra -instruktionen under ögonblicksbildisolering om den är aktiverad i databasen, särskilt om RCSI har inaktiverats. Precis 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. Isolering av ögonblicksbilder kan också användas med frågor som utför ändringar, men du måste hantera uppdateringskonflikter.

    Om du har en långvarig 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 vilande SPID som har en transaktion som inte har överförts

    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å (@@TRANCOUNTopen_transaction_countfrå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 ett avbrott uppstår utan att du också utfärdar 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 den återställs inte automatiskt eller genomför transaktionen. Programmet ansvarar för detta eftersom Azure SQL Database inte kan förutsätta att en hel transaktion måste återställas på grund av att en enskild fråga avbryts. Tidsgränsen eller avbryt frågan visas som en ATTENTION-signalhändelse för SPID i den utökade händelsesessionen.

    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 checkats in 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 uppstår inte tidsgränsen för frågan eller avbrytas. 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. Tänk på följande:

      • I felhanteraren för klientprogrammet kör IF @@TRANCOUNT > 0 ROLLBACK TRAN du följande 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 ett litet antal 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 leder det till 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. Om det uppstår ett körningsfel avbryter den här inställningen alla öppna transaktioner och returnerar kontrollen till klienten. Mer information finns i SET XACT_ABORT (Transact-SQL).

    Kommentar

    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 sin felhanterare eller använda SET XACT_ABORT ON för att undvika den här potentiella fördröjningen.

    Försiktighet

    Följande SET XACT_ABORT ONT-SQL-instruktioner efter en instruktion som orsakar ett fel kommer inte att köras. 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 slutförande

    När du har skickat en fråga till servern måste alla program omedelbart hämta alla resultatrader för slutförande. Om ett program inte hämtar alla resultatrader kan lås lämnas på 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 som fungerar dåligt till en rapport- eller beslutsstödsdatabas, separat från OLTP-huvuddatabasen.

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

    Kommentar

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

    Lösning: Programmet måste skrivas om för att hämta alla rader i resultatet till slutförande. Detta utesluter inte användning av OFFSET och FETCH i ORDER BY-satsen för en fråga för att utföra växling på serversidan.

  4. Blockering orsakad av en session i återställningstillstånd

    En dataändringsfråga som är KILLed eller avbryts utanför en användardefinierad transaktion återställs. 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 dödlägesoffer. Detta kan ofta identifieras genom att observera utdata från sys.dm_exec_requests, vilket kan tyda på ROLLBACK-kommandot, och percent_complete kolumnen kan visa förlopp.

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

    Lösning: Vänta tills SPID har återställt de ändringar som har 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 klientprogrammet löser fel eller om klientarbetsstationen startas om kanske nätverkssessionen till servern inte avbryts omedelbart 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 Felsöka överblivna anslutningar i SQL Server.

    Lösning: Om klientprogrammet har kopplats från utan att rensa resurserna på rätt sätt kan du avsluta SPID med hjälp KILL av kommandot . 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

Se även

Nästa steg