Förstå och lös blockeringsproblem med SQL Server

gäller för: SQL Server, Azure SQL Managed Instance

Ursprungligt KB-nummer: 224453

Sammanfattning

Blockering är normalt i alla låsbaserade relationsdatabaser, men beständig eller långvarig blockering försämrar dataflödet och orsakar tidsgränser för programmet. Den här artikeln beskriver vad blockering finns i SQL Server, hur du samlar in blockerande data från dynamiska hanteringsvyer (DMV:er) och Utökade händelser och hur du identifierar huvudblockeraren. Den går sedan igenom de vanligaste blockeringsscenarierna och deras lösningar.

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. Varje SPID kallas ofta för en "process", men det är inte en separat operativsystemprocess. Det är resurserna på serversidan och datastrukturerna som betjänar en klientanslutning. Ett enskilt klientprogram kan ha en eller flera anslutningar. Från SQL Server perspektiv behandlas anslutningar från många klienter och många anslutningar från en klient på samma sätt. En anslutning kan blockera en annan anslutning, oavsett källklient.

Kommentar

Den här artikeln fokuserar på SQL Server instanser, inklusive Azure SQL Managed Instance. Information som är specifik för felsökning av blockering i Azure SQL-databas finns i Förstå och lösa blockeringsproblem i Azure SQL-databas.

Vad orsakar blockeringen?

Blockering är en oundviklig och avsiktlig egenskap i ett hanteringssystem för relationsdatabaser (RDBMS) med låsbaserad samtidighet. Som tidigare nämnts sker blockering i SQL Server när en session låser en specifik resurs och en andra SPID försöker hämta en låstyp i konflikt på samma resurs. Vanligtvis är tidsramen liten för när den första SPID:en låser resursen. När ägarsessionen släpper låset är den andra anslutningen fri att hämta ett eget lås för resursen och fortsätta bearbetningen. Blockering enligt beskrivningen här är normalt beteende och kan inträffa många gånger under en dag utan märkbar effekt på systemets prestanda.

Varaktigheten och transaktionskontexten för en fråga avgör hur länge låsen hålls kvar och därmed deras effekt på andra frågor. Om frågan inte körs i en transaktion (och inga låstips används) kommer låsen för SELECT-uttryck endast att lagras på en resurs när den faktiskt läses, inte under frågan. För INSERT-, UPDATE- och DELETE-uttryck lagras låsen under frågan, både för datakonsekvens och för att frågan ska kunna återställas om det behövs.

För frågor som körs i en transaktion avgör typen av fråga, transaktionsisoleringsnivån och om låstips används i frågan hur länge låsen hålls. En beskrivning av låsnings-, låstips- och transaktionsisoleringsnivåer finns i följande artiklar:

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

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

  • En SPID låser 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:ar orsakar blockering på olika resurser över tid, vilket skapar ett rörligt mål. De här situationerna ä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.

Program och blockering

När du stöter på ett blockeringsproblem kan du fokusera på justerings- och plattformsproblem på serversidan. Men att bara uppmärksamma databasen kanske inte leder till en lösning. Det kan absorbera tid och energi som du kan rikta bättre på att undersöka klientprogrammet och de frågor som skickas. Oavsett vilken synlighetsnivå programmet exponerar för de databasanrop det gör, kräver ett blockeringsproblem ofta både kontroll av de exakta SQL-instruktioner som programmet skickar 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ågereducering, tidsgräns för frågor, resultathämtning och så vidare kanske du inte kan lösa blockeringsproblem. Granska den här potentialen noggrant innan du väljer ett programutvecklingsverktyg för SQL Server, särskilt för prestandakänsliga OLTP-miljöer.

Var uppmärksam på databasens prestanda under design- och konstruktionsfasen för databasen och programmet. Utvärdera i synnerhet resursförbrukning, isoleringsnivå och transaktionssökvägslängd för varje fråga. Varje fråga och transaktion bör vara så enkel som möjligt. Använd ett bra område för anslutningshantering. 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 SQL Server stödja tusentals samtidiga användare på en enda server, med liten blockering.

Felsöka blockering

Oavsett vilken blockeringssituation du befinner dig i är metoden för felsökning av låsning densamma. Dessa logiska separationer 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 du har identifierat den problematiska frågan (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 du förstår varför kan du göra ändringar genom att göra om frågan och transaktionen.

Steg i felsökning:

  1. Identifiera huvudblockeringssessionen (huvudblockerare).

  2. Hitta den fråga och transaktion som orsakar blockeringen (vad som låser sig under en längre period).

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

  4. Lös blockeringsproblemet genom att göra om frågan och transaktionen.

Nu ska vi titta närmare på hur du kan hitta den huvudsakliga blockeringssessionen 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 på SQL Server. Det finns två kostnadsfria metoder för att samla in dessa data.

Den första metoden är att köra frågor mot dynamiska hanteringsobjekt 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 Extended Events (XEvents) eller SQL Profiler Traces för att samla in vad som körs. Eftersom SQL Trace och SQL Server Profiler är inaktuella fokuserar den här felsökningsguiden på XEvents.

Samla in information från DMV:er

Om du vill felsöka blockering använder du DMV:er för att identifiera SPID (sessions-ID) i huvudet på blockeringskedjan och SQL-instruktionen. Leta efter spid-offer som är blockerade. Om en SPID blockeras av en annan SPID undersöker du den SPID som äger resursen (den blockerande SPID:en). Är ägaren SPID också blockerad? Du kan följa kedjan för att hitta huvudblockeraren och sedan undersöka varför den behåller låset.

Gör detta genom att använda någon av följande metoder:

  • I SQL Server Management Studio (SSMS) Object Explorer högerklickar du på serverobjektet på den översta nivån, expanderar Rapporter, expanderar Standardrapporter och väljer sedan Aktivitet – Alla blockerande transaktioner. Den här rapporten visar aktuella transaktioner i huvudet på en blockeringskedja. Om du expanderar transaktionen visar rapporten de transaktioner som blockeras av huvudtransaktionen. Den här rapporten visar också Spärrar SQL-sats och Spärrad sql-sats.

  • Öppna Aktivitetsövervakaren i SSMS och se kolumnen Blockerad av . Mer information finns i Aktivitetsövervakaren.

Mer detaljerade frågebaserade metoder är också tillgängliga med DMV:er:

  • Kommandona sp_who och sp_who2 är äldre kommandon som visar 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 i andra frågor.

  • Om du redan har identifierat en viss session kan du använda DBCC INPUTBUFFER(<session_id>) för att hitta den sista instruktionen som en session har skickat. Liknande resultat returneras av 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 kolumnen sys.dm_exec_requests och blocking_session_id. När blocking_session_id = 0 blockeras inte en session. Listor sys.dm_exec_requests över endast begäranden som körs för närvarande, sys.dm_exec_sessions visar en lista över alla anslutningar (aktiva eller inte). Skapa på den här gemensamma kopplingen mellan sys.dm_exec_requests och sys.dm_exec_sessions i nästa fråga. Tänk på att för att returneras av sys.dm_exec_requestsmåste frågan köras aktivt med SQL Server.

  • 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 DMV:erna sys.dm_exec_sql_text eller sys.dm_exec_input_buffer. Om data som returneras av kolumnen text i sys.dm_exec_sql_text är NULL körs inte frågan för närvarande. I så fall innehåller kolumnen event_info i sys.dm_exec_input_buffer 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 som 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 avancerade exempelfrågan, som tillhandahålls av Microsoft Support, för att identifiera huvudet 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 tråd-/aktivitetsskiktet i SQL Server. Den här DMV:en returnerar information om vilken SQL-wait_type begäran för närvarande upplever. Precis som sys.dm_exec_requests, returneras 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.

  • Använd DMV:n sys.dm_tran_locks för mer detaljerad information om vilka lås som har placerats av frågor. Den här DMV:n kan returnera stora mängder data på en produktionsinstans för SQL Server 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_tasks begränsar följande fråga utdata från sys.dm_tran_locks till endast 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 beständig blockering eller trender. Verktyget för CSS för att felsöka sådana problem är att använda datainsamlaren PSSDiag. Det här verktyget använder "SQL Server Perf Stats" för att samla in resultatuppsättningar från DMV:er som refereras ovan över tid. Allt eftersom det här verktyget ständigt utvecklas kan du granska den senaste offentliga versionen av DiagManager på GitHub.

Samla in information från utökade händelser

Förutom föregående information måste du ofta samla in en spårning av aktiviteterna på servern för att noggrant undersöka ett blockeringsproblem i SQL Server. Om en session till exempel kör flera instruktioner i en transaktion representeras endast den sista instruktionen som sessionen skickar. 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 en session kör i den aktuella transaktionen.

Du kan samla in spårningar i SQL Server med hjälp av två metoder: Extended Events (XEvents) och Profiler Traces. SQL-spårningar som använder SQL Server Profiler är dock inaktuella. XEvents är den nyare, överlägsna spårningsplattformen som erbjuder mer mångsidighet och mindre påverkan på det observerade systemet. Dess gränssnitt är integrerat i SSMS.

I Object Explorer, under menyn för XEvent Profiler, hittar du färdiga Extended Event-sessioner som är redo att starta i SSMS. Mer information finns i XEvent Profiler. Du kan också skapa egna anpassade extended event-sessioner i SSMS. Mer information finns i guiden Ny session för utökade händelser. För felsökning av blockeringsproblem samlar du vanligtvis in:

  • Kategorifel:
    • Observera!
    • Blocked_process_report**
    • Error_reported (kanaladministratör)
    • Exchange_spill
    • Execution_warning

**Om du vill konfigurera tröskelvärdet och frekvensen för när blockerade processrapporter genereras använder du kommandot sp_configure för att konfigurera alternativet blockerat procesströskelvärde, som kan anges i sekunder. Som standard skapas inga blockerade processrapporter.

  • Kategorivarningar:

    • Hash_warning
    • Missing_column_statistics
    • Missing_join_predicate
    • Sort_warning

  • Kategorikörning:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • Kategorilås

    • Lock_deadlock

  • Kategorisession

    • Existing_connection
    • Inloggning
    • Utloggning

Identifiera och lösa vanliga blockeringsscenarier

Genom att undersöka föregående information kan du fastställa orsaken till de flesta blockerande problem. I resten av den här artikeln beskrivs 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 använder blockeringsskripten (som refererades tidigare) för att samla in information om de blockerande SPID:erna och samla 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. Dessa utdata identifierar tydligt vilka begäranden som blockeras och vilka som blockerar dem. Titta närmare på de sessioner som blockeras och blockerar. Finns det en gemensam eller rot i blockeringskedjan? De delar troligen 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 i blockeringskedjans huvud. Leta efter följande kolumner:

    • sys.dm_exec_requests.status

      Den här kolumnen visar status för en viss begäran. Normalt indikerar en vilostatus att SPID slutförde 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 deadlock, loggskrivare eller kontrollpunkt.
      Vilar SPID körs inte för närvarande. Detta är vanligtvis en indikation på att SPID väntar på ett kommando från programmet.
      Körs 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äggningstid.
      Inaktiverad SPID väntar på en resurs, t.ex. ett lås eller en spärr.

    • sys.dm_exec_sessions.open_transaction_count

      Den här kolumnen visar 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. Den öppna transaktionen kan skapas antingen av en aktiv instruktion eller av en instruktionsbegäran som kördes tidigare och inte längre är aktiv.

    • sys.dm_exec_requests.open_transaction_count

      På samma sätt visar den här kolumnen 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 förvärvas av en aktiv instruktion i transaktionen. Till skillnad från sys.dm_exec_sessions.open_transaction_count, om det inte finns en aktiv begäran, visar den här kolumnen 0.

    • sys.dm_exec_requests.wait_type, wait_time och last_wait_type

      sys.dm_exec_requests.wait_type Om är NULL väntar begäran för närvarande inte på något. Värdet last_wait_type anger det sista 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 framsteg. 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 det här villkoret 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). Du kan verifiera det här villkoret wait_resource genom att 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

      Den här kolumnen 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 Flik: 5:261575970:1 I det här fallet är databas-ID 5 pubarnas exempeldatabas och object_id 261575970 är rubriktabellen och 1 är det klustrade indexet.
      Sida Databas-ID: fil-ID: sid-ID 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 databas-ID, fält-ID och sid-ID från wait_resource.
      Nyckel Databas-ID: Hobt_id (hashvä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 (tabellen Rubriker). Använd katalogvyn sys.partitions för att associera hobt_id till en viss index_id och object_id. Det finns inget sätt att ta bort indexnyckelns hash till ett specifikt nyckelvärde.
      Rad Databas-ID: fil-ID: sid-ID: spår (rad) RID: 5:1:104:3 I det här fallet är databas-ID 5 pubar, fil-ID 1 är den primära datafilen, sida 104 är en sida som tillhör tabellen Rubriker och plats 3 anger radens position på sidan.

    • sys.dm_tran_active_transactions DMV:n sys.dm_tran_active_transactions 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
, 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 kring problemet. Du kan till exempel avgöra om problemet bara inträffar från vissa klienter (hostname), på vissa nätverksbibliotek (client_interface_name), när den sista 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 wait_type, open_transaction_count 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 Wait_type Open_Tran Status Lösningar? 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 ökar kolumnerna med tiden. Frågans varaktighet är hög när den är klar.
2 NULL >0 viloläge Nej, men SPID kan stoppas. En uppmärksamhetssignal kan visas i session Utökade händelser 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 Nej. Löses inte förrän klienten hämtar alla rader eller stänger anslutningen. SPID kan stoppas, men det kan ta upp till 30 sekunder. Om open_transaction_count = 0 och SPID innehåller lås 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 kan stoppas, men kan ta upp till 30 sekunder. Kolumnen hostname i sys.dm_exec_sessions för SPID i huvudet på en blockeringskedja är densamma som en av SPID:en som den blockerar.
5 NULL >0 återställning Ja. En uppmärksamhetssignal kan visas i sessionen Utökade händelser för denna SPID, som anger att en tidsgräns för frågor eller avbrutna frågor har inträffat, eller så har en återställningsuttryck utfärdats.
6 NULL >0 viloläge Så småningom. När Windows NT fastställer att sessionen inte längre är aktiv bryts anslutningen. Värdet last_request_start_time i sys.dm_exec_sessions är mycket tidigare än den aktuella tiden.

Detaljerade blockeringsscenarier

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

I det här scenariot har en fråga som körs aktivt fått lås och låsen släpps inte (de påverkas av transaktionsisoleringsnivån). Så andra sessioner väntar på låsen tills de släpps.

Lösning:

Optimera frågan för att lösa det här blockeringsproblemet. Den här typen av blockeringsproblem kan vara ett prestandaproblem. Behandla 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 ställ in för prestanda.

Rapporter som är inbyggda i SSMS från Query Store (som introducerades i SQL Server 2016) är också ett starkt rekommenderat och värdefullt verktyg för att identifiera de mest kostsamma frågorna och de suboptimala körningsplanerna.

Om du har en långvarig fråga som blockerar andra användare och du inte kan optimera den kan du överväga att flytta den från en OLTP-miljö till ett dedikerat rapporteringssystem. Du kan också använda AlwaysOn-tillgänglighetsgrupper för att synkronisera en skrivskyddad replikering av databasen.

Kommentar

Blockering under frågekörningen kan orsakas av frågeeskalering, ett scenario när rad- eller sidlås eskaleras till tabellås. Microsoft SQL Server avgör dynamiskt när låseskalering ska utföras. Det enklaste och säkraste sättet att förhindra låseskalering är att hålla transaktionerna korta och minska låsfotavtrycket för dyra frågor så att tröskelvärdena för låseskalering inte överskrids. Mer information om hur du identifierar och förhindrar överdriven låseskalering finns i Lösa blockeringsproblem som orsakas av låseskalering.

Scenario 2: Blockering orsakad av en vilande SPID som har en obekräftad transaktion

Du kan ofta identifiera den här typen av blockering av en SPID som ligger i viloläge eller väntar på ett kommando med en transaktions kapslingsnivå (@@TRANCOUNTfrån open_transaction_countsys.dm_exec_requests) som är större än noll. Detta kan inträffa om programmet överskrider tidsgränsen för frågor eller avbryter utan att även utfärda det antal ROLLBACK- och/eller COMMIT-uttryck som krävs. När en SPID tar emot en tidsgräns för frågor eller ett avbrott avslutas den aktuella frågan och batchen men återställs inte automatiskt eller genomför transaktionen. Programmet ansvarar för den här åtgärden eftersom SQL Server inte kan anta att en hel transaktion måste återställas på grund av att en enskild fråga avbryts. Tidsgränsen för frågan eller avbryts visas som en ATTENTION-signalhändelse för SPID i den utökade händelsesessionen.

För att demonstrera en explicit transaktion som inte har skickats utfärdar du följande fråga:

CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
GO
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 transaktionsantalet är en. Alla lås som införskaffats i transaktionen hålls kvar tills transaktionen har genomförts 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:

  • Den här klassen av blockeringsproblem kan också vara ett prestandaproblem. Om du kan minska frågekörningstiden kanske inte tidsgränsen för frågan eller avbrytas. Det är viktigt att programmet kan hantera tidsgränsen 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 också på följande:

    • 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. Kontroll av öppna transaktioner krävs eftersom en lagrad procedur som anropas under batchen kunde ha startat en transaktion utan klientprogrammets vetskap. Vissa villkor, som att avbryta frågan, förhindrar proceduren från att exekvera förbi den aktuella satsen, så även om proceduren har logik för att kontrollera IF @@ERROR <> 0 och avbryta transaktionen, körs denna kod för återställning inte i sådana fall.

    • Om du använder anslutningspooler i ett program som öppnar anslutningen och kör några frågor innan du släpper anslutningen 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 den SQL Server 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 (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 förblir ö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 att använda SET XACT_ABORT ON för att undvika den här potentiella fördröjningen.

Varning

Efter SET XACT_ABORT ON kommer T-SQL-uttryck efter ett uttryck som orsakar ett fel inte att köras. Detta kan påverka det avsedda flödet av befintlig kod.

Scenario 3: Blockering orsakad av en SPID vars motsvarande klientprogram inte hämtade alla resultatrader till slutförande

Efter att ha skickat en fråga till servern måste alla applikationer omedelbart hämta alla resultatrader till slutförandet. Om ett program inte hämtar alla resultatrader kan det lämna lås på tabellerna som blockerar andra användare. Om du använder ett program som transparent skickar SQL-uttryck 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. Undvik problemet genom att begränsa program med dåligt uppförande till en rapport eller en beslutsstödsdatabas, separat från oltp-huvuddatabasen.

Lösning:

Skriv om programmet för att hämta alla rader i resultatet till slutförande. Detta utesluter inte användningen av OFFSET och FETCH i ORDER BY-satsen i en fråga för att utföra växling på serversidan.

Scenario 4: Blockering orsakad av en distribuerad klient/server-dödläge

Till skillnad från ett konventionellt deadlock kan ett distribuerat deadlock inte identifieras med hjälp av RDBMS-låshanteraren. Detta beror på att endast en av resurserna som är inblandade i deadlocket är ett SQL Server-lås. Den andra sidan av deadlocket är på klientprogramsnivå, där SQL Server inte har någon kontroll. I följande två avsnitt visas exempel på hur detta kan ske och möjliga sätt för programmet att undvika det.

Exempel A: Klient-/server-distribuerat dödläge med en enda klienttråd

Om klienten har flera öppna anslutningar och en enda körningstråd kan följande distribuerade dödläge inträffa. Observera att termen dbproc som används här refererar till klientanslutningsstrukturen.

 SPID1------blocked on lock------->SPID2
   /\ (waiting to write results back to client)
   | 
   | |
   | | Server side
   | ================================|==================================
   | <-- single thread --> | Client side
   | \/
   dbproc1 <------------------- dbproc2
   (waiting to fetch (effectively blocked on dbproc1, awaiting
   next row) single thread of execution to run)

I föregående fall har en enda klientprogramtråd två öppna anslutningar. Den skickar asynkront en SQL-åtgärd på dbproc1. Det innebär att den inte väntar på att anropet ska returneras innan du fortsätter. Programmet skickar sedan en annan SQL-åtgärd på dbproc2 och väntar på att resultaten ska börja bearbeta de returnerade data. När data börjar komma tillbaka (beroende på vilket dbproc som först svarar – anta att det här är dbproc1) bearbetas alla data som returneras på den dbprocen. Den hämtar resultat från dbproc1 tills SPID1 blockeras på ett lås som innehas av SPID2 (eftersom de två frågorna körs asynkront på servern). Nu väntar dbproc1 på obestämd tid på mer data. SPID2 blockeras inte på ett lås, men försöker skicka data till klienten dbproc2. Dbproc2 blockeras dock effektivt på dbproc1 på programlagret, eftersom den enda körningstråden för programmet används av dbproc1. Den här situationen resulterar i ett dödläge som SQL Server inte kan identifiera eller lösa eftersom endast en av resurserna är en SQL Server resurs.

Exempel B: Klient-/server-distribuerat dödläge med en tråd per anslutning

Även om det finns en separat tråd för varje anslutning på klienten kan en variant av det här distribuerade dödläget fortfarande inträffa, vilket visas i följande exempel.

SPID1------blocked on lock-------->SPID2
  /\ (waiting on net write) Server side
  | |
  | |
  | INSERT |SELECT
  | ================================|==================================
  | <-- thread per dbproc --> | Client side
  | \/
  dbproc1 <-----data row------- dbproc2
  (waiting on (blocked on dbproc1, waiting for it
  insert) to read the row from its buffer)

Det här fallet liknar exempel A, förutom att dbproc2 och SPID2 kör en SELECT-instruktion med avsikten att utföra rad-i-tid-bearbetning och överlämna varje rad via en buffert till dbproc1 för en INSERT-, UPDATE- eller DELETE-instruktion i samma tabell. Så småningom blockeras SPID1 (som utför INSERT, UPDATE eller DELETE) på ett lås som innehas av SPID2 (som utför SELECT). SPID2 skriver en resultatrad till klienten dbproc2. Dbproc2 försöker sedan skicka raden i en buffert till dbproc1, men finner att dbproc1 är upptagen (den blockeras i väntan på SPID1 ska slutföra den aktuella INSERT, som blockeras på SPID2). Nu blockeras dbproc2 i programlagret av dbproc1 vars SPID (SPID1) blockeras på databasnivå av SPID2. Detta resulterar återigen i ett dödläge som SQL Server inte kan identifiera eller lösa eftersom endast en av de berörda resurserna är en SQL Server-resurs.

Båda exemplen A och B är grundläggande problem som programutvecklare måste känna till. De måste koda program för att hantera dessa fall på lämpligt sätt.

Lösning:

När du anger en tidsgräns för frågor, om det distribuerade dödläget inträffar, bryter tidsgränsen dödläget. Mer information om hur du använder en tidsgräns för frågor finns i dokumentationen för anslutningsprovidern.

Scenario 5: Blockering orsakad av en session i återställningstillstånd

När du avslutar eller avbryter en dataändringsfråga utanför en användardefinierad transaktion återställs frågan. Den här återställningen kan också inträffa som en bieffekt av att klientnätverkssessionen kopplas från, klientdatorn startas om eller den begäran som väljs som ett dödlägesoffer. Du kan ofta inte återställa en dataändringsfråga snabbare än vad ändringarna ursprungligen tillämpades. Om till exempel en DELETE- , INSERT- eller UPDATE -instruktion kördes i en timme kan det ta minst en timme att återställa. Det här beteendet är förväntat eftersom transaktionell och fysisk integritet i databasen skulle komprometteras om ändringarna inte återställs. Eftersom den här återställningen måste slutföras markerar SQL Server sessionen i tillståndet KILLED/ROLLBACK och du kan inte avsluta sessionen igen eller välja den som ett dödlägesoffer. Du kan ofta identifiera det här tillståndet genom att observera utdata sys.dm_exec_requestsfrån , där kolumnrapporterna percent_completecommandKILLED/ROLLBACK och kolumnen visar förloppet.

Kommentar

Långa återställningar är sällsynta när funktionen Accelererad databasåterställning är aktiverad. Den här funktionen har lagts till i SQL Server 2019.

Lösning:

Vänta tills sessionen har återställt ändringarna.

Om du stänger av instansen mitt i den här åtgärden är databasen i återställningsläge vid omstart och den är otillgänglig tills alla öppna transaktioner bearbetas. Startåterställning tar i stort sett samma tid per transaktion som körningsåterställning, och databasen är otillgänglig under den här perioden. Därför är det ofta kontraproduktivt att tvinga ned servern för att åtgärda en SPID i återställningstillstånd. I SQL Server 2019 med Accelererad databasåterställning aktiverad bör den här situationen inte inträffa.

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

Scenario 6: Blockering som orsakas av en överbliven anslutning

Det här är ett vanligt problemscenario och överlappar delvis scenario 2. Om klientprogrammet stoppas, klientarbetsstationen startas om eller om det uppstår ett batch-avbrutet fel kan alla dessa lämna en transaktion öppen. Den här situationen kan inträffa om programmet inte återställer transaktionen i programmets CATCH eller FINALLY blockerar eller om det inte hanterar den här situationen på annat sätt.

I det här scenariot, medan körningen av en SQL-batch har avbrutits, lämnas SQL-anslutningen och transaktionen öppna av programmet. Från SQL Server-instansens perspektiv verkar klienten fortfarande finnas och eventuella lås som hämtas kan fortfarande behållas.

Om du vill demonstrera en överbliven transaktion kör du följande fråga, som simulerar ett batch-avbrutet fel genom att infoga data i en tabell som inte finns:

CREATE TABLE #test2 (col1 INT);
INSERT INTO #test2 SELECT 1;
go
BEGIN TRAN
UPDATE #test2 SET col1 = 2 where col1 = 1;
INSERT INTO #NonExistentTable values (10)

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

SELECT @@TRANCOUNT;

Utdata från den andra frågan anger att transaktionsantalet är en. Alla lås som införskaffats i transaktionen hålls kvar tills transaktionen har genomförts eller återställts. Eftersom batchen redan har avbrutits av frågan kan programmet som kör den fortsätta att köra andra frågor i samma session utan att rensa upp transaktionen som fortfarande är öppen. Låset hålls kvar tills sessionen avbryts eller SQL Server-instansen startas om.

Lösningar:

  • Det bästa sättet att förhindra det här villkoret är genom att förbättra programfel och undantagshantering, särskilt för oväntade avslutningar. Se till att du använder ett Try-Catch-Finally block i programkoden och återställ transaktionen i händelse av ett undantag.
  • Överväg att använda 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 som avbryter batchen återställer den här inställningen automatiskt alla öppna transaktioner och returnerar kontrollen till klienten. Mer information finns i SET XACT_ABORT (Transact-SQL).
  • Om du vill lösa en överbliven anslutning för ett klientprogram som har kopplats från utan att rensa resurserna på rätt sätt kan du avsluta SPID med hjälp av kommandot KILL. Mer information finns i Avsluta (Transact-SQL).

Kommandot KILL tar SPID-värdet som indata. Om du till exempel vill avsluta SPID 9 kör du följande kommando:

KILL 99

Kommentar

Kommandot KILL kan ta upp till 30 sekunder att slutföra på grund av intervallet mellan kontrollerna för kommandot KILL.

Relaterat innehåll

  • Last updated on