Problemen met query's oplossen die nooit op SQL Server

Artikel
01/19/2024

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

Wat is een oneindige query?

Dit document is gericht op query's die nog steeds worden uitgevoerd of gecompileerd, dat wil gezegd dat hun CPU blijft toenemen. Het is niet van toepassing op query's die zijn geblokkeerd of wachten op een resource die nooit is vrijgegeven (de CPU blijft constant of verandert heel weinig).

Belangrijk

Als een query wordt overgelaten om de uitvoering te voltooien, wordt deze uiteindelijk voltooid. Dit kan slechts enkele seconden duren, of het kan enkele dagen duren.

De term never-ending wordt gebruikt om de perceptie te beschrijven dat een query niet wordt voltooid, terwijl de query uiteindelijk wordt voltooid.

Een oneindige query identificeren

Voer de volgende stappen uit om te bepalen of een query continu wordt uitgevoerd of vastloopt 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 om te weten dat wijzigingen in logical_reads in in dit geval niet relevant zijn, omdat sommige CPU-gebonden T-SQL-aanvragen mogelijk helemaal geen logische leesbewerkingen uitvoeren (bijvoorbeeld berekeningen of een WHILE lus uitvoeren).
  
  session_id Status cpu_time logical_reads wait_time wait_type
  
  56 Met 7038 101000 0 NULL
  
  56 uitvoerbaar 12040 301000 0 NULL
  
  56 Met 17020 523000 0 NULL
- Dit artikel is niet van toepassing als u een wachtscenario ziet dat vergelijkbaar is met het volgende scenario waarbij de CPU niet of weinig verandert en de sessie wacht op een resource.
  
  session_id Status cpu_time logical_reads wait_time wait_type
  
  56 Geschorst 0 3 8312 LCK_M_U
  
  56 Geschorst 0 3 13318 LCK_M_U
  
  56 Geschorst 0 5 18331 LCK_M_U
Zie Wachttijden of knelpunten vaststellen voor meer informatie.

session_id	Status	cpu_time	logical_reads	wait_type
56	Met	7038	101000	NULL
56	uitvoerbaar	12040	301000	NULL
56	Met	17020	523000	NULL

session_id	Status	logical_reads	wait_time	wait_type
56	Geschorst	3	8312	LCK_M_U
56	Geschorst	3	13318	LCK_M_U
56	Geschorst	5	18331	LCK_M_U

Lange compilatietijd

In zeldzame gevallen ziet u misschien dat de CPU in de loop van de tijd voortdurend toeneemt, maar dat dit niet wordt veroorzaakt door de uitvoering van query's. In plaats daarvan kan dit worden aangestuurd door een te lange compilatie (het parseren en compileren van een query). Controleer in dat geval 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 geschatte XML van het queryuitvoeringsplan vast .
Bekijk het queryplan om te zien of er duidelijke aanwijzingen zijn van waar de traagheid vandaan kan komen. Typische voorbeelden zijn:
- Tabel- of indexscans (bekijk geschatte rijen).
- Geneste lussen die worden aangedreven door een enorme gegevensset voor buitenste tabellen.
- Geneste lussen met een grote tak in de binnenkant van de lus.
- Tabelspools.
- Functies in de SELECT lijst die lang duren voordat elke rij is verwerkt.
Als de query op elk gewenst moment snel wordt uitgevoerd, kunt u de 'snelle' uitvoeringen vastleggen Werkelijke XML-uitvoeringsplan om te vergelijken.

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

Voer de volgende stappen uit om de trage stappen in de query te identificeren met behulp van lichtgewicht profileringsinfrastructuur voor queryuitvoeringsstatistieken 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 uit om de uitvoeringsstatistieken voor 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 vast die één minuut uit elkaar staan om u voldoende gegevens te geven voor analyse. U kunt met name de getallen voor elke operator in de row_count loop van de 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 geschat queryplan vast voor de probleemquery door de volgende opdrachten uit te voeren:
```
SET SHOWPLAN_XML ON
GO
<problem query here>
GO
SET SHOWPLAN_XML OFF
```
Gebruik de knooppunt-id met het hoogste aantal rijen dat is geïdentificeerd door de query in stap 3 en zoek hetzelfde knooppunt in het geschatte queryplan. Deze stap helpt u te begrijpen welke operator in het plan de hoofdoorzaak is van de lange uitvoeringstijd.

Stop XEvent door de volgende opdracht uit te voeren:

ALTER EVENT SESSION [NodePerfStats] ON SERVER STATE = STOP

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

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

Gebruik een van de volgende methoden om de lichtgewicht infrastructuur in te schakelen voor deze versies van SQL Server:

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

U kunt ook XEvent query_thread_profile 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 van uw oneindige 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, één minuut uit elkaar om het queryplan en de werkelijke statistieken in het plan te bekijken. Zorg ervoor dat u het queryplan elke keer opslaat, zodat u ze kunt vergelijken en kunt bepalen welke queryoperator de meeste CPU-tijd verbruikt. U kunt met name het aantal rijen (het werkelijke aantal rijen) voor elke operator in de loop van de tijd vergelijken en zien welke van de operatoren een aanzienlijke toename van het aantal rijen (miljoen of meer) laat zien. Vervang door <session_id> de waarde voor geheel getal die u in de vorige stap 3 hebt gevonden.
```
SELECT * FROM sys.dm_exec_query_statistics_xml (<session_id>)
```

Stop het 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 profileringsinfrastructuur voor lichtgewicht queryuitvoeringsstatistieken v3 gebruiken om live queryplannen vast te leggen met werkelijke waarden voor het aantal rijen. Met deze profileringsinfrastructuur kunt u queryoperators in een queryplan tijdens uitvoering onderzoeken en begrijpen waar de meeste tijd in een query wordt besteed. Lichtgewicht profilering is standaard ingeschakeld op 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 van uw oneindige 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 bekijken. Zorg ervoor dat u het queryplan elke keer opslaat, zodat u ze kunt vergelijken en kunt bepalen welke queryoperator de meeste CPU-tijd verbruikt. Vervang door <session_id> de waarde voor geheel getal 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 met een afstand van één minuut om u voldoende gegevens te geven voor analyse. U kunt met name het aantal rijen (het werkelijke aantal rijen) voor elke operator in de loop van de tijd vergelijken en zien welke operatoren een aanzienlijke toename in rijaantal (miljoen of meer) laten zien.
Opmerking

Als u geen uitvoer krijgt van sys.dm_exec_query_statistics_xml, 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 statistieken van het laatste queryplan op databaseniveau inschakelen door uit te voeren ALTER DATABASE SCOPED CONFIGURATION SET LAST_QUERY_PLAN_STATS = ON.

Methode voor het controleren van de verzamelde plannen

In deze sectie wordt uitgelegd hoe u de verzamelde gegevens kunt controleren. Er worden meerdere XML-queryplannen (met 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 bestand met een queryuitvoeringsplan (.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 een groot aantal rijen tussen operatoren aangeven. Selecteer vervolgens de operator vóór of na de pijl en vergelijk het aantal werkelijke rijen tussen twee plannen.
Vergelijk het tweede en derde plan om te zien of de grootste stroom rijen plaatsvindt in dezelfde operatoren.

Hier volgt een voorbeeld:

Oplossing

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

Wachttijden of knelpunten vaststellen

Deze sectie is hier opgenomen als een referentie voor het geval uw probleem geen langlopende CPU-aansturende query is. U kunt het 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 wacht het resterende deel van de levensduur van de query.

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 langverwachte query'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 te bepalen welke query's momenteel worden uitgevoerd met wachttijden die langer zijn dan 500 ms:

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 Werkelijke uitvoeringsplan opnemen op.
2. Klik met de rechtermuisknop op de operator die het meest links is 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 wachtende query'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 voorkomen of te verminderen

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

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

Share via