Problemen met query's oplossen die nooit lijken te eindigen op SQL Server

Artikel
07/30/2024

In dit artikel worden de stappen voor probleemoplossing beschreven voor het probleem waarbij u een query hebt die nooit is voltooid, of het voltooien ervan vele uren of dagen kan duren.

Wat is een nooit eindigende query?

Dit document is gericht op query's die blijven uitvoeren of compileren, dat wil gezegd, hun CPU blijft toenemen. Het is niet van toepassing op query's die worden geblokkeerd of wachten op een resource die nooit wordt vrijgegeven (de CPU blijft constant of verandert zeer weinig).

Belangrijk

Als er nog een query overblijft om de uitvoering te voltooien, wordt deze uiteindelijk voltooid. Het kan slechts een paar seconden duren of het kan enkele dagen duren.

De term die nooit eindigt, wordt gebruikt om de perceptie te beschrijven van een query die niet wordt voltooid wanneer de query uiteindelijk wordt voltooid.

Een nooit-eindigende query identificeren

Voer de volgende stappen uit om te bepalen of een query continu wordt uitgevoerd of vastgelopen op een knelpunt:

Voer de volgende query uit.

DECLARE @cntr int = 0

WHILE (@cntr < 3)
BEGIN
    SELECT TOP 10 s.session_id,
                    r.status,
                    r.wait_time,
                    r.wait_type,
                    r.wait_resource,
                    r.cpu_time,
                    r.logical_reads,
                    r.reads,
                    r.writes,
                    r.total_elapsed_time / (1000 * 60) 'Elaps M',
                    SUBSTRING(st.TEXT, (r.statement_start_offset / 2) + 1,
                    ((CASE r.statement_end_offset
                        WHEN -1 THEN DATALENGTH(st.TEXT)
                        ELSE r.statement_end_offset
                    END - r.statement_start_offset) / 2) + 1) AS statement_text,
                    COALESCE(QUOTENAME(DB_NAME(st.dbid)) + N'.' + QUOTENAME(OBJECT_SCHEMA_NAME(st.objectid, st.dbid)) 
                    + N'.' + QUOTENAME(OBJECT_NAME(st.objectid, st.dbid)), '') AS command_text,
                    r.command,
                    s.login_name,
                    s.host_name,
                    s.program_name,
                    s.last_request_end_time,
                    s.login_time,
                    r.open_transaction_count,
                    atrn.name as transaction_name,
                    atrn.transaction_id,
                    atrn.transaction_state
        FROM sys.dm_exec_sessions AS s
        JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id 
                CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
        LEFT JOIN (sys.dm_tran_session_transactions AS stran 
             JOIN sys.dm_tran_active_transactions AS atrn
                ON stran.transaction_id = atrn.transaction_id)
        ON stran.session_id =s.session_id
        WHERE r.session_id != @@SPID
        ORDER BY r.cpu_time DESC

    SET @cntr = @cntr + 1
WAITFOR DELAY '00:00:05'
END

Controleer de voorbeelduitvoer.
- De stappen voor probleemoplossing in dit artikel zijn specifiek van toepassing wanneer u een uitvoer ziet die vergelijkbaar is met de volgende, waarbij de CPU evenredig toeneemt met de verstreken tijd, zonder aanzienlijke wachttijden. Het is belangrijk te weten dat wijzigingen logical_reads in dit geval niet relevant zijn, omdat sommige CPU-gebonden T-SQL-aanvragen helemaal geen logische leesbewerkingen uitvoeren (bijvoorbeeld berekeningen of een WHILE lus uitvoeren).
  
  session_id status cpu_time logical_reads wait_time wait_type
  
  56 actief 7038 101000 0 NULL
  
  56 uitvoerbaar 12040 301000 0 NULL
  
  56 actief 17020 523000 0 NULL
- Dit artikel is niet van toepassing als u een wachtscenario ziet dat vergelijkbaar is met het volgende scenario waarin de CPU niet erg verandert of verandert, en de sessie wacht op een resource.
  
  session_id status cpu_time logical_reads wait_time wait_type
  
  56 onderbroken 0 3 8312 LCK_M_U
  
  56 onderbroken 0 3 13318 LCK_M_U
  
  56 onderbroken 0 5 18331 LCK_M_U
Zie Wachten of knelpunten diagnosticeren voor meer informatie.

session_id	status	cpu_time	logical_reads	wait_type
56	actief	7038	101000	NULL
56	uitvoerbaar	12040	301000	NULL
56	actief	17020	523000	NULL

session_id	status	logical_reads	wait_time	wait_type
56	onderbroken	3	8312	LCK_M_U
56	onderbroken	3	13318	LCK_M_U
56	onderbroken	5	18331	LCK_M_U

Lange compilatietijd

In zeldzame gevallen merkt u misschien dat de CPU continu toeneemt in de loop van de tijd, maar dat wordt niet aangestuurd door de uitvoering van query's. In plaats daarvan kan het worden aangestuurd door een te lange compilatie (het parseren en compileren van een query). Controleer in die gevallen de transaction_name uitvoerkolom en zoek naar een waarde van sqlsource_transform. Deze transactienaam geeft een compilatie aan.

Diagnostische gegevens verzamelen

Voer de volgende stappen uit om diagnostische gegevens te verzamelen met behulp van SQL Server Management Studio (SSMS):

Leg de xml van het geschatte uitvoeringsplan voor query's vast.
Bekijk het queryplan om te zien of er duidelijke indicaties zijn van waar de traagheid vandaan kan komen. Enkele voorbeelden hiervan zijn:
- Tabel- of indexscans (bekijk geschatte rijen).
- Geneste lussen die worden aangestuurd door een enorme buitenste tabelgegevensset.
- Geneste lussen met een grote vertakking aan de binnenkant van de lus.
- Tafelspools.
- Functies in de SELECT lijst die lang duren om elke rij te verwerken.
Als de query op elk gewenst moment snel wordt uitgevoerd, kunt u de 'snelle' uitvoeringen vastleggen om het werkelijke XML-uitvoeringsplan te vergelijken.

De lichtgewicht infrastructuur voor queryprofilering is geïntroduceerd in deze versies van SQL Server. Hiermee kunt u werkelijke statistieken vastleggen tijdens de uitvoering van een trage query. Met deze functie voor probleemoplossing kunt u queryoperators in een queryplan tijdens runtime onderzoeken en begrijpen waar de meeste tijd wordt besteed aan een query.

Volg deze stappen om de trage stappen in de query te identificeren met behulp van lightweight-queryuitvoeringsstatistieken profileringsinfrastructuur v1:

Voer de volgende opdrachten uit om XEvent query_thread_profile in te schakelen:

CREATE EVENT SESSION [NodePerfStats] ON SERVER
ADD EVENT sqlserver.query_thread_profile(
  ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
    sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
    sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
    sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
  EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
  MAX_DISPATCH_LATENCY=30 SECONDS,
  MAX_EVENT_SIZE=0 KB,
  MEMORY_PARTITION_MODE=NONE,
  TRACK_CAUSALITY=OFF,
  STARTUP_STATE=OFF);

ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = START

Start de betrokken nooit eindigende query vanuit de toepassing.

Voer de volgende opdracht meerdere keren per minuut of zo uit om de uitvoeringsstatistieken van de queryplanoperators te controleren:

SELECT CONVERT (varchar(30), getdate(), 126) as runtime,
            qp.session_id,
            convert(nvarchar(48), qp.physical_operator_name) as physical_operator_name,
            qp.row_count,
            qp.estimate_row_count,
            qp.node_id,
            req.cpu_time,
            req.total_elapsed_time,
            substring
            (REPLACE
            (REPLACE
                (SUBSTRING
                (SQLText.text
                , (req.statement_start_offset/2) + 1
                , (
                    (CASE statement_END_offset
                        WHEN -1
                        THEN DATALENGTH(SQLText.text)  
                        ELSE req.statement_END_offset
                        END
                        - req.statement_start_offset)/2) + 1)
            , CHAR(10), ' '), CHAR(13), ' '), 1, 512)  AS active_statement_text
FROM sys.dm_exec_query_profiles qp 
RIGHT OUTER JOIN sys.dm_exec_requests req
    ON qp.session_id = req.session_id
LEFT OUTER JOIN sys.dm_exec_sessions sess
    on req.session_id = sess.session_id
LEFT OUTER JOIN sys.dm_exec_connections conn on conn.session_id = req.session_id
OUTER APPLY sys.dm_exec_sql_text (ISNULL (req.sql_handle, conn.most_recent_sql_handle)) as SQLText
WHERE req.session_id <> @@SPID 
    AND sess.is_user_process = 1 
ORDER BY qp.session_id asc, row_count desc 
--this is to prevent massive grants
OPTION (max_grant_percent = 3, MAXDOP 1)

Leg drie of vier momentopnamen van één minuut uit elkaar vast om u voldoende gegevens te geven voor analyse. In het bijzonder kunt u de getallen voor elke operator in de loop van de row_count tijd vergelijken en zien welke een aanzienlijke toename van het aantal rijen (miljoen of meer) laat zien.
Leg in een nieuw queryvenster in SSMS een geschatte queryplanning vast voor de probleemquery door de volgende opdrachten uit te voeren:
```
SET SHOWPLAN_XML ON
GO
<problem query here>
GO
SET SHOWPLAN_XML OFF
```
Als u de knooppunt-id gebruikt met het hoogste aantal rijen dat is geïdentificeerd door de query in stap 3, zoekt u hetzelfde knooppunt in het geschatte queryplan. Deze stap helpt u te begrijpen welke operator in het plan de belangrijkste oorzaak is van de lange uitvoeringstijd.

Stop de XEvent door de volgende opdracht uit te voeren:

ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = STOP

U kunt de Lightweight-queryuitvoeringsstatistieken profileringsinfrastructuur v2 gebruiken om livequeryplannen vast te leggen met werkelijke waarden voor het aantal rijen. Met deze profileringsinfrastructuur kunt u queryoperators in een queryplan tijdens runtime onderzoeken en begrijpen waar de meeste tijd wordt besteed aan een query.

Volg deze stappen om de trage stappen in de query te identificeren:

Als u de lichtgewicht infrastructuur in deze versies van SQL Server wilt inschakelen, gebruikt u een van de volgende methoden:

Schakel traceringsvlag 7412 in door de volgende opdracht uit te voeren:
```
DBCC TRACEON (7412, -1)
```

U kunt de query_thread_profile XEvent ook inschakelen door de volgende opdrachten uit te voeren:

CREATE EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER
ADD EVENT sqlserver.query_plan_profile(
 ACTION(sqlos.scheduler_id,sqlserver.database_id,sqlserver.is_system,
   sqlserver.plan_handle,sqlserver.query_hash_signed,sqlserver.query_plan_hash_signed,
   sqlserver.server_instance_name,sqlserver.session_id,sqlserver.session_nt_username,
   sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(25600))
WITH (MAX_MEMORY=4096 KB,
 EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
 MAX_DISPATCH_LATENCY=30 SECONDS,
 MAX_EVENT_SIZE=0 KB,
 MEMORY_PARTITION_MODE=NONE,
 TRACK_CAUSALITY=OFF,
 STARTUP_STATE=OFF);

ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = START

Start de betrokken nooit eindigende query vanuit de toepassing.
Gebruik een opdracht die vergelijkbaar is met de volgende opdracht om de Session_id nooit eindigende query te identificeren die wordt uitgevoerd:
```
SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
```
Voer de volgende opdracht drie of vier keer één minuut uit om het queryplan en de werkelijke statistieken in het plan te onderzoeken. Zorg ervoor dat u het queryplan elke keer opslaat, zodat u deze kunt vergelijken en kunt bepalen welke queryoperator het grootste deel van de CPU-tijd verbruikt. U kunt het aantal rijen (werkelijk aantal rijen) voor elke operator in de loop van de tijd vergelijken en zien welke van de operators een aanzienlijke toename van het aantal rijen (miljoen of meer) laat zien. Vervang <session_id> door de gehele waarde die u in de vorige stap 3 hebt gevonden.
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```

Stop de XEvent als u er een hebt gestart of schakel de traceringsvlag uit:

ALTER EVENT SESSION [PerfStats_LWP_Plan_v2] ON SERVER STATE = STOP
-- or
DBCC TRACEOFF (7412, -1)

U kunt de lightweight-queryuitvoeringsstatistieken profileringsinfrastructuur v3 gebruiken om livequeryplannen vast te leggen met werkelijke waarden voor het aantal rijen. Met deze profileringsinfrastructuur kunt u queryoperators in een queryplan tijdens runtime onderzoeken en begrijpen waar de meeste tijd wordt besteed aan een query. Lichtgewicht profilering is standaard ingeschakeld in SQL Server 2019.

Volg deze stappen om de trage stappen in de query te identificeren:

Start de betrokken nooit eindigende query vanuit uw toepassing.
Gebruik een opdracht die vergelijkbaar is met de volgende opdracht om de Session_id nooit eindigende query te identificeren die wordt uitgevoerd:
```
SELECT t.text, session_id 
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text (req.sql_handle) as t
```
Voer de volgende opdracht drie of vier keer uit om het queryplan en de werkelijke statistieken in het plan te onderzoeken. Zorg ervoor dat u het queryplan elke keer opslaat, zodat u deze kunt vergelijken en kunt bepalen welke queryoperator het grootste deel van de CPU-tijd verbruikt. Vervang <session_id> door de gehele waarde die u in de vorige stap 3 hebt gevonden.
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```
Selecteer met name de XML-koppeling onder de kolom query_plan . Zodra het grafische queryplan in een nieuw venster wordt geopend, klikt u er met de rechtermuisknop op en selecteert u Uitvoeringsplan opslaan als.... Herhaal de stappen voor het vastleggen van drie of vier momentopnamen die één minuut uit elkaar liggen om u voldoende gegevens te geven voor analyse. U kunt het aantal rijen (het werkelijke aantal rijen) voor elke operator in de loop van de tijd vergelijken en zien welke van de operators een aanzienlijke toename van het aantal rijen (miljoen of meer) laat zien.
Notitie

Als u geen uitvoer van sys.dm_exec_query_statistics_xmlkrijgt, kunt u controleren of de databaseoptie LAST_QUERY_PLAN_STATS is uitgeschakeld door de volgende opdracht uit te voeren:
```
SELECT name, value, value_for_secondary, is_value_default 
FROM sys.database_scoped_configurations
WHERE name = 'LAST_QUERY_PLAN_STATS'
```
U kunt de laatste statistieken van het queryplan op databaseniveau inschakelen door deze uit te voeren ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON.

Methode voor het controleren van de verzamelde plannen

In deze sectie ziet u hoe u de verzamelde gegevens kunt bekijken. Hierbij worden de meerdere XML-queryplannen (met behulp van extensie *.sqlplan) gebruikt die zijn verzameld in SQL Server 2016 SP1 en latere builds en versies.

Volg deze stappen om uitvoeringsplannen te vergelijken:

Open een eerder opgeslagen queryuitvoeringsplanbestand (.sqlplan).
Klik met de rechtermuisknop in een leeg gebied van het uitvoeringsplan en selecteer Showplan vergelijken.
Kies het tweede queryplanbestand dat u wilt vergelijken.
Zoek naar dikke pijlen die duiden op een groot aantal rijen die tussen operators stromen. Selecteer vervolgens de operator vóór of na de pijl en vergelijk het aantal werkelijke rijen tussen twee plannen.
Vergelijk de tweede en derde planning om te zien of de grootste stroom rijen in dezelfde operatoren plaatsvindt.

Hier volgt een voorbeeld:

Oplossing

Zorg ervoor dat statistieken worden bijgewerkt voor de tabellen die in de query worden gebruikt.
Zoek naar een ontbrekende indexaanveling in het queryplan en pas deze toe.
Herschrijf de query met het doel om deze te vereenvoudigen:
- Gebruik meer selectieve WHERE predicaten om de verwerkte gegevens vooraf te verminderen.
- Breek het uit elkaar.
- Selecteer enkele onderdelen in tijdelijke tabellen en voeg ze later toe.
- Verwijder TOP, EXISTSen FAST (T-SQL) in de query's die gedurende zeer lange tijd worden uitgevoerd vanwege het doel van de optimizer-rij. U kunt ook de DISABLE_OPTIMIZER_ROWGOAL hint gebruiken. Zie Row Goals Gone Rogue voor meer informatie.
- Vermijd het gebruik van CTE's (Common Table Expressions) in gevallen waarin ze instructies combineren in één grote query.
Probeer queryhints te gebruiken om een beter plan te maken:
- HASH JOIN of MERGE JOIN hint
- FORCE ORDER tip
- FORCESEEK tip
- RECOMPILE
- GEBRUIKEN PLAN N'<xml_plan>' als u een snel queryplan hebt dat u kunt afdwingen
Gebruik Query Store (QDS) om een goed bekend plan af te dwingen als een dergelijk plan bestaat en of uw SQL Server-versie Query Store ondersteunt.

Wachttijden of knelpunten diagnosticeren

Deze sectie is hier opgenomen als referentie voor het geval uw probleem geen langlopende CPU-rijquery is. U kunt deze gebruiken om problemen met query's op te lossen die lang zijn vanwege wachttijden.

Als u een query wilt optimaliseren die wacht op knelpunten, identificeert u hoe lang de wachttijd is en waar het knelpunt zich bevindt (het wachttype). Zodra het wachttype is bevestigd, vermindert u de wachttijd of elimineert u de wachttijd volledig.

Als u de geschatte wachttijd wilt berekenen, trekt u de CPU-tijd (werktijd) af van de verstreken tijd van een query. Normaal gesproken is de CPU-tijd de werkelijke uitvoeringstijd en het resterende deel van de levensduur van de query wacht.

Voorbeelden van het berekenen van de geschatte wachttijd:

Verstreken tijd (ms)	CPU-tijd (ms)	Wachttijd (ms)
3200	3000	200
7080	1000	6080

Het knelpunt identificeren of wachten

Voer de volgende query uit om historische langwachtquery's te identificeren (bijvoorbeeld >20% van de totale verstreken tijd is wachttijd). Deze query maakt gebruik van prestatiestatistieken voor queryplannen in de cache sinds het begin van SQL Server.

SELECT t.text,
         qs.total_elapsed_time / qs.execution_count
         AS avg_elapsed_time,
         qs.total_worker_time / qs.execution_count
         AS avg_cpu_time,
         (qs.total_elapsed_time - qs.total_worker_time) / qs.execution_count
         AS avg_wait_time,
         qs.total_logical_reads / qs.execution_count
         AS avg_logical_reads,
         qs.total_logical_writes / qs.execution_count
         AS avg_writes,
         qs.total_elapsed_time
         AS cumulative_elapsed_time
FROM sys.dm_exec_query_stats qs
         CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE (qs.total_elapsed_time - qs.total_worker_time) / qs.total_elapsed_time
         > 0.2
ORDER BY qs.total_elapsed_time / qs.execution_count DESC

Voer de volgende query uit om query's met wachttijden langer dan 500 ms te identificeren:

SELECT r.session_id, r.wait_type, r.wait_time AS wait_time_ms
FROM sys.dm_exec_requests r 
   JOIN sys.dm_exec_sessions s ON r.session_id = s.session_id 
WHERE wait_time > 500
AND is_user_process = 1

Als u een queryplan kunt verzamelen, controleert u de WaitStats van de eigenschappen van het uitvoeringsplan in SSMS:
1. Voer de query uit met Het werkelijke uitvoeringsplan opnemen op.
2. Klik met de rechtermuisknop op de meest linkse operator op het tabblad Uitvoeringsplan
3. Selecteer Eigenschappen en vervolgens de eigenschap WaitStats .
4. Controleer de WaitTimeMs en WaitType.
Als u bekend bent met PSSDiag-/SQLdiag - of SQL LogScout LightPerf-/GeneralPerf-scenario's, kunt u een van deze scenario's gebruiken om prestatiestatistieken te verzamelen en wachtquery's op uw SQL Server-exemplaar te identificeren. U kunt de verzamelde gegevensbestanden importeren en de prestatiegegevens analyseren met SQL Nexus.

Verwijzingen om wachttijden te elimineren of te verminderen

De oorzaken en oplossingen voor elk wachttype variëren. Er is geen algemene methode om alle wachttypen op te lossen. Hier volgen artikelen voor het oplossen van veelvoorkomende problemen met wachttypen:

Zie de tabel in Typen wachttijden voor beschrijvingen van veel wachttypen en wat ze aangeven.

Delen via