Delen via

Inzicht in SQL Server-blokkeringsproblemen en deze oplossen

  • Artikel

Van toepassing op: SQL Server (alle ondersteunde versies), Azure SQL Managed Instance

Oorspronkelijk KB-nummer: 224453

Doelstelling

In het artikel wordt beschreven hoe u blokkeringen in SQL Server kunt oplossen en hoe u blokkeringen kunt oplossen.

In dit artikel verwijst de term verbinding naar één aangemelde sessie van de database. Elke verbinding wordt weergegeven als een sessie-id (SPID) of session_id in veel DMV's. Elk van deze SPID's wordt vaak aangeduid als een proces, hoewel het geen afzonderlijke procescontext is in de gebruikelijke zin. In plaats daarvan bestaat elke SPID uit de serverbronnen en gegevensstructuren die nodig zijn om de aanvragen van één verbinding van een bepaalde client te verwerken. Een enkele clienttoepassing kan een of meer verbindingen hebben. Vanuit het perspectief van SQL Server is er geen verschil tussen meerdere verbindingen van één clienttoepassing op één clientcomputer en meerdere verbindingen vanuit meerdere clienttoepassingen of meerdere clientcomputers; Ze zijn atomisch. Eén verbinding kan een andere verbinding blokkeren, ongeacht de bronclient.

Notitie

Dit artikel is gericht op SQL Server-exemplaren, waaronder Azure SQL Managed Instances. Zie Understand and resolve Azure SQL Database blocking problems (Problemen met blokkeren in Azure SQL Database begrijpen en oplossen) voor informatie over het oplossen van problemen met blokkeren in Azure SQL Database.

Wat is blokkering

Blokkering is een onvermijdbaar en zo ontworpen kenmerk van elk relationeel databasebeheersysteem (RDBMS) met gelijktijdigheid op basis van vergrendeling. Zoals eerder vermeld, treedt in SQL Server blokkeren op wanneer één sessie een vergrendeling op een specifieke resource bevat en een tweede SPID probeert een conflicterend vergrendelingstype op dezelfde resource te verkrijgen. Normaal gesproken is het tijdsbestek waarvoor de eerste SPID de resource vergrendelt, klein. Wanneer de sessie die eigenaar is, de vergrendeling vrijgeeft, is de tweede verbinding vervolgens vrij om een eigen vergrendeling op de resource te verkrijgen en kan de verwerking worden voortgezet. Blokkeren zoals hier wordt beschreven, is normaal gedrag en kan gedurende de loop van een dag vaak plaatsvinden zonder merkbaar effect op systeemprestaties.

De duur en transactiecontext van een query bepalen hoe lang de vergrendelingen worden bewaard en daarmee hun effect op andere query's. Als de query niet binnen een transactie wordt uitgevoerd (en er geen vergrendelingshints worden gebruikt), worden de vergrendelingen voor SELECT-instructies alleen opgeslagen op een resource op het moment dat deze daadwerkelijk wordt gelezen, niet tijdens de query. Voor INSERT-, UPDATE- en DELETE-instructies worden de vergrendelingen bewaard tijdens de query, zowel voor gegevensconsistentie als om de query indien nodig terug te draaien.

Voor query's die worden uitgevoerd binnen een transactie, wordt de duur van de vergrendelingen bepaald door het type query, het isolatieniveau van de transactie en of vergrendelingshints worden gebruikt in de query. Zie de volgende artikelen voor een beschrijving van vergrendelings-, vergrendelingshints en transactieisolatieniveaus:

Wanneer het vergrendelen en blokkeren zich blijft voordoen tot het punt waar er een schadelijk effect op de systeemprestaties is, is dit een van de volgende redenen:

  • Een SPID bevat vergrendelingen voor een set resources gedurende een langere periode voordat ze worden vrijgegeven. Dit type blokkeren lost zichzelf na verloop van tijd op, maar kan leiden tot prestatievermindering.

  • Een SPID bevat vergrendelingen op een set resources en brengt ze nooit vrij. Dit type blokkeren lost zichzelf niet op en voorkomt de toegang tot de betrokken resources voor onbepaalde tijd.

In het eerste scenario kan de situatie erg vloeiend zijn omdat verschillende SPID's in de loop van de tijd blokkeren op verschillende resources, waardoor een bewegend doel ontstaat. Deze situaties zijn moeilijk op te lossen met behulp van SQL Server Management Studio om het probleem te beperken tot afzonderlijke query's. De tweede situatie resulteert daarentegen in een consistente status die gemakkelijker te diagnosticeren is.

Toepassingen en blokkeren

Er kan een tendens zijn om zich te richten op het afstemmen aan de serverzijde en platformproblemen bij het ondervinden van een blokkeringsprobleem. Aandacht die alleen aan de database wordt besteed, kan echter niet leiden tot een oplossing en kan tijd en energie opnemen die beter is gericht op het onderzoeken van de clienttoepassing en de query's die deze verzendt. Ongeacht het zichtbaarheidsniveau dat door de toepassing wordt weergegeven met betrekking tot de databaseaanroepen, vereist een blokkerend probleem echter regelmatig zowel de inspectie van de exacte SQL-instructies die door de toepassing zijn ingediend als het exacte gedrag van de toepassing met betrekking tot het annuleren van query's, verbindingsbeheer, het ophalen van alle resultaatrijen, enzovoort. Als het ontwikkelprogramma geen expliciete controle toestaat over verbindingsbeheer, annulering van query's, time-out van query's, ophalen van resultaten, enzovoort, kunnen blokkeringsproblemen mogelijk niet worden opgelost. Dit potentieel moet grondig worden onderzocht voordat u een hulpprogramma voor toepassingsontwikkeling voor SQL Server selecteert, met name voor prestatiegevoelige OLTP-omgevingen.

Let op databaseprestaties tijdens de ontwerp- en bouwfase van de database en toepassing. Met name het resourceverbruik, het isolatieniveau en de lengte van het transactiepad moeten voor elke query worden geëvalueerd. Elke query en transactie moeten zo licht mogelijk zijn. Er moet een goede discipline voor verbindingsbeheer worden uitgevoerd, zonder dat de toepassing acceptabele prestaties lijkt te hebben bij een laag aantal gebruikers, maar de prestaties kunnen aanzienlijk afnemen naarmate het aantal gebruikers omhoog wordt geschaald.

Met het juiste toepassings- en queryontwerp kan SQL Server vele duizenden gelijktijdige gebruikers op één server ondersteunen, met weinig blokkeren.

Problemen met blokkeren oplossen

Ongeacht de blokkerende situatie waarin we zich bevinden, is de methodologie voor het oplossen van vergrendelingen hetzelfde. Deze logische scheidingen bepalen de rest van de samenstelling van dit artikel. Het concept is om de hoofdblokkering te vinden en te bepalen wat die query doet en waarom deze wordt geblokkeerd. Zodra de problematische query is geïdentificeerd (wat vergrendelingen voor de langere periode vasthoudt), is de volgende stap het analyseren en bepalen waarom de blokkering plaatsvindt. Nadat we hebben begrepen waarom, kunnen we vervolgens wijzigingen aanbrengen door de query en de transactie opnieuw te ontwerpen.

Stappen bij het oplossen van problemen:

  1. De hoofdblokkeringssessie identificeren (hoofdblokkering)

  2. Zoek de query en transactie die de blokkering veroorzaken (wat vergrendelingen voor een langere periode vasthoudt)

  3. Analyseren/begrijpen waarom de langdurige blokkering plaatsvindt

  4. Blokkeringsprobleem oplossen door query's en transacties opnieuw te ontwerpen

Laten we nu bespreken hoe u de hoofdblokkeringssessie kunt aanwijzen met een geschikte gegevensopname.

Blokkerende informatie verzamelen

Om de problemen met blokkerende problemen tegen te gaan, kan een databasebeheerder SQL-scripts gebruiken die voortdurend de status van vergrendeling en blokkering op SQL Server bewaken. Er zijn twee gratis methoden om deze gegevens te verzamelen.

De eerste is het uitvoeren van query's op dynamische beheerobjecten (DMO's) en het opslaan van de resultaten voor vergelijking in de loop van de tijd. Sommige objecten waarnaar in dit artikel wordt verwezen, zijn dynamische beheerweergaven (DMV's) en sommige zijn dynamische beheerfuncties (DMF's).

De tweede is het gebruik van extended events (XEvents) of SQL Profiler Traces om vast te leggen wat er wordt uitgevoerd. Omdat SQL Trace en SQL Server Profiler zijn afgeschaft, is deze handleiding voor probleemoplossing gericht op XEvents.

Informatie verzamelen van DMV's

Als u verwijst naar DMV's om problemen met blokkeren op te lossen, is het doel om de SPID (sessie-id) aan het hoofd van de blokkerende keten en de SQL-instructie te identificeren. Zoek naar slachtoffer SPID's die worden geblokkeerd. Als een SPID wordt geblokkeerd door een andere SPID, onderzoekt u de SPID die eigenaar is van de resource (de blokkerende SPID). Wordt die eigenaar SPID ook geblokkeerd? U kunt de keten doorlopen om de hoofdblokkering te vinden en vervolgens te onderzoeken waarom het de vergrendeling onderhoudt.

Hiervoor kunt u een van de volgende methoden gebruiken:

  • Klik in SSMS (SQL Server Management Studio) Objectverkenner met de rechtermuisknop op het serverobject op het hoogste niveau, vouw Rapporten uit, vouw Standaardrapporten uit en selecteer vervolgens Activiteit - Alle blokkeringstransacties. Dit rapport toont de huidige transacties aan het hoofd van een blokkerende keten. Als u de transactie uitvouwt, worden in het rapport de transacties weergegeven die door de hoofdtransactie worden geblokkeerd. In dit rapport worden ook de blokkerings-SQL-instructie en de geblokkeerde SQL-instructie weergegeven.

  • Open Activiteitsmonitor in SSMS en raadpleeg de kolom Geblokkeerd door. Meer informatie over Activity Monitor vindt u hier.

Er zijn ook meer gedetailleerde op query's gebaseerde methoden beschikbaar met DMV's:

  • De sp_who opdrachten en sp_who2 opdrachten zijn oudere opdrachten om alle huidige sessies weer te geven. De DMV sys.dm_exec_sessions retourneert meer gegevens in een resultatenset die gemakkelijker te doorzoeken en te filteren is. U vindt sys.dm_exec_sessions de kern van andere query's.

  • Als u al een bepaalde sessie hebt geïdentificeerd, kunt u de DBCC INPUTBUFFER(<session_id>) laatste instructie vinden die door een sessie is ingediend. Vergelijkbare resultaten kunnen worden geretourneerd met de sys.dm_exec_input_buffer functie dynamisch beheer (DMF), in een resultatenset die gemakkelijker te doorzoeken en te filteren is, waardoor de session_id en de request_id worden verstrekt. Als u bijvoorbeeld de meest recente query wilt retourneren die is ingediend door session_id 66 en request_id 0:

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

  • Raadpleeg de sys.dm_exec_requests kolom en verwijs ernaar blocking_session_id . Wanneer blocking_session_id = 0, wordt een sessie niet geblokkeerd. Terwijl sys.dm_exec_requests alleen aanvragen worden weergegeven die momenteel worden uitgevoerd, worden alle verbindingen (actief of niet) vermeld in sys.dm_exec_sessions. Bouw voort op deze algemene join tussen sys.dm_exec_requests en sys.dm_exec_sessions in de volgende query. Houd er rekening mee dat de query actief moet worden uitgevoerd sys.dm_exec_requestsmet SQL Server.

  • Voer deze voorbeeldquery uit om de actieve query's en hun huidige SQL-batchtekst of invoerbuffertekst te vinden met behulp van de sys.dm_exec_sql_text of sys.dm_exec_input_buffer DMV's. Als de gegevens die worden geretourneerd door de text kolom sys.dm_exec_sql_text NULL zijn, wordt de query momenteel niet uitgevoerd. In dat geval bevat de kolom van sys.dm_exec_input_buffer de kolom de event_info laatste opdrachtreeks die is doorgegeven aan de SQL-engine. Deze query kan ook worden gebruikt om sessies te identificeren die andere sessies blokkeren, waaronder een lijst met session_ids geblokkeerd 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;
  • Voer deze uitgebreidere voorbeeldquery uit, geleverd door Microsoft Ondersteuning, om het hoofd van een keten met meerdere sessieblokkeringen te identificeren, inclusief de querytekst van de sessies die betrokken zijn bij een blokkeringsketen.
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];
  • Naslaginformatie sys.dm_os_waiting_tasks die zich in de thread-/taaklaag van SQL Server bevindt. Hiermee wordt informatie geretourneerd over wat SQL wait_type de aanvraag momenteel ondervindt. Net als sys.dm_exec_requests, worden alleen actieve aanvragen geretourneerd door sys.dm_os_waiting_tasks.

Notitie

Zie de DMV-sys.dm_db_wait_stats voor meer informatie over wachttypen, waaronder geaggregeerde wachtstatistieken in de loop van de tijd.

  • Gebruik de sys.dm_tran_locks DMV voor gedetailleerdere informatie over welke vergrendelingen zijn geplaatst door query's. Deze DMV kan grote hoeveelheden gegevens retourneren op een SQL Server-exemplaar in een productieomgeving en is handig voor het vaststellen van de vergrendelingen die momenteel worden bewaard.

Vanwege de INNER JOIN ingeschakeld sys.dm_os_waiting_tasks, beperkt de volgende query de uitvoer van sys.dm_tran_locks alleen tot momenteel geblokkeerde aanvragen, de wachtstatus en de vergrendelingen:

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>';

Met DMV's levert het opslaan van de queryresultaten in de loop van de tijd gegevenspunten waarmee u de blokkering gedurende een bepaald tijdsinterval kunt controleren om persistente blokkeringen of trends te identificeren. De go-to-tool voor CSS voor het oplossen van dergelijke problemen is het gebruik van de PSSDiag-gegevensverzamelaar. Dit hulpprogramma gebruikt de 'SQL Server Perf Stats' om resultatensets te verzamelen van DMV's waarnaar hierboven wordt verwezen, in de loop van de tijd. Aangezien dit hulpprogramma voortdurend in ontwikkeling is, bekijkt u de nieuwste openbare versie van DiagManager op GitHub.

Informatie verzamelen van uitgebreide gebeurtenissen

Naast de bovenstaande informatie is het vaak nodig om een tracering van de activiteiten op de server vast te leggen om een blokkeringsprobleem in SQL Server grondig te onderzoeken. Als een sessie bijvoorbeeld meerdere instructies binnen een transactie uitvoert, wordt alleen de laatste instructie weergegeven die is ingediend. Een van de eerdere verklaringen kan echter de reden zijn dat er nog steeds vergrendelingen worden bewaard. Met een tracering kunt u alle opdrachten zien die worden uitgevoerd door een sessie binnen de huidige transactie.

Er zijn twee manieren om traceringen vast te leggen in SQL Server; Uitgebreide gebeurtenissen (XEvents) en Profiler-traceringen. SQL-traceringen met behulp van de SQL Server Profiler worden echter afgeschaft. XEvents zijn het nieuwere, superieure traceringsplatform dat meer veelzijdigheid en minder impact op het waargenomen systeem mogelijk maakt en de interface is geïntegreerd in SSMS.

Er zijn vooraf gemaakte uitgebreide gebeurtenissessies klaar om te starten in SSMS, vermeld in Objectverkenner onder het menu voor XEvent Profiler. Zie XEvent Profiler voor meer informatie. U kunt ook uw eigen aangepaste uitgebreide gebeurtenissessies maken in SSMS. Zie de wizard Nieuwe sessie met uitgebreide gebeurtenissen. Voor het oplossen van blokkeringsproblemen leggen we meestal het volgende vast:

  • Categoriefouten:
    • Opmerking
    • Blocked_process_report**
    • Error_reported (kanaalbeheerder)
    • Exchange_spill
    • Execution_warning

**Als u de drempelwaarde en frequentie wilt configureren waarmee geblokkeerde procesrapporten worden gegenereerd, gebruikt u de opdracht sp_configure om de optie voor de drempelwaarde voor geblokkeerd proces te configureren, die in seconden kan worden ingesteld. Standaard worden er geen geblokkeerde procesrapporten geproduceerd.

  • Categoriewaarschuwingen:

    • Hash_warning
    • Missing_column_statistics
    • Missing_join_predicate
    • Sort_warning

  • Uitvoering van categorie:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • Categorievergrendeling

    • Lock_deadlock

  • Categoriesessie

    • Existing_connection
    • Aanmelden
    • Afmelden

Veelvoorkomende blokkeringsscenario's identificeren en oplossen

Door de bovenstaande informatie te bekijken, kunt u de oorzaak van de meeste blokkeringsproblemen bepalen. De rest van dit artikel is een discussie over het gebruik van deze informatie om enkele veelvoorkomende blokkeringsscenario's te identificeren en op te lossen. In deze discussie wordt ervan uitgegaan dat u de blokkeringsscripts (waarnaar eerder is verwezen) hebt gebruikt om informatie vast te leggen over de blokkerende SPID's en dat u toepassingsactiviteit hebt vastgelegd met behulp van een XEvent-sessie.

Blokkerende gegevens analyseren

  • Bekijk de uitvoer van de DMV's sys.dm_exec_requests en sys.dm_exec_sessions bepaal de hoofden van de blokkeringsketens, met behulp blocking_these van en session_id. Hiermee wordt duidelijk opgegeven welke aanvragen worden geblokkeerd en welke worden geblokkeerd. Bekijk verder de sessies die worden geblokkeerd en geblokkeerd. Is er een veelvoorkomende of hoofdmap voor de blokkerende keten? Ze delen waarschijnlijk een gemeenschappelijke tabel en een of meer sessies die betrokken zijn bij een blokkeringsketen, voeren een schrijfbewerking uit.

  • Bekijk de uitvoer van de DMV's en sys.dm_exec_sessions voor informatie over de SPID's sys.dm_exec_requests aan het hoofd van de blokkeringsketen. Zoek naar de volgende kolommen:

    • sys.dm_exec_requests.status

      In deze kolom ziet u de status van een bepaalde aanvraag. Normaal gesproken geeft een slaapstatus aan dat de SPID de uitvoering heeft voltooid en wacht totdat de toepassing een andere query of batch verzendt. Een uitvoerbare of actieve status geeft aan dat de SPID momenteel een query verwerkt. De volgende tabel bevat korte uitleg over de verschillende statuswaarden.

      Status Betekenis
      Achtergrond De SPID voert een achtergrondtaak uit, zoals impassedetectie, logboekschrijver of controlepunt.
      Slaapstatus De SPID wordt momenteel niet uitgevoerd. Dit geeft meestal aan dat de SPID wacht op een opdracht van de toepassing.
      Wordt uitgevoerd De SPID wordt momenteel uitgevoerd op een scheduler.
      Kan worden uitgevoerd De SPID bevindt zich in de uitvoerbare wachtrij van een scheduler en wacht tot de tijd van de scheduler wordt opgehaald.
      Onderbroken De SPID wacht op een resource, zoals een vergrendeling.

    • sys.dm_exec_sessions.open_transaction_count

      In deze kolom ziet u het aantal geopende transacties in deze sessie. Als deze waarde groter is dan 0, bevindt de SPID zich binnen een geopende transactie en kan deze sloten bevatten die zijn verkregen door een willekeurige instructie binnen de transactie. De open transactie kan zijn gemaakt door een momenteel actieve instructie of door een instructieaanvraag die in het verleden is uitgevoerd en niet langer actief is.

    • sys.dm_exec_requests.open_transaction_count

      Op dezelfde manier geeft deze kolom het aantal openstaande transacties in deze aanvraag aan. Als deze waarde groter is dan 0, bevindt de SPID zich binnen een geopende transactie en kan deze sloten bevatten die zijn verkregen door een actieve instructie binnen de transactie. In tegenstelling tot sys.dm_exec_sessions.open_transaction_count, als er geen actieve aanvraag is, wordt in deze kolom 0 weergegeven.

    • sys.dm_exec_requests.wait_type, en wait_timelast_wait_type

      Als de waarde sys.dm_exec_requests.wait_type NULL is, wacht de aanvraag momenteel niet op iets en geeft de last_wait_type waarde de laatste wait_type aan die de aanvraag heeft aangetroffen. Zie sys.dm_os_wait_stats voor meer informatie over sys.dm_os_wait_stats en een beschrijving van de meest voorkomende wachttypen. De wait_time waarde kan worden gebruikt om te bepalen of de aanvraag voortgang maakt. Wanneer een query op basis van de sys.dm_exec_requests tabel een waarde retourneert in de wait_time kolom die kleiner is dan de wait_time waarde van een eerdere query, geeft dit aan dat de eerdere vergrendeling is verkregen en vrijgegeven en nu wacht op een nieuwe vergrendeling (uitgaande van niet-nulwait_time).sys.dm_exec_requests Dit kan worden gecontroleerd door de wait_resource sys.dm_exec_requests uitvoer te vergelijken, waarin de resource wordt weergegeven waarvoor de aanvraag wacht.

    • sys.dm_exec_requests.wait_resource

      Deze kolom geeft de resource aan waarop een geblokkeerde aanvraag wacht. De volgende tabel bevat algemene wait_resource notaties en hun betekenis:

      Bron Format Voorbeeld Uitleg
      Tabel DatabaseID:ObjectID:IndexID TABBLAD: 5:261575970:1 In dit geval is database-id 5 de voorbeelddatabase pubs en object_id 261575970 de titelstabel is en 1 de geclusterde index.
      Pagina DatabaseID:FileID:PageID PAGINA: 5:1:104 In dit geval is database-id 5 pubs, bestand-id 1 het primaire gegevensbestand en pagina 104 is een pagina die hoort bij de titeltabel. Als u de object_id waartoe de pagina behoort wilt identificeren, gebruikt u de dynamische beheerfunctie sys.dm_db_page_info, waarbij u de DatabaseID, FileId, PageId van de wait_resourcepagina doorgeeft.
      Sleutel DatabaseID:Hobt_id (Hash-waarde voor indexsleutel) SLEUTEL: 5:72057594044284928 (3300a4f361aa) In dit geval is database-id 5 Pubs, Hobt_ID 72057594044284928 overeenkomt met index_id 2 voor object_id 261575970 (tabel titels). Gebruik de sys.partitions catalogusweergave om de hobt_id aan een bepaalde index_id en object_id. Er is geen manier om de hash van de indexsleutel ongedaan te maken naar een specifieke sleutelwaarde.
      Rij DatabaseID:FileID:PageID:Slot(rij) RID: 5:1:104:3 In dit geval is database-id 5 pubs, bestand-id 1 het primaire gegevensbestand, pagina 104 is een pagina die hoort bij de titeltabel en site 3 geeft de positie van de rij op de pagina aan.
      Compileren DatabaseID:FileID:PageID:Slot(rij) RID: 5:1:104:3 In dit geval is database-id 5 pubs, bestand-id 1 het primaire gegevensbestand, pagina 104 is een pagina die hoort bij de titeltabel en site 3 geeft de positie van de rij op de pagina aan.

    • sys.dm_tran_active_transactions De sys.dm_tran_active_transactions DMV bevat gegevens over openstaande transacties die kunnen worden toegevoegd aan andere DMV's voor een volledig beeld van transacties die wachten op doorvoer of terugdraaien. Gebruik de volgende query om informatie over geopende transacties te retourneren, die zijn toegevoegd aan andere DMV's, inclusief sys.dm_tran_session_transactions. Overweeg de huidige status transaction_begin_timevan een transactie en andere situatiegegevens om te evalueren of deze een bron van blokkering kunnen zijn.

      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;

    • Overige kolommen

      De resterende kolommen in sys.dm_exec_sessions en sys.dm_exec_request kunnen ook inzicht geven in de hoofdmap van een probleem. Hun nut varieert, afhankelijk van de omstandigheden van het probleem. U kunt bijvoorbeeld bepalen of het probleem alleen optreedt bij bepaalde clients (hostname), in bepaalde netwerkbibliotheken (client_interface_name), wanneer de laatste batch die door een SPID werd sys.dm_exec_sessionslast_request_start_time ingediend, hoe lang een aanvraag werd uitgevoerdstart_time, sys.dm_exec_requestsenzovoort.

Veelvoorkomende blokkeringsscenario's

In de onderstaande tabel worden veelvoorkomende symptomen toegewezen aan hun waarschijnlijke oorzaken.

De wait_typekolommen , open_transaction_counten status kolommen verwijzen naar informatie die wordt geretourneerd door sys.dm_exec_request, andere kolommen kunnen worden geretourneerd door sys.dm_exec_sessions. De kolom 'Lost?' geeft aan of de blokkering zelfstandig wordt omgezet of dat de sessie via de KILL opdracht moet worden gedood. Zie KILL (Transact-SQL) voor meer informatie.

Scenario Wait_type Open_Tran Status Opgelost? Andere symptomen
1 NIET NULL >= 0 kan worden uitgevoerd Ja, wanneer de query is voltooid. In sys.dm_exec_sessions, reads, cpu_timeen /of memory_usage kolommen neemt in de loop van de tijd toe. De duur van de query is hoog wanneer deze is voltooid.
2 NULL >0 slapend Nee, maar SPID kan worden gedood. Er kan een aandachtssignaal worden weergegeven in de uitgebreide gebeurtenissessie voor deze SPID, wat aangeeft dat er een time-out voor de query is opgetreden of geannuleerd.
3 NULL >= 0 kan worden uitgevoerd Nummer Wordt pas omgezet als de client alle rijen ophaalt of de verbinding sluit. SPID kan worden gedood, maar het kan tot 30 seconden duren. Als open_transaction_count = 0 en de SPID vergrendelingen bevat terwijl het niveau van transactieisolatie standaard is (READ COMMIT), is dit waarschijnlijk de oorzaak.
4 Varieert >= 0 kan worden uitgevoerd Nummer Wordt pas omgezet als de client query's annuleert of verbindingen sluit. SPID's kunnen worden gedood, maar het kan tot 30 seconden duren. De hostname kolom sys.dm_exec_sessions voor de SPID aan het hoofd van een blokkeringsketen is hetzelfde als een van de SPID die wordt geblokkeerd.
5 NULL >0 terugdraaien Ja. Er kan een aandachtssignaal worden weergegeven in de sessie Uitgebreide gebeurtenissen voor deze SPID, waarmee wordt aangegeven dat er een time-out of annulering van een query is opgetreden, of dat er gewoon een terugdraaiinstructie is uitgegeven.
6 NULL >0 slapend Uiteindelijk. Wanneer Windows NT bepaalt dat de sessie niet meer actief is, wordt de verbinding verbroken. De last_request_start_time waarde in sys.dm_exec_sessions is veel eerder dan de huidige tijd.

Gedetailleerde blokkeringsscenario's

Scenario 1: Blokkeren die wordt veroorzaakt door een normaal uitgevoerde query met een lange uitvoeringstijd

In dit scenario heeft een actief uitgevoerde query vergrendelingen verkregen en worden de vergrendelingen niet vrijgegeven (dit wordt beïnvloed door het niveau van transactieisolatie). Andere sessies wachten dus op de vergrendelingen totdat ze worden vrijgegeven.

Oplossing:

De oplossing voor dit blokkeringsprobleem is het zoeken naar manieren om de query te optimaliseren. Deze klasse van het blokkerende probleem kan een prestatieprobleem zijn en vereist dat u het als zodanig behandelt. Zie Problemen met trage query's in SQL Server oplossen voor informatie over het oplossen van problemen met een specifieke trage query. Zie Monitor and Tune for Performance (Controleren en afstemmen op prestaties) voor meer informatie.

Rapporten die zijn ingebouwd in SSMS vanuit de Query Store (geïntroduceerd in SQL Server 2016) zijn ook een zeer aanbevolen en waardevol hulpprogramma voor het identificeren van de meest kostbare query's en suboptimale uitvoeringsplannen.

Als u een langlopende query hebt die andere gebruikers blokkeert en deze niet kan worden geoptimaliseerd, kunt u overwegen om deze te verplaatsen van een OLTP-omgeving naar een toegewezen rapportagesysteem. U kunt alwayson-beschikbaarheidsgroepen ook gebruiken om een alleen-lezen replica van de database te synchroniseren.

Notitie

Blokkeren tijdens het uitvoeren van query's kan worden veroorzaakt door escalatie van query's, een scenario waarin rij- of paginavergrendelingen zijn geëscaleerd naar tabelvergrendelingen. Microsoft SQL Server bepaalt dynamisch wanneer de escalatie van vergrendelingen moet worden uitgevoerd. De eenvoudigste en veiligste manier om escalatie van vergrendelingen te voorkomen, is door transacties kort te houden en de vergrendelingsvoetafdruk van dure query's te verminderen, zodat de drempelwaarden voor vergrendelings escalatie niet worden overschreden. Zie Blokkerende problemen oplossen die worden veroorzaakt door escalatie van vergrendelingen voor meer informatie over het detecteren en voorkomen van overmatige escalatie van vergrendelingen.

Scenario 2: Blokkeren veroorzaakt door een slaap-SPID met een niet-doorgevoerde transactie

Dit type blokkeren kan vaak worden geïdentificeerd door een SPID die slaapt of wacht op een opdracht met een transactie genest niveau (@@TRANCOUNTvansys.dm_exec_requests), open_transaction_count groter dan nul. Deze situatie kan optreden als de toepassing een time-out van een query ondervindt of een annulering uitvoert zonder het vereiste aantal ROLLBACK- en/of COMMIT-instructies uit te geven. Wanneer een SPID een time-out voor een query of een annulering ontvangt, wordt de huidige query en batch beëindigd, maar wordt de transactie niet automatisch teruggedraaid of doorgevoerd. De toepassing is hiervoor verantwoordelijk, omdat SQL Server niet kan aannemen dat een volledige transactie moet worden teruggedraaid omdat één query wordt geannuleerd. De time-out of annulering van de query wordt weergegeven als een ATTENTION-signaalgebeurtenis voor de SPID in de uitgebreide gebeurtenissessie.

Als u een niet-doorgevoerde expliciete transactie wilt demonstreren, voert u de volgende query uit:

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

Voer vervolgens deze query uit in hetzelfde venster:

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

De uitvoer van de tweede query geeft aan dat het aantal transacties één is. Alle vergrendelingen die in de transactie zijn verkregen, worden nog bewaard totdat de transactie is doorgevoerd of teruggedraaid. Als toepassingen expliciet transacties openen en doorvoeren, kan een communicatie of een andere fout de sessie en de transactie ervan in een open status laten staan.

Gebruik het script dat eerder in dit artikel is gebaseerd op sys.dm_tran_active_transactions het identificeren van momenteel niet-doorgevoerde transacties in het exemplaar.

Oplossingen:

  • Deze klasse van blokkerende problemen kan ook een prestatieprobleem zijn en vereist dat u het als zodanig behandelt. Als de uitvoeringstijd van de query kan worden verminderd, kan de time-out van de query of annuleren niet optreden. Het is belangrijk dat de toepassing de time-out- of annuleringsscenario's kan verwerken als deze zich voordoen, maar u kunt ook profiteren van het onderzoeken van de prestaties van de query.

  • Toepassingen moeten de nestniveaus van transacties correct beheren, of ze kunnen een blokkerend probleem veroorzaken na de annulering van de query op deze manier. Overweeg de volgende:

    • Voer IF @@TRANCOUNT > 0 ROLLBACK TRAN in de fouthandler van de clienttoepassing een fout uit, zelfs als de clienttoepassing niet denkt dat een transactie open is. Controleren op openstaande transacties is vereist omdat een opgeslagen procedure die tijdens de batch wordt aangeroepen, een transactie kan zijn gestart zonder dat de clienttoepassing dit weet. Bepaalde voorwaarden, zoals het annuleren van de query, verhinderen dat de procedure wordt uitgevoerd na de huidige instructie, dus zelfs als de procedure logica heeft om de transactie te controleren IF @@ERROR <> 0 en af te breken, wordt deze terugdraaicode in dergelijke gevallen niet uitgevoerd.

    • Als groepsgewijze verbindingen worden gebruikt in een toepassing waarmee de verbinding wordt geopend en enkele query's worden uitgevoerd voordat de verbinding met de groep wordt vrijgegeven, zoals een webtoepassing, kan het tijdelijk uitschakelen van groepsgewijze verbindingen helpen het probleem te verhelpen totdat de clienttoepassing wordt gewijzigd om de fouten op de juiste manier af te handelen. Door groepsgewijze verbindingen uit te schakelen, veroorzaakt het vrijgeven van de verbinding een fysieke verbinding van de SQL Server-verbinding, waardoor de server geopende transacties terugstuurt.

    • Gebruik SET XACT_ABORT ON deze functie voor de verbinding of in opgeslagen procedures die transacties starten en die niet worden opgeschoond na een fout. In het geval van een runtimefout wordt met deze instelling alle geopende transacties afgebroken en wordt het besturingselement naar de client geretourneerd. Raadpleeg SET XACT_ABORT (Transact-SQL) voor meer informatie.

Notitie

De verbinding wordt pas opnieuw ingesteld als deze opnieuw wordt gebruikt vanuit de verbindingsgroep, dus het is mogelijk dat een gebruiker een transactie kan openen en vervolgens de verbinding met de verbindingsgroep kan vrijgeven, maar deze mogelijk niet meer opnieuw wordt gebruikt gedurende enkele seconden, gedurende welke tijd de transactie open zou blijven. Als de verbinding niet opnieuw wordt gebruikt, wordt de transactie afgebroken wanneer er een time-out optreedt voor de verbinding en wordt verwijderd uit de verbindingsgroep. Het is dus optimaal voor de clienttoepassing om transacties in hun fouthandler af te breken of te gebruiken SET XACT_ABORT ON om deze potentiële vertraging te voorkomen.

Let op

Hierna SET XACT_ABORT ONworden T-SQL-instructies gevolgd door een instructie die ervoor zorgt dat een fout niet wordt uitgevoerd. Dit kan van invloed zijn op de beoogde stroom van bestaande code.

Scenario 3: Blokkeren veroorzaakt door een SPID waarvan de bijbehorende clienttoepassing niet alle resultaatrijen tot voltooiing heeft opgehaald

Nadat een query naar de server is verzonden, moeten alle toepassingen onmiddellijk alle resultaatrijen volledig ophalen. Als een toepassing niet alle resultaatrijen ophaalt, kunnen vergrendelingen op de tabellen achterblijven, waardoor andere gebruikers worden geblokkeerd. Als u een toepassing gebruikt die transparant SQL-instructies naar de server verzendt, moet de toepassing alle resultaatrijen ophalen. Als dit niet zo is (en als dit niet kan worden geconfigureerd), kunt u het blokkeringsprobleem mogelijk niet oplossen. U kunt het probleem voorkomen door toepassingen die slecht gedrag vertonen, te beperken tot een rapportage- of beslissingsondersteuningsdatabase, gescheiden van de belangrijkste OLTP-database.

Oplossing:

De toepassing moet opnieuw worden geschreven om alle rijen van het resultaat op te halen tot voltooiing. Hiermee wordt het gebruik van OFFSET en FETCH niet uitgesloten in de ORDER BY-component van een query om paging aan de serverzijde uit te voeren.

Scenario 4: Blokkeren veroorzaakt door een impasse van een gedistribueerde client/server

In tegenstelling tot een conventionele impasse kan een gedistribueerde impasse niet worden gedetecteerd met behulp van rdbms-vergrendelingsbeheer. Dit komt doordat slechts één van de resources die betrokken zijn bij de impasse een SQL Server-vergrendeling is. De andere kant van de impasse bevindt zich op het niveau van de clienttoepassing, waarvoor SQL Server geen controle heeft. In de volgende twee secties ziet u voorbeelden van hoe dit kan gebeuren en mogelijke manieren waarop de toepassing dit kan voorkomen.

Voorbeeld A: Client/server gedistribueerde impasse met één clientthread

Als de client meerdere geopende verbindingen en één thread met uitvoering heeft, kan de volgende gedistribueerde impasse optreden. Opmerking: de term dbproc die hier wordt gebruikt, verwijst naar de clientverbindingsstructuur.

 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)

In het bovenstaande geval heeft één clienttoepassingsthread twee geopende verbindingen. Er wordt asynchroon een SQL-bewerking verzonden op dbproc1. Dit betekent dat het niet wacht op de aanroep om terug te keren voordat u doorgaat. De toepassing verzendt vervolgens een andere SQL-bewerking op dbproc2 en wacht op de resultaten om de geretourneerde gegevens te verwerken. Wanneer de gegevens terugkomen (afhankelijk van welke dbproc het eerst reageert, wordt ervan uitgegaan dat dit dbproc1 is), worden alle gegevens verwerkt die zijn geretourneerd op die dbproc. Het haalt resultaten van dbproc1 op totdat SPID1 wordt geblokkeerd op een vergrendeling die door SPID2 wordt vastgehouden (omdat de twee query's asynchroon op de server worden uitgevoerd). Op dit moment wacht dbproc1 voor onbepaalde tijd op meer gegevens. SPID2 wordt niet geblokkeerd op een vergrendeling, maar probeert gegevens naar de client te verzenden, dbproc2. Dbproc2 wordt echter effectief geblokkeerd op dbproc1 op de toepassingslaag, omdat de enige thread van uitvoering voor de toepassing wordt gebruikt door dbproc1. Dit resulteert in een impasse die SQL Server niet kan detecteren of oplossen omdat slechts een van de betrokken resources een SQL Server-resource is.

Voorbeeld B: Client/server gedistribueerde impasse met een thread per verbinding

Zelfs als er een afzonderlijke thread bestaat voor elke verbinding op de client, kan er nog steeds een variatie van deze gedistribueerde impasse optreden, zoals wordt weergegeven in het volgende.

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)

Dit geval is vergelijkbaar met voorbeeld A, behalve dbproc2 en SPID2, voert een SELECT instructie uit met de bedoeling om rij-op-een-tijd te verwerken en elke rij door een buffer te geven aan dbproc1 voor een INSERT, UPDATEof DELETE instructie in dezelfde tabel. Uiteindelijk wordt SPID1 (uitvoeren van de INSERT, UPDATEof DELETE) geblokkeerd op een slot dat door SPID2 wordt vastgehouden (het uitvoeren van de SELECT). SPID2 schrijft een resultaatrij naar de client dbproc2. Dbproc2 probeert vervolgens de rij in een buffer door te geven aan dbproc1, maar zoekt dat dbproc1 bezet is (het wordt geblokkeerd wachten op SPID1 om de huidige INSERTte voltooien, die wordt geblokkeerd op SPID2). Op dit moment wordt dbproc2 geblokkeerd op de toepassingslaag door dbproc1 waarvan SPID (SPID1) op databaseniveau door SPID2 wordt geblokkeerd. Nogmaals, dit resulteert in een impasse die SQL Server niet kan detecteren of oplossen omdat slechts één van de betrokken resources een SQL Server-resource is.

Zowel voorbeelden A als B zijn fundamentele problemen waar toepassingsontwikkelaars rekening mee moeten houden. Ze moeten toepassingen coderen om deze gevallen op de juiste manier te verwerken.

Oplossing:

Wanneer er een time-out voor een query is opgegeven en de gedistribueerde impasse optreedt, wordt deze verbroken wanneer er een time-out optreedt. Raadpleeg de documentatie van uw verbindingsprovider voor meer informatie over het gebruik van een time-out voor query's.

Scenario 5: Blokkeren veroorzaakt door een sessie met de status Terugdraaien

Een query voor het wijzigen van gegevens die wordt gedood of geannuleerd buiten een door de gebruiker gedefinieerde transactie, wordt teruggedraaid. Dit kan ook optreden als een neveneffect van de verbinding met de clientnetwerksessie of wanneer een aanvraag wordt geselecteerd als het impasseslachtoffer. Dit kan vaak worden geïdentificeerd door de uitvoer van sys.dm_exec_requests, die kan duiden op het terugdraaien command, en de kolom kan de percent_complete voortgang weergeven.

Een query voor het wijzigen van gegevens die wordt gedood of geannuleerd buiten een door de gebruiker gedefinieerde transactie, wordt teruggedraaid. Dit kan ook optreden als een neveneffect van het opnieuw opstarten van de clientcomputer en het verbreken van de netwerksessie. Op dezelfde manier wordt een query die is geselecteerd als het impasseslachtoffer teruggedraaid. Een gegevenswijzigingsquery kan vaak niet sneller worden teruggedraaid dan de wijzigingen in eerste instantie zijn toegepast. Als een, INSERTof UPDATE instructie DELETEbijvoorbeeld een uur lang werd uitgevoerd, kan het minstens een uur duren voordat de instructie is teruggedraaid. Dit is verwacht gedrag, omdat de aangebrachte wijzigingen moeten worden teruggedraaid, of transactionele en fysieke integriteit in de database zou worden aangetast. Omdat dit moet gebeuren, markeert SQL Server de SPID in een gouden of terugdraaistatus (wat betekent dat het niet kan worden gedood of geselecteerd als een impasseslachtoffer). Dit kan vaak worden geïdentificeerd door de uitvoer van sp_who, die kan duiden op de opdracht TERUGDRAAIEN. In de status kolom sys.dm_exec_sessions wordt de status TERUGDRAAIEN aangegeven.

Notitie

Langdurige terugdraaiacties komen zelden voor wanneer de functie Versneld databaseherstel is ingeschakeld. Deze functie is geïntroduceerd in SQL Server 2019.

Oplossing:

U moet wachten totdat de sessie klaar is met het terugdraaien van de aangebrachte wijzigingen.

Als het exemplaar in het midden van deze bewerking wordt afgesloten, bevindt de database zich in de herstelmodus bij het opnieuw opstarten en is deze niet toegankelijk totdat alle geopende transacties worden verwerkt. Opstartherstel duurt in wezen dezelfde tijd per transactie als het uitvoeren van herstel en de database is gedurende deze periode niet toegankelijk. Het afdwingen van de server om een SPID in een terugdraaistatus op te lossen, is dus vaak contraproductief. In SQL Server 2019 waarvoor versneld databaseherstel is ingeschakeld, mag dit niet gebeuren.

Om deze situatie te voorkomen, voert u geen grote batchschrijfbewerkingen of indexbewerkingen of onderhoudsbewerkingen uit tijdens drukke uren op OLTP-systemen. Voer, indien mogelijk, dergelijke bewerkingen uit tijdens perioden met een lage activiteit.

Scenario 6: Blokkeren veroorzaakt door een zwevende transactie

Dit is een veelvoorkomend probleemscenario en overlapt gedeeltelijk met Scenario 2. Als de clienttoepassing stopt, wordt het clientwerkstation opnieuw opgestart of is er een batch-aborting-fout opgetreden, dan kan dit allemaal een transactie open laten. Deze situatie kan zich voordoen als de toepassing de transactie niet terugdraait in de CATCH toepassing of blokkeert of FINALLY als deze deze situatie niet op een andere manier afhandelt.

In dit scenario, terwijl de uitvoering van een SQL-batch is geannuleerd, laat de toepassing de SQL-transactie open. Vanuit het perspectief van het SQL Server-exemplaar lijkt de client nog steeds aanwezig te zijn en blijven eventuele verkregen vergrendelingen behouden.

Als u een zwevende transactie wilt demonstreren, voert u de volgende query uit, waarmee een batch-aborting-fout wordt gesimuleerd door gegevens in te voegen in een niet-bestaande tabel:

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)

Voer vervolgens deze query uit in hetzelfde venster:

SELECT @@TRANCOUNT;

De uitvoer van de tweede query geeft aan dat het aantal transacties één is. Alle vergrendelingen die in de transactie zijn verkregen, worden nog bewaard totdat de transactie wordt doorgevoerd of teruggedraaid. Omdat de batch al is afgebroken door de query, kan de toepassing die deze uitvoert, andere query's blijven uitvoeren op dezelfde sessie zonder de transactie op te schonen die nog steeds is geopend. De vergrendeling wordt bewaard totdat de sessie wordt beëindigd of het SQL Server-exemplaar opnieuw wordt opgestart.

Oplossingen:

  • De beste manier om deze voorwaarde te voorkomen, is door de verwerking van toepassingsfouten/uitzonderingen te verbeteren, met name voor onverwachte beëindigingen. Zorg ervoor dat u een Try-Catch-Finally blok in de toepassingscode gebruikt en de transactie terugdraait in het geval van een uitzondering.
  • Overweeg het gebruik SET XACT_ABORT ON voor de sessie of in opgeslagen procedures die transacties starten en die niet worden opgeschoond na een fout. In het geval van een runtimefout die de batch afbreekt, wordt met deze instelling automatisch alle geopende transacties teruggedraaid en wordt het besturingselement teruggezet naar de client. Raadpleeg SET XACT_ABORT (Transact-SQL) voor meer informatie.
  • Als u een zwevende verbinding wilt oplossen van een clienttoepassing die de verbinding heeft verbroken zonder de resources op de juiste manier op te schonen, kunt u de SPID beëindigen met behulp van de KILL opdracht. Zie KILL (Transact-SQL) voor naslaginformatie.

De KILL opdracht gebruikt de SPID-waarde als invoer. Als u bijvoorbeeld SPID 9 wilt doden, voert u de volgende opdracht uit:

KILL 99

Notitie

Het kan tot 30 seconden duren voordat de KILL opdracht is voltooid vanwege het interval tussen controles voor de KILL opdracht.

Zie ook