Dela via

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

  • Artikel

Gäller för: SQL Server (alla versioner som stöds), Azure SQL Managed Instance

Ursprungligt KB-nummer: 224453

Mål

Artikeln beskriver blockering i SQL Server 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 SQL Server 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.

Kommentar

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

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 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 ä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 någon märkbar effekt på systemets prestanda.

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. Om frågan inte körs inom en transaktion (och inga låstips används) kommer låsen för SELECT-instruktioner endast att lagras på en resurs när den faktiskt läses, inte under frågan. 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 transaktion bestäms varaktigheten för låsen 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.

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ågereducering, 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 SQL Server, 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 SQL Server stödja tusentals samtidiga användare på en enda server, med liten blockering.

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 på SQL Server. Det finns två kompletterande metoder för att samla in dessa data.

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 ä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

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.

För att göra detta kan du 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 spetsen för en blockeringskedja. Om du expanderar transaktionen visar rapporten de transaktioner som blockeras av huvudtransaktionen. Den här rapporten visar också sql-instruktionen Blockering och den blockerade SQL-instruktionen.

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

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

  • 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 sys.dm_exec_requests kolumnen och blocking_session_id . 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. Tänk på att returneras av sys.dm_exec_requests, frågan måste 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 sys.dm_exec_sql_text eller sys.dm_exec_input_buffer DMV:er. Om data som returneras av text kolumnen sys.dm_exec_sql_text i är NULL körs inte frågan för närvarande. I så fall innehåller kolumnen sys.dm_exec_input_buffer i event_info 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 Server-trådens/uppgiftsskiktet. Detta returnerar information om vad SQL wait_type 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.

  • 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 på en SQL Server-produktionsinstans 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. Go-to-verktyget för CSS för att felsöka sådana problem är att använda PSSDiag-datainsamlaren. 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 läsa den senaste offentliga versionen av DiagManager på GitHub.

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

Förutom ovanstående information ä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 SQL Server. 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-spårningar med SQL Server Profiler är dock inaktuella. XEvents är den nyare, överlägsna spårningsplattformen som ger mer mångsidighet och mindre påverkan på det observerade systemet, och dess gränssnitt är integrerat i SSMS.

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

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

**Om du vill konfigurera tröskelvärdet och frekvensen med vilken blockerade processrapporter genereras använder du kommandot sp_configure för att konfigurera alternativet för blockerad procesströskel, 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 informationen ovan 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 kolumner:

    • 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 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 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. Den öppna transaktionen kan ha skapats antingen av en aktiv instruktion eller av en instruktionsbegäran som har körts 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 och last_wait_type värdet 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 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

      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 TAB: 5:261575970:1 I det här fallet är databas-ID 5 exempeldatabasen pubar och object_id 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.
      Nyckel 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.
      Rad 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
, 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 (hostname), på vissa nätverksbibliotek (client_interface_name), 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 wait_type, open_transaction_countoch 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ö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 Nej. 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 COMMITTED) är detta en sannolik orsak.
4 Det varierar >= 0 Körbar Nej. 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 spid-koden som den blockerar.
5 NULL >0 ångring Ja. En uppmärksamhetssignal kan visas i sessionen Extended Events för denna SPID, som anger 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 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 hämtat lås och låsen släpps inte (det påverkas av transaktionsisoleringsnivån). Så andra sessioner väntar på låsen tills de släpps.

Lösning:

Lösningen på det här blockeringsproblemet är att leta efter sätt att optimera frågan. Den här klassen av blockeringsproblem kan vara ett prestandaproblem och kräver att du behandlar 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 som är inbyggda i SSMS från Query Store (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 den inte kan optimeras 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 replik av databasen.

Kommentar

Blockering under frågekörning kan orsakas av frågeeskalering, ett scenario när rad- eller sidlås eskaleras till tabelllå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 som orsakas 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 med en transaktions kapslingsnivå (@@TRANCOUNTfrån open_transaction_count sys.dm_exec_requests) som är större än noll. Den här situationen kan inträffa om programmet drabbas av en tidsgräns för frågor eller avbryter utan att behöva utfärda det antal ROLLBACK- och/eller COMMIT-instruktioner 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 detta 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 eller avbryt frågan visas som en ATTENTION-signalhändelse för SPID i sessionen Extended Event.

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;
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 ett. Alla lås som införskaffats i transaktionen lagras fortfarande 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:

  • Den här klassen av blockeringsproblem kan också vara ett prestandaproblem och kräver att du behandlar det som sådant. Om frågekörningstiden kan minskas 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 IF @@TRANCOUNT > 0 ROLLBACK TRAN du följande fel, även om klientprogrammet inte tror att en transaktion är öppen. Sökning efter öppna transaktioner krävs eftersom en lagrad procedur som anropas 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 anslutningen en fysisk frånkoppling av 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. 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.

Varning

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

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 finnas kvar i tabellerna, vilket blockerar andra användare. Om du använder ett program som transparent skickar SQL-instruktioner till servern måste programmet hämta alla resultatrader. Om det inte gör det (och om det 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.

Lösning:

Programmet måste skrivas om 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 ett distribuerat klient-/server-dödläge

Till skillnad från ett konventionellt dödläge går det inte att identifiera ett distribuerat dödläge med RDBMS-låshanteraren. Det beror på att endast en av de resurser som ingår i dödläget är ett SQL Server-lås. Den andra sidan av dödläget är på klientprogramnivå, som SQL Server inte har någon kontroll över. Följande två avsnitt visar exempel på hur detta kan hända 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 det fall som visas ovan har en enda klientprogramtråd två öppna anslutningar. Den skickar asynkront en SQL-åtgärd på dbproc1. Det innebär att det 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. Detta 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: Distribuerat dödläge för klient/server 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 enligt följande.

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-en-tid-bearbetning och överlämna varje rad via en buffert till dbproc1 för en INSERT, UPDATEeller DELETE -instruktion i samma tabell. Så småningom blockeras SPID1 (utför INSERT, UPDATEeller DELETE) på ett lås som innehas av SPID2 (utför SELECT). SPID2 skriver en resultatrad till klienten dbproc2. Dbproc2 försöker sedan skicka raden i en buffert till dbproc1, men hittar att dbproc1 är upptagen (den blockeras väntar på SPID1 för att slutföra den aktuella INSERT, som är blockerad på SPID2). I det här läget blockeras dbproc2 på programlagret av dbproc1 vars SPID (SPID1) blockeras på databasnivå av SPID2. Återigen resulterar detta i ett dödläge som SQL Server inte kan identifiera eller lösa eftersom endast en av 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å rätt sätt.

Lösning:

När en tidsgräns för frågor har angetts, om det distribuerade dödläget inträffar, bryts den när tidsgränsen uppnås. Mer information om hur du använder en tidsgräns för frågor finns i dokumentationen för anslutningsprovidern.

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

En dataändringsfråga som avbryts 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å ÅTERSTÄLLNING command, och percent_complete kolumnen kan visa förlopp.

En dataändringsfråga som avbryts eller avbryts utanför en användardefinierad transaktion återställs. Detta kan också inträffa som en bieffekt av att klientdatorn startas om och nätverkssessionen kopplas från. På samma sätt återställs en fråga som valts som ett dödlägesoffer. En dataändringsfråga kan ofta inte återställas snabbare än vad ändringarna ursprungligen tillämpades. Om till exempel en DELETE- , INSERT- eller UPDATE -instruktion hade körts i en timme kan det ta minst en timme att återställa. Detta är ett förväntat beteende eftersom de ändringar som görs måste återställas, eller så komprometteras transaktionell och fysisk integritet i databasen. Eftersom detta måste inträffa markerar SQL Server SPID i ett gyllene tillstånd eller återställningstillstånd (vilket innebär att den inte kan avlivas eller väljas som ett dödlägesoffer). Detta kan ofta identifieras genom att observera utdata sp_whofrån , vilket kan tyda på ROLLBACK-kommandot. Kolumnen status i sys.dm_exec_sessions anger rollback-status.

Kommentar

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

Lösning:

Du måste vänta tills sessionen har slutförts och återställa de ändringar som har gjorts.

Om instansen stängs av mitt i den här åtgärden är databasen i återställningsläge när den startas om 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 aktiverat bör detta inte inträffa.

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.

Scenario 6: Blockering orsakad av en överbliven transaktion

Det här är ett vanligt problemscenario och överlappar delvis scenario 2. Om klientprogrammet stoppas startas klientarbetsstationen 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 blocken 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ämnar programmet SQL-transaktionen öppen. Från SQL Server-instansens perspektiv verkar klienten fortfarande finnas och alla lås som hämtas behålls.

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 obefintlig tabell:

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 ett. Alla lås som hämtas i transaktionen lagras fortfarande tills transaktionen har checkats in eller återställts. Eftersom batchen redan har avbrutits av frågan kan det program som kör den fortsätta att köra andra frågor i samma session utan att rensa den transaktion som fortfarande är öppen. Låset kommer att hållas tills sessionen har avlivats eller SQL Server-instansen har startats om.

Lösningar:

  • Det bästa sättet att förhindra det här villkoret är genom att förbättra hanteringen av programfel/undantag, 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 sessionen 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 behöva rensa resurserna på rätt sätt kan du avsluta SPID med hjälp KILL av kommandot . Referens finns i KILL (Transact-SQL).

Kommandot KILL tar SPID-värdet som indata. Om du till exempel vill döda 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 KILL kommandot.

Se även