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 kallas ofta för en process, även om det inte är en separat processkontext i vanlig mening. Varje SPID består snarare 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 enda 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

I den här artikeln ligger fokus på SQL Server-instanser, inklusive Azure SQL Managed Instances. 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 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åsnings-, låstips- och transaktionsisoleringsnivåer finns i följande artiklar:

När låsning och blockering kvarstår till den punkt där systemets prestanda påverkas negativt beror det 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

Det kan finnas en tendens att fokusera på justering på serversidan och plattformsproblem vid blockeringsproblem. Men uppmärksamhet som ägnas endast databasen kanske inte leder till en lösning, och kan uppta tid och energi på ett bättre sätt om den riktas mot att undersöka klientprogrammet och de frågor som den skickar. Oavsett vilken synlighetsnivå programmet exponerar för de databasanrop som görs kräver ett blockeringsproblem ofta både kontroll av de exakta SQL-uttryck som skickas av programmet och programmets exakta beteende när det gäller frågeavbokning, anslutningshantering, hämtning av alla resultatrader och så vidare. Om utvecklingsverktyget inte tillåter explicit kontroll över anslutningshantering, frågeavbokning, tidsgräns för frågor, resultathämtning och så vidare kanske blockeringsproblemen 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 konstruktionsfasen för 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 bör vara så enkel som möjligt. Bra disciplin för anslutningshantering måste finnas, utan det kan programmet verka ha acceptabel prestanda för ett lågt antal användare, men prestandan kan försämras avsevärt när antalet användare ökar.

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 det som kommer att diktera resten av den här artikelns sammansättning. 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 (d.v.s. vad som låser 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 låser under en längre period)

  3. Analysera/förstå varför den långvariga blockeringen sker

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

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.

Det första är att fråga 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 av blockeringskedjan och SQL-uttrycket. Leta efter SPID som utsatts för blockering. Om en SPID blockeras av en annan SPID undersöker du den SPID som äger resursen (den blockerande SPID:en). Blockeras även den ägarens SPID? Du kan gå igenom kedjan för att hitta huvudblockeraren och sedan undersöka varför den behåller låsningen.

Detta gör du 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 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 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 det sista uttrycket 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 fråga 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. Medan sys.dm_exec_requests endast visar begäranden som körs nu, 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 frågan måste köras aktivt med SQL Server för att returneras av sys.dm_exec_requests.

  • 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 sys.dm_exec_sql_text i text ä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 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-/uppgiftsskiktet i SQL Server. Detta 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:n 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 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. En av de tidigare instruktionerna kan dock vara orsaken till att lås fortfarande hålls kvar. Med en spårning kan du se alla kommandon som körs av en session i den aktuella transaktionen.

Det finns två sätt att samla in spårningar i SQL Server. Extended Events (XEvents) och Profiler Traces. SQL-spårningar som använder 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 som är 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 egna anpassade extended event-sessioner i SSMS, se guiden Extended Event ny session. 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 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 informationen ovan kan du fastställa orsaken till de flesta blockeringsproblem. 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 (refererat 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 tydligt vilka begäranden som blockeras och vilka som blockerar. 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 vilolägesstatus 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 ligger SPID inom en öppen transaktion och kan innehålla lås som förvärvats 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

      Om sys.dm_exec_requests.wait_type är NULL väntar inte begäran för närvarande på något och värdet last_wait_type anger det sista wait_type som begäran stötte på. 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 tabellen sys.dm_exec_requests returnerar ett värde i kolumnen wait_time som är mindre än värdet wait_time från en tidigare fråga i sys.dm_exec_requests, anger 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 jämföra wait_resource 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.
      Kompilera 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:

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ä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 ställ in 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 dyraste frågorna och icke-optimala körningsplaner.

Om du har en långvarig fråga som blockerar andra användare och inte kan optimeras, överväg 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

Den här typen av blockering kan ofta identifieras av en SPID som är i viloläge eller väntar på ett kommando, men vars kapslingsnivå för transaktioner (@@TRANCOUNT, open_transaction_count från sys.dm_exec_requests) ä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 en avbruten fråga avslutas den aktuella frågan och batchen, men den återställs inte automatiskt eller utför transaktionen. Programmet ansvarar för detta eftersom SQL Server inte kan förutsätta att en hel transaktion måste återställas på grund av att en enskild fråga avbryts. Frågans timeout eller avbrytning visas som en ATTENTION-signalhändelse för SPID i sessionen Utökade händelser.

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:

  • Dessutom kan den här klassen av blockeringsproblem också vara ett prestandaproblem och kräva att du behandlar den som sådant. Om frågekörningstiden kan minskas uppstår inte frågans tidsgräns eller avbrytning. 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 kapslingsnivåer för transaktioner korrekt, eller så kan de orsaka ett blockeringsproblem 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 anslutningspoolen 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 lösa problemet genom att tillfälligt inaktivera anslutningspooler tills klientprogrammet ändras för att hantera felen på rätt sätt. Genom att inaktivera 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. I händelse av ett körtidsfel avbryter denna inställning alla öppna transaktioner och återför 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 lås lämnas kvar i tabellerna, vilket 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. För att undvika problemet kan du begränsa program med dåligt uppförande till en rapporteringsdatabas eller en beslutsstödsdatabas, separat från huvuddatabasen för OLTP.

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 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ägen uppstå. 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 klientprogramstrå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 detta är dbproc1) bearbetar den för att slutföra alla data som returneras på den dbproc. 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: 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 deadlock 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-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 en tidsgräns för frågor har angetts bryts den när tidsgränsen uppnås om det distribuerade dödläget inträffar. 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

En dataändringsfråga som är avslutad eller avbruten 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 lägsta prioritet. Detta kan ofta identifieras genom att observera utdata från sys.dm_exec_requests, vilket kan tyda på återställning command, och kolumnen percent_complete kan visa förlopp.

En dataändringsfråga som är avslutad eller avbruten 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 lägsta prioritet. En dataändringsfråga kan ofta inte återställas snabbare än vad ändringarna ursprungligen tillämpades. Om till exempel en INSERT, UPDATE eller DELETE-instruktion hade körts i en timme kan det ta minst en timme att återställa. Detta är förväntat eftersom de ändringar som görs måste återställas, annars skulle transaktionell och fysisk integritet i databasen komprometteras. Eftersom detta måste inträffa markerar SQL Server SPID i ett gyllene tillstånd eller återställningstillstånd (vilket innebär att det inte kan avslutas eller väljas som lägsta prioritet). Detta kan ofta identifieras genom att observera utdata från sp_who, vilket kan tyda på kommandot återställning. Kolumnen status i sys.dm_exec_sessions anger återställningsstatus.

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:

Du måste vänta tills sessionen har återställt ändringarna som gjorts.

Om instansen stängs av mitt i den här åtgärden är databasen i återställningsläge vid omstart och den är otillgänglig tills alla öppna transaktioner bearbetats. 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 ske.

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 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 anslutningen eller i lagrade procedurer som påbörjar transaktioner och inte rensar upp efter ett fel. I händelse av ett körtidsfel som avbryter denna batch återställer denna inställning alla öppna transaktioner och återför 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.

Se även