Delen via

Problemen met blokkerende Azure SQL Database begrijpen en oplossen

  • Artikel

Van toepassing op: Azure SQL Database

Doelstelling

In het artikel wordt beschreven hoe u blokkeringen in Azure SQL-databases kunt blokkeren 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 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 voor 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 Sql Server-blokkeringsproblemen begrijpen en oplossen voor meer informatie over blokkeren in SQL Server.

Informatie over blokkeren

Blokkering is een onvermijdbaar en zo ontworpen kenmerk van elk relationeel databasebeheersysteem (RDBMS) met gelijktijdigheid op basis van vergrendeling. 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 van de vergrendeling wordt vrijgegeven, is de tweede verbinding vervolgens gratis om een eigen vergrendeling op de resource te verkrijgen en door te gaan met verwerken. Dit is normaal gedrag en kan vaak gebeuren gedurende de loop van een dag zonder merkbaar effect op systeemprestaties.

Voor elke nieuwe database in Azure SQL Database is de standaardinstelling voor het lezen vastgelegde momentopname (RCSI) ingeschakeld. Blokkeren tussen sessies die gegevens lezen en sessies die gegevens schrijven, wordt geminimaliseerd onder RCSI, waarbij gebruik wordt gemaakt van rijversiebeheer om de gelijktijdigheid te verhogen. Blokkeringen en impasses kunnen echter nog steeds optreden in databases in Azure SQL Database, omdat:

  • Query's die gegevens wijzigen, kunnen elkaar blokkeren.
  • Query's kunnen worden uitgevoerd onder isolatieniveaus die de blokkering verhogen. Isolatieniveaus kunnen worden opgegeven in verbindingsreeksen van toepassingen, queryhints of SET-instructies in Transact-SQL.
  • RCSI kan worden uitgeschakeld, waardoor de database gedeelde (S)-vergrendelingen gebruikt om SELECT-instructies te beveiligen die worden uitgevoerd op het niveau van de vastgelegde isolatie voor lezen. Dit kan leiden tot blokkeringen en impasses.

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 hun transactieisolatieniveau 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 is_read_committed_snapshot_on kolom de waarde 1. Als isolatie van momentopnamen is ingeschakeld, retourneert de snapshot_isolation_state_desc kolom de waarde AAN.

De duur en transactiecontext van een query bepalen hoe lang de vergrendelingen worden bewaard en daarmee hun effect op andere query's. SELECT-instructies worden uitgevoerd onder RCSI verkrijgen geen gedeelde (S) vergrendelingen voor de gegevens die worden gelezen en blokkeren daarom 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 bepaald door het type query, het niveau van transactieisolatie en of vergrendelingshints in de query worden gebruikt. 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 houdt vergrendelingen op een set resources vast 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.

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: TID-vergrendeling (Transaction ID) (ook gebruikt in andere functies voor rijversiebeheer) en vergrendelen na kwalificatie (LAQ). Er is geen aanvullende configuratie vereist.

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

Zie Geoptimaliseerde vergrendeling voor meer informatie en voor meer informatie over waar geoptimaliseerde vergrendeling beschikbaar 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 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 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 Azure SQL Database vele duizenden gelijktijdige gebruikers op één server ondersteunen, met weinig blokkering.

Notitie

Zie Connectiviteitsproblemen en andere fouten oplossen met Azure SQL Database en Azure SQL Managed Instance en tijdelijke foutafhandeling voor 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 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 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 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 ketting lopen 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 deze in 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 blocking_session_id kolom in sys.dm_exec_requests. 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.

  • 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 sys.dm_exec_sql_text NULL zijn, wordt de query momenteel niet uitgevoerd. In dat geval bevat het veld de event_info sys.dm_exec_input_buffer 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 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];
  • Referentie sys.dm_os_waiting_tasks die zich op de thread-/taaklaag van SQL bevindt. Hiermee wordt informatie geretourneerd over het SQL-wachttype dat 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. Deze DMV retourneert alleen statistische wachtstatistieken voor de huidige database.

  • Gebruik de sys.dm_tran_locks DMV voor gedetailleerdere informatie over welke vergrendelingen zijn geplaatst door query's. Deze DMV kan grote hoeveelheden gegevens in een productiedatabase retourneren 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.

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 in 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 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 Server Profiler wordt echter afgeschaft voor traceringstechnologie die niet wordt ondersteund voor Azure SQL Database. Uitgebreide gebeurtenissen is de nieuwere traceringstechnologie die meer veelzijdigheid en minder impact 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 Nieuwe sessie 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:

    • Opmerking
    • Error_reported
    • Execution_warning

  • Categoriewaarschuwingen:

    • Missing_join_predicate

  • Uitvoering van categorie:

    • Rpc_completed
    • Rpc_starting
    • Sql_batch_completed
    • Sql_batch_starting

  • Categorie-deadlock_monitor

    • database_xml_deadlock_report

  • Categoriesessie

    • Existing_connection
    • Aanmelden
    • Afmelden

Notitie

Zie Impasses analyseren en voorkomen in Azure SQL Database voor 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 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 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 wordt momenteel uitgevoerd op een planner.
    Kan worden uitgevoerd De SPID bevindt zich in de uitvoerbare wachtrij van een planner en wacht tot de tijd van de planner wordt opgehaald.
    Onderbroken De SPID wacht op een resource, zoals een vergrendeling of een vergrendeling.

    • 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 deze sloten bevatten 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 sloten bevatten die zijn verkregen door een willekeurige instructie binnen de transactie.

    • 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 Dit veld geeft de resource aan waarop een geblokkeerde aanvraag wacht. De volgende tabel bevat algemene wait_resource notaties en hun betekenis:

    Resource 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 titeltabel 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.
    Key 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
, 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 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 (hostnaam), in bepaalde netwerkbibliotheken (net_library), wanneer de laatste batch die door een SPID is last_request_start_time sys.dm_exec_sessionsingediend, in hoe lang een aanvraag werd uitgevoerd start_time sys.dm_exec_requests, enzovoort.

Veelvoorkomende blokkeringsscenario's

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

De kolommen Waittype, Open_Tran en Status 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 Waittype 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 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 NULL >= 0 kan worden uitgevoerd Nee 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 COMMMITTED), is dit waarschijnlijk de oorzaak.
4 Varieert >= 0 kan worden uitgevoerd Nee 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 de query is opgetreden, of dat er gewoon een terugdraaiinstructie is uitgegeven.
6 NULL >0 Slapen Uiteindelijk. Wanneer Windows NT 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 de huidige tijd.

Gedetailleerde blokkeringsscenario's

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

    Oplossing: De oplossing voor dit type blokkerende probleem is om te zoeken naar manieren om de query te optimaliseren. Deze klasse van blokkerende problemen kan gewoon een prestatieprobleem zijn en vereisen dat u het als zodanig wilt uitvoeren. 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 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 de sectie Intelligente prestaties van Azure Portal voor de Azure SQL-database, waaronder Query Performance Insight.

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

    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 alleen-lezen replica van de database.

  2. Blokkeren veroorzaakt door een slaapspd met een niet-doorgevoerde transactie

    Dit type blokkeren kan vaak worden geïdentificeerd door een SPID die slaapt of wacht op een opdracht, maar waarvan het niveau van transactie genest (@@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 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;
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 niveau van transactie genest éé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 dat eerder in dit artikel is gebaseerd op sys.dm_tran_active_transactions het identificeren van momenteel niet-doorgevoerde transacties in het exemplaar.

    Oplossingen:

    • Daarnaast kan deze klasse van blokkerende problemen ook een prestatieprobleem zijn en moet u dit als zodanig uitvoeren. 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. Denk aan het 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 is geopend. 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 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 een klein aantal query's worden uitgevoerd voordat de verbinding met de groep wordt vrijgegeven, zoals een webtoepassing, kan het tijdelijk uitschakelen van verbindingsgroepen helpen 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 verbinding met de Azure SQL Database-verbinding, wat resulteert in het terugdraaien van geopende transacties op de server.
      • 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 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 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 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 volledig ophalen. Als een toepassing niet alle resultaatrijen ophaalt, kunnen vergrendelingen in de tabellen worden achtergelaten, 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 het geval 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.

    De impact van dit scenario wordt verminderd wanneer de READ COMMITTED SNAPSHOT is ingeschakeld voor de database. Dit is de standaardconfiguratie in Azure SQL Database. Meer informatie vindt u in de sectie Blokkerende informatie in dit artikel.

    Notitie

    Zie de richtlijnen voor logica voor opnieuw proberen voor toepassingen die verbinding maken met Azure SQL 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.

  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 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 de opdracht TERUGDRAAIEN, en de kolom kan de percent_complete voortgang weergeven.

    Dankzij de functie Versneld databaseherstel die in 2019 is geïntroduceerd, moeten lange terugdraaiacties zeldzaam zijn.

    Oplossing: 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 zwevende 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 gehandhaafd. Zie Problemen met zwevende verbindingen in SQL Server oplossen voor meer informatie.

    Oplossing: Als de clienttoepassing 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. De KILL opdracht gebruikt de SPID-waarde als invoer. Als u bijvoorbeeld SPID 99 wilt doden, voert u de volgende opdracht uit:

    KILL 99

Zie ook

Volgende stappen