Delen via

Blokkerende problemen begrijpen en oplossen

  • Artikel

van toepassing op:Azure SQL DatabaseSQL-database in Fabric

In het artikel wordt beschreven hoe u blokkeringen in Azure SQL Database en Fabric SQL Database kunt blokkeren en hoe u blokkeringen kunt oplossen.

Objectief

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 in de gebruikelijke zin geen afzonderlijke procescontext is. 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 Azure SQL Database 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.

Zie Impasses analyseren en voorkomen in Azure SQL Database en Fabric SQL Databasevoor meer informatie over het oplossen van impasses.

Notitie

Deze inhoud is gericht op Azure SQL Database. Azure SQL Database is gebaseerd op de nieuwste stabiele versie van de Microsoft SQL Server-database-engine, dus veel van de inhoud is vergelijkbaar, hoewel de opties en hulpprogramma's voor probleemoplossing kunnen verschillen. Zie Problemen met blokkeren in SQL Server begrijpen en oplossenvoor meer informatie over blokkeren in SQL Server. Fabric SQL Database deelt veel functies met Azure SQL Database. Zie SQL-database bewaken in Microsoft Fabricvoor meer informatie over prestatiebewaking.

Begrijp het blokkeren

Blokkering is een onvermijdelijk en opzettelijk kenmerk van elk relationeel databasebeheersysteem (RDBMS) met vergrendelingsgebaseerde concurrentie. Blokkeren in een database in Azure SQL Database vindt plaats 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 door te gaan met verwerken. Dit gedrag is normaal en kan gedurende de loop van een dag vaak gebeuren zonder merkbaar effect op systeemprestaties.

Voor elke nieuwe database in Azure SQL Database is standaard de vastgelegde momentopname database-instelling (RCSI) ingeschakeld. Blokkades tussen sessies die gegevens lezen en schrijven worden geminimaliseerd onder RCSI, waarbij versiebeheer van rijen wordt gebruikt om de gelijktijdigheid te verhogen. Blokkeringen en impasses kunnen echter nog steeds voorkomen in databases in Azure SQL Database, omdat:

  • Query's die gegevens wijzigen, kunnen elkaar blokkeren.
  • Queries kunnen worden uitgevoerd onder isolatieniveaus die de blokkades verhogen. Isolatieniveaus kunnen worden opgegeven in verbindingsreeksen van toepassingen, query-hintsof SET-instructies in Transact-SQL.
  • RCSI kan worden uitgeschakeld, waardoor de database gebruikmaakt van gedeelde (S)-vergrendelingen om SELECT-instructies te beveiligen die worden uitgevoerd op het niveau van de Read Committed-isolatie. Dit kan blokkeringen en deadlocks vergroten.

isolatieniveau voor momentopnamen is ook standaard ingeschakeld voor nieuwe databases in Azure SQL Database. Isolatie van momentopnamen is een extra isolatieniveau op basis van rijen dat consistentie op transactieniveau biedt voor gegevens en waarbij rijversies worden gebruikt om rijen te selecteren die moeten worden bijgewerkt. Als u isolatie van momentopnamen wilt gebruiken, moeten query's of verbindingen het niveau van de transactieisolatie expliciet instellen op SNAPSHOT. Dit kan alleen worden gedaan wanneer isolatie van momentopnamen is ingeschakeld voor de database.

U kunt bepalen of RCSI en/of momentopname-isolatie zijn ingeschakeld met Transact-SQL. Maak verbinding met uw database in Azure SQL Database en voer de volgende query uit:

SELECT name, is_read_committed_snapshot_on, snapshot_isolation_state_desc
FROM sys.databases
WHERE name = DB_NAME();
GO

Als RCSI is ingeschakeld, retourneert de kolom is_read_committed_snapshot_on de waarde 1. Als isolatie van momentopnamen is ingeschakeld, retourneert de kolom snapshot_isolation_state_desc de waarde ON.

De duur en transactiecontext van een query bepalen hoe lang de vergrendelingen worden bewaard en wat het effect ervan is op andere query's. SELECT-instructies die onder RCSI worden uitgevoerd, verkrijgen geen gedeelde (S)-vergrendelingen op de gegevens die worden gelezenen daardoor blokkeren ze geen transacties die gegevens wijzigen. 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 expliciete transactie, wordt het type vergrendelingen en de duur waarvoor de vergrendelingen worden bewaard, 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 vergrendelt een set resources voor een langere periode voordat deze worden vrijgegeven. Dit type blokkeren lost zichzelf na verloop van tijd op, maar kan leiden tot prestatievermindering.

  • Een SPID houdt vergrendelingen vast op een set resources en laat ze nooit los. 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 veranderlijk zijn omdat verschillende SPID's in de loop van de tijd blokkades veroorzaken 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.

Geoptimaliseerde vergrendeling

Geoptimaliseerde vergrendeling is een nieuwe database-enginefunctie die het vergrendelingsgeheugen drastisch vermindert en het aantal vergrendelingen dat gelijktijdig vereist is voor schrijfbewerkingen. Geoptimaliseerde vergrendeling maakt gebruik van twee primaire onderdelen: Transaction ID (TID) vergrendeling (ook gebruikt in andere functies voor rijversiebeheer) en vergrendeling na kwalificatie (LAQ). Er is geen extra configuratie vereist.

Dit artikel is momenteel van toepassing op het gedrag van de database-engine zonder geoptimaliseerde vergrendeling.

Zie Geoptimaliseerde vergrendelingvoor meer informatie en voor meer informatie over waar geoptimaliseerde vergrendeling beschikbaar is.

Toepassingen en blokkeringen

Er kan een tendens zijn om zich te richten op problemen met het afstemmen van de serverzijde en het platform bij het ondervinden van een blokkeringsprobleem. Het is echter mogelijk dat alleen aandacht besteden aan de database niet tot een oplossing leidt, en tijd en energie beter kunnen worden besteed aan het onderzoeken van de clienttoepassing en de query's die deze indient. Ongeacht het zichtbaarheidsniveau van de toepassing met betrekking tot de databaseaanroepen die worden uitgevoerd, vereist een blokkerend probleem vaak 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 het ontwikkelen van toepassingen voor Azure SQL Database 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 dit kan de toepassing acceptabele prestaties bij lage aantallen gebruikers hebben, maar de prestaties kunnen aanzienlijk afnemen naarmate het aantal gebruikers omhoog wordt geschaald.

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

Notitie

Zie Connectiviteitsproblemen en andere fouten oplossen en tijdelijke foutafhandelingvoor meer richtlijnen voor het ontwikkelen van toepassingen.

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 de redenen hebben begrepen, 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 in de database in Azure SQL Database bewaken. Er zijn in feite twee 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 methode is het gebruik van XEvents om vast te leggen wat er wordt uitgevoerd.

Informatie verzamelen van voertuigregistratiebureaus

Verwijzen naar DMV's om problemen met blokkeren op te lossen heeft als doel de SPID (sessie-id) aan het hoofd van de blokkadeketen en de SQL-instructie te identificeren. Zoek naar geblokkeerde SPIDs van slachtoffers. 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.

Vergeet niet om elk van deze scripts uit te voeren in de doeldatabase in Azure SQL Database.

  • De opdrachten sp_who en sp_who2 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 in de kern van andere query's.

  • Als u al een bepaalde sessie hebt geïdentificeerd, kunt u DBCC INPUTBUFFER(<session_id>) gebruiken om de laatste verklaring te vinden die door een sessie is verzonden. Vergelijkbare resultaten kunnen worden teruggegeven met de sys.dm_exec_input_buffer functie voor dynamisch beheer (DMF), in een resultatenset die gemakkelijker te doorzoeken en te filteren is, waarbij 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 kolom blocking_session_id in sys.dm_exec_requests. Wanneer blocking_session_id = 0, wordt een sessie niet geblokkeerd. Hoewel sys.dm_exec_requests alleen aanvragen weergeeft die momenteel worden uitgevoerd, wordt een verbinding (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.

  • 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 het text veld van sys.dm_exec_sql_text NULL is, wordt de query momenteel niet uitgevoerd. In dat geval bevat het event_info veld van sys.dm_exec_input_buffer de 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 van sessie-ID's die per sessie-ID geblokkeerd zijn.

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 blokkerende keten voor meerdere sessies 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];
  • Verwijzing sys.dm_os_waiting_tasks die zich in de thread-/taaklaag van SQL bevindt. Hiermee wordt informatie geretourneerd over het SQL-wachttype dat de aanvraag momenteel ondervindt. Net als sys.dm_exec_requestsworden alleen actieve aanvragen geretourneerd door sys.dm_os_waiting_tasks.

Notitie

Zie de DMV-sys.dm_db_wait_statsvoor meer informatie over wachttypen, waaronder geaggregeerde wachtstatistieken in de loop van de tijd. Deze DMV retourneert alleen statistische wachtstatistieken voor de huidige database.

  • Gebruik de sys.dm_tran_locks DMV voor meer gedetailleerde informatie over welke vergrendelingen door query's zijn geplaatst. Deze DMV kan grote hoeveelheden gegevens in een productiedatabase opleveren en is handig voor het vaststellen van de vergrendelingen die momenteel actief zijn.

Vanwege de INNER JOIN op sys.dm_os_waiting_tasksbeperkt 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 biedt het opslaan van de queryresultaten in de loop van de tijd gegevenspunten waarmee u blokkeringen gedurende een opgegeven tijdsinterval kunt controleren om persistente blokkeringen of trends te identificeren.

Informatie verzamelen van uitgebreide gebeurtenissen

Naast de vorige informatie is het vaak nodig om een tracering van de activiteiten op de server vast te leggen om een blokkerend probleem op Azure SQL Database 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 vergrendelingen nog steeds 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 Server Profiler is echter verouderde traceertechnologie die niet wordt ondersteund voor Azure SQL Database. uitgebreide gebeurtenissen is de nieuwere traceringstechnologie die meer veelzijdigheid en minder effect op het waargenomen systeem mogelijk maakt en de interface is geïntegreerd in SQL Server Management Studio (SSMS).

Raadpleeg het document waarin wordt uitgelegd hoe u de wizard Uitgebreide gebeurtenissen gebruikt in SSMS. Voor Azure SQL-databases biedt SSMS echter een submap 'Uitgebreide Gebeurtenissen' onder elke database in Objectverkenner. Gebruik de wizard Uitgebreide gebeurtenissen om deze nuttige gebeurtenissen vast te leggen:

  • Categoriefouten:

    • Aandacht
    • Fout_gemeld
    • Uitvoeringswaarschuwing

  • Categoriewaarschuwingen:

    • Ontbrekende_join_voorwaarde

  • Categorie-uitvoering:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • Categorie-deadlock_monitor

    • database_xml_deadlock_report

  • Categorie sessie

    • Bestaande_verbinding
    • Inloggen
    • Afmelden

Notitie

Zie Impasses analyseren en voorkomen in Azure SQL Database en Fabric SQL Databasevoor gedetailleerde informatie over impasses.

Veelvoorkomende blokkeringsscenario's identificeren en oplossen

Door de vorige 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 over de blokkerende SPID's vast te leggen en 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 om de hoofden van de blokkeringsketens te bepalen met behulp van blocking_these en session_id. Hiermee wordt duidelijk aangegeven welke aanvragen worden geblokkeerd en welke aan het blokkeren zijn. Bekijk nader de sessies die geblokkeerd worden en blokkeren. Is er een gemeenschappelijke of basis voor de blokkering in de keten? Ze delen waarschijnlijk een gemeenschappelijke tabel, en een of meer van de sessies die bij een blokkeringsketen betrokken zijn, voeren een schrijfbewerking uit.

  • Bekijk de uitvoer van de DMVs sys.dm_exec_requests en sys.dm_exec_sessions voor informatie over de SPID's aan het begin van de blokkeringsketen. Zoek naar de volgende velden:

    • 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.
    Slapen De SPID wordt momenteel niet uitgevoerd. Dit geeft meestal aan dat de SPID wacht op een opdracht van de toepassing.
    Hardlopen De SPID draait momenteel op een taakplanner.
    Uitvoerbaar De SPID bevindt zich in de uitvoerwachtrij van een planner en wacht op de planningstijd.
    Opgeschort De SPID wacht op een bron, zoals een vergrendeling of een grendel.

    • sys.dm_exec_sessions.open_transaction_count
      In dit veld wordt het aantal geopende transacties in deze sessie aangegeven. Als deze waarde groter is dan 0, bevindt de SPID zich binnen een geopende transactie en kan vergrendelingen vasthouden die zijn verkregen door een willekeurige instructie binnen de transactie.

    • sys.dm_exec_requests.open_transaction_count
      Op dezelfde manier wordt in dit veld het aantal geopende transacties in deze aanvraag aangegeven. Als deze waarde groter is dan 0, bevindt de SPID zich binnen een geopende transactie en kan deze mogelijk sloten vasthouden die zijn verkregen door een statement binnen de transactie.

    • sys.dm_exec_requests.wait_type, wait_timeen last_wait_type
      Als de 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_statsvoor 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 kolom wait_time die kleiner is dan de wait_time waarde uit een vorige query van sys.dm_exec_requests, geeft dit aan dat de eerdere vergrendeling is verkregen en vrijgegeven en nu wacht op een nieuwe vergrendeling (uitgaande van niet-nul-wait_time). Dit kan worden gecontroleerd door de wait_resource te vergelijken met de uitvoer van sys.dm_exec_requests, waarin de resource wordt weergegeven waarop het verzoek wacht.

    • sys.dm_exec_requests.wait_resource Dit veld geeft de resource aan waarop een geblokkeerde aanvraag wacht. De volgende tabel bevat algemene wait_resource indelingen en betekenis:

    Hulpbron Formaat Voorbeeld Uitleg
    Tafel DatabaseID:ObjectID:IndexID TABBLAD: 5:261575970:1 In dit geval is database-id 5 de voorbeelddatabase pubs en object-id 261575970 de titeltabel is en 1 de geclusterde index.
    Bladzijde DatabaseID:FileID:PageID PAGINA: 5:1:104 In dit geval is database-id 5 is pubs, bestands-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 functie voor dynamisch beheer sys.dm_db_page_info, waarbij u de DatabaseID, FileId, PageId van de wait_resourcedoorgeeft.
    Sleutelcode DatabaseID:Hobt_id (Hash-waarde voor indexsleutel) SLEUTEL: 5:72057594044284928 (3300a4f361aa) In dit geval is database-id 5 pubsen komt Hobt_ID 72057594044284928 overeen met index_id 2 voor object_id 261575970 (titelstabel). Gebruik de sys.partitions catalogusweergave om de hobt_id aan een bepaalde index_id en object_idte koppelen. Er is geen manier om de hash van de indexsleutel terug te zetten 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 openstaande transacties te retourneren, gekoppeld aan andere DMV's, inclusief sys.dm_tran_session_transactions. Overweeg de huidige status van een transactie, transaction_begin_timeen 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
, azure_dtc_state = CASE tat.dtc_state 
                    WHEN 1 THEN 'ACTIVE'
                    WHEN 2 THEN 'PREPARED'
                    WHEN 3 THEN 'COMMITTED'
                    WHEN 4 THEN 'ABORTED'
                    WHEN 5 THEN 'RECOVERED' END
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count  
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;

    • Andere kolommen

      De resterende kolommen in sys.dm_exec_sessions en sys.dm_exec_request kunnen ook inzicht geven in de oorzaak 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 (hostnaam), in bepaalde netwerkbibliotheken (net_library), wanneer de laatste batch die door een SPID is verzonden, in sys.dm_exec_sessionsis last_request_start_time, hoe lang een aanvraag werd uitgevoerd met behulp van start_time in sys.dm_exec_requests, enzovoort.

Veelvoorkomende blokkeringsscenario's

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

De kolommen Waittype, Open_Tranen Status verwijzen naar informatie die door sys.dm_exec_requestwordt geretourneerd. Andere kolommen kunnen teruggegeven worden door sys.dm_exec_sessions. De kolom 'Oplossen?' geeft aan of de blokkering vanzelf wordt opgelost of dat de sessie moet worden beëindigd via de opdracht KILL. Zie KILLvoor meer informatie.

Scenario Wachttype Open_Tran Status Is dit opgelost? Andere symptomen
1 NIET NULL >= 0 uitvoerbaar Ja, wanneer de query is voltooid. In sys.dm_exec_sessionsnemen reads, cpu_timeen/of memory_usage kolommen in de loop van de tijd toe. De duur van de query is hoog wanneer deze is voltooid.
2 NUL >0 aan het slapen 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 dat er een annulering is opgetreden.
3 NUL >= 0 uitvoerbaar Nee. Wordt pas opgelost nadat de client alle rijen heeft opgehaald 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 uitvoerbaar Nee. Wordt niet opgelost totdat de client query's annuleert of verbindingen sluit. SPID's kunnen worden gedood, maar het kan tot 30 seconden duren. De hostname kolom in sys.dm_exec_sessions voor de SPID aan het hoofd van een blokkeringsketen is hetzelfde als die van een van de SPIDs die worden geblokkeerd.
5 NUL >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 NUL >0 slapend Uiteindelijk. Wanneer Windows bepaalt dat de sessie niet meer actief is, wordt de Azure SQL Database-verbinding verbroken. De last_request_start_time-waarde in sys.dm_exec_sessions is veel eerder dan het huidige tijdstip.

Gedetailleerde blokkeringsscenario's

  1. Blokkering veroorzaakt door een normaal draaiende query met een lange uitvoeringstijd

    oplossing: de oplossing voor dit type blokkerende probleem is om te zoeken naar manieren om de query te optimaliseren. Eigenlijk kan deze klasse van blokkerende problemen gewoon een prestatieprobleem zijn en moet u dit als zodanig uitvoeren. Zie Problemen met trage query's oplossen op SQL Server-voor informatie over het oplossen van problemen met een specifieke trage query. Zie Bewaken en afstemmen op prestatiesvoor meer informatie.

    Rapporten uit de Query Store- in SSMS zijn ook een zeer aanbevolen en waardevol hulpprogramma voor het identificeren van de meest kostbare query's, suboptimale uitvoeringsplannen. Bekijk ook Query Performance Insight-.

    Als de query alleen SELECT-bewerkingen uitvoert, kunt u overwegen de instructie uit te voeren onder isolatie van momentopnamen als deze is ingeschakeld in uw database, met name als RCSI is uitgeschakeld. Net als wanneer RCSI is ingeschakeld, zijn voor query's die gegevens lezen geen gedeelde (S) vergrendelingen nodig onder isolatieniveau voor momentopnamen. Daarnaast biedt isolatie van momentopnamen consistentie op transactieniveau voor alle instructies in een expliciete transactie met meerdere instructies. Isolatie van momentopnamen kan al zijn ingeschakeld in uw database-. Isolatie van momentopnamen kan ook worden gebruikt met query's die wijzigingen uitvoeren, maar u moet updateconflictenafhandelen.

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

  2. Blokkering veroorzaakt door een slapende SPID met een niet-doorgevoerde transactie

    Dit type blokkering kan vaak worden geïdentificeerd door een SPID die slaapt of wacht op een opdracht, maar waarvan het transactie-nestniveau (@@TRANCOUNT, open_transaction_count van sys.dm_exec_requests) groter is dan nul. Dit kan gebeuren als de toepassing een time-out voor een query ondervindt of een annulering uitvoert zonder ook 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 Azure SQL Database niet kan aannemen dat een volledige transactie moet worden teruggedraaid omdat één query wordt geannuleerd. De time-out van de query of annuleren 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;
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 geneste transactieniveau één is. Alle vergrendelingen die in de transactie zijn verkregen, worden nog steeds 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 eerder in dit artikel op basis van sys.dm_tran_active_transactions om momenteel niet-doorgevoerde transacties in het exemplaar te identificeren.

    resoluties:

    • Daarnaast kan dit type blokkerend probleem ook een prestatieprobleem zijn en vereist dat u het als zodanig aanpakt. Als de uitvoeringstijd van de query kan worden verminderd, treedt er geen time-out op voor de query of wordt de query geannuleerd. Het is belangrijk dat de toepassing de time-out- of annuleringsscenario's kan afhandelen 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. Overwegen:

      • Voer in de foutenafhandelaar van de clienttoepassing IF @@TRANCOUNT > 0 ROLLBACK TRAN uit na elke fout, zelfs als de clienttoepassing niet denkt dat er een transactie openstaat. Controleren op openstaande transacties is vereist, omdat een opgeslagen procedure die tijdens de batch wordt aangeroepen, een transactie kan hebben 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 IF @@ERROR <> 0 te controleren en de transactie af te breken, wordt deze terugdraaicode niet uitgevoerd in dergelijke gevallen.
      • Als groepsgewijze verbindingen worden gebruikt in een toepassing waarmee de verbinding wordt geopend en een paar query's worden uitgevoerd voordat de verbinding met de pool wordt vrijgegeven, zoals een webtoepassing, kan het tijdelijk uitschakelen van groepsgewijze verbindingen helpen om het probleem te verhelpen totdat de clienttoepassing wordt gewijzigd om de fouten op de juiste manier af te handelen. Als u verbindingspooling uitschakelt, zorgt het vrijgeven van de verbinding voor een fysieke verbreking van de verbinding met de Azure SQL Database, waardoor de server alle openstaande transacties terugdraait.
      • Gebruik SET XACT_ABORT ON voor de verbinding of in opgeslagen procedures die transacties starten en die niet worden opgeschoond na een fout. In het geval van een run-time fout worden met deze instelling alle geopende transacties afgebroken en wordt de controle aan de client teruggegeven. Voor meer informatie, raadpleeg SET XACT_ABORT.

    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 kan mogelijk gedurende enkele seconden niet opnieuw worden gebruikt, 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 dat de clienttoepassing transacties in hun fouthandler afbreekt of SET XACT_ABORT ON gebruikt om deze potentiële vertraging te voorkomen.

    Voorzichtigheid

    Na SET XACT_ABORT ONworden T-SQL-instructies gevolgd door een instructie die een fout veroorzaakt, niet uitgevoerd. Dit kan van invloed zijn op de beoogde stroom van bestaande code.

  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 ophalen tot voltooiing. Als een toepassing niet alle resultaatrijen ophaalt, kunnen vergrendelingen worden achtergelaten in de tabellen, 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. Om het probleem te voorkomen, kunt u slecht gedragen toepassingen beperken tot een rapportage- of beslissingsondersteuningsdatabase, gescheiden van de primaire OLTP-database.

    De impact van dit scenario wordt verminderd wanneer 'read committed snapshot' is ingeschakeld op de database, wat de standaardconfiguratie is in Azure SQL Database. Lees meer in het onderdeel Begrijp blokkerende van dit artikel.

    Notitie

    Zie richtlijnen voor het opnieuw proberen van logica voor toepassingen die verbinding maken met Azure SQL Database.

    oplossing: de toepassing moet opnieuw worden geschreven om alle rijen van het resultaat tot voltooiing op te halen. 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.

  4. Blokkering veroorzaakt door een sessie met een terugdraaistatus

    Een query voor het wijzigen van gegevens die KILLed is of wordt geannuleerd buiten een door de gebruiker gedefinieerde transactie, wordt teruggedraaid. Dit kan ook optreden als een neveneffect van de verbinding van de clientnetwerksessie, of wanneer een verzoek wordt geselecteerd als het deadlock-slachtoffer. Dit kan vaak worden geïdentificeerd door de uitvoer van sys.dm_exec_requestste observeren. Dit kan duiden op de opdracht TERUGDRAAIEN en de kolom percent_complete kan de voortgang weergeven.

    Dankzij versneld databaseherstel geïntroduceerd in 2019, moeten langdurige terugdraaiacties zeldzaam zijn.

    resolutie: wacht totdat de SPID klaar is met het terugdraaien van de aangebrachte wijzigingen.

    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.

  5. Blokkeren veroorzaakt door een verweesde verbinding

    Als de clienttoepassing fouten trapt of het clientwerkstation opnieuw wordt opgestart, wordt de netwerksessie naar de server mogelijk niet onmiddellijk geannuleerd onder bepaalde omstandigheden. Vanuit het perspectief van Azure SQL Database lijkt de client nog steeds aanwezig te zijn en kunnen eventuele verkregen vergrendelingen nog steeds worden bewaard. Zie Problemen met zwevende verbindingen oplossen in SQL Servervoor meer informatie.

    Oplossing: als de clienttoepassing de verbinding heeft verbroken zonder de resources op te schonen, kunt u de SPID beëindigen met behulp van de opdracht KILL. Met de opdracht KILL wordt de SPID-waarde als invoer gebruikt. Als u bijvoorbeeld SPID 99 wilt doden, voert u de volgende opdracht uit:

    KILL 99

Verwante inhoud