Problemen met traag uitgevoerde query's in SQL Server oplossen

  • Artikel

Originele productversie: SQL Server
Origineel KB-nummer: 243589

Inleiding

In dit artikel wordt beschreven hoe u een prestatieprobleem kunt afhandelen dat databasetoepassingen kunnen ondervinden bij het gebruik van SQL Server: trage prestaties van een specifieke query of groep query's. Met de volgende methodologie kunt u de oorzaak van het probleem met trage query's beperken en u naar een oplossing leiden.

Trage query's zoeken

Als u wilt vaststellen dat u problemen hebt met queryprestaties op uw SQL Server-exemplaar, begint u met het onderzoeken van query's op hun uitvoeringstijd (verstreken tijd). Controleer of de tijd een drempelwaarde overschrijdt die u hebt ingesteld (in milliseconden) op basis van een vastgestelde prestatiebasislijn. In een stresstestomgeving hebt u bijvoorbeeld een drempelwaarde ingesteld voor uw workload die niet langer mag zijn dan 300 ms en kunt u deze drempelwaarde gebruiken. Vervolgens kunt u alle query's identificeren die deze drempelwaarde overschrijden, waarbij u zich richt op elke afzonderlijke query en de vooraf ingestelde duur van de prestatiebasislijn. Uiteindelijk geven zakelijke gebruikers om de totale duur van databasequery's; daarom ligt de nadruk op de duur van de uitvoering. Andere metrische gegevens, zoals CPU-tijd en logische leesbewerkingen, worden verzameld om het onderzoek te beperken.

  • Controleer total_elapsed_time en cpu_time kolommen in sys.dm_exec_requests voor het uitvoeren van instructies. Voer de volgende query uit om de gegevens op te halen:

    SELECT 
    req.session_id
    , req.total_elapsed_time AS duration_ms
    , req.cpu_time AS cpu_time_ms
    , req.total_elapsed_time - req.cpu_time AS wait_time
    , req.logical_reads
    , SUBSTRING (REPLACE (REPLACE (SUBSTRING (ST.text, (req.statement_start_offset/2) + 1, 
       ((CASE statement_end_offset
           WHEN -1
           THEN DATALENGTH(ST.text)  
           ELSE req.statement_end_offset
         END - req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 
      1, 512)  AS statement_text  
FROM sys.dm_exec_requests AS req
    CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) AS ST
ORDER BY total_elapsed_time DESC;

  • Voor eerdere uitvoeringen van de query controleert u last_elapsed_time en last_worker_time kolommen in sys.dm_exec_query_stats. Voer de volgende query uit om de gegevens op te halen:

    SELECT t.text,
     (qs.total_elapsed_time/1000) / qs.execution_count AS avg_elapsed_time,
     (qs.total_worker_time/1000) / qs.execution_count AS avg_cpu_time,
     ((qs.total_elapsed_time/1000) / qs.execution_count ) - ((qs.total_worker_time/1000) / 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/1000) AS cumulative_elapsed_time_all_executions
FROM sys.dm_exec_query_stats qs
     CROSS apply sys.Dm_exec_sql_text (sql_handle) t
WHERE t.text like '<Your Query>%'
-- Replace <Your Query> with your query or the beginning part of your query. The special chars like '[','_','%','^' in the query should be escaped.
ORDER BY (qs.total_elapsed_time / qs.execution_count) DESC

    Opmerking

    Als avg_wait_time er een negatieve waarde wordt weergegeven, is het een parallelle query.

  • Als u de query op aanvraag kunt uitvoeren in SQL Server Management Studio (SSMS) of Azure Data Studio, voert u deze uit met SET STATISTICS TIMEON en SET STATISTICS IOON.

    SET STATISTICS TIME ON
SET STATISTICS IO ON
<YourQuery>
SET STATISTICS IO OFF
SET STATISTICS TIME OFF

    Vervolgens ziet u in Berichten de CPU-tijd, verstreken tijd en logische leesbewerkingen als volgt:

      Table 'tblTest'. Scan count 1, logical reads 3, physical reads 0, page server reads 0, read-ahead reads 0, page server read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob page server reads 0, lob read-ahead reads 0, lob page server read-ahead reads 0.

  SQL Server Execution Times:
    CPU time = 460 ms,  elapsed time = 470 ms.

  • Als u een queryplan kunt verzamelen, controleert u de gegevens uit de eigenschappen van het uitvoeringsplan.

    1. Voer de query uit met Werkelijke uitvoeringsplan opnemen op.

    2. Selecteer de meest linkse operator in Uitvoeringsplan.

    3. Vouw in Eigenschappende eigenschap QueryTimeStats uit.

    4. Schakel ElapsedTime en CpuTime in.

      Schermopname van het venster eigenschappen van SQL Server uitvoeringsplan met de eigenschap QueryTimeStats uitgevouwen.

Uitvoeren versus wachten: waarom zijn query's traag?

Als u query's vindt die uw vooraf gedefinieerde drempelwaarde overschrijden, controleert u waarom deze traag kunnen zijn. De oorzaak van prestatieproblemen kan worden gegroepeerd in twee categorieën: actief of wachtend:

  • WACHTEN: query's kunnen traag zijn omdat ze lang op een knelpunt wachten. Bekijk een gedetailleerde lijst met knelpunten in typen wachttijden.

  • UITVOEREN: query's kunnen traag zijn omdat ze lang worden uitgevoerd (uitgevoerd). Met andere woorden, deze query's maken actief gebruik van CPU-resources.

Een query kan enige tijd worden uitgevoerd en enige tijd in de levensduur (duur) wachten. Uw focus is echter om te bepalen welke categorie de dominante categorie is die bijdraagt aan de lange verstreken tijd. Daarom is de eerste taak om vast te stellen in welke categorie de query's vallen. Het is eenvoudig: als een query niet wordt uitgevoerd, wacht deze. Idealiter besteedt een query het grootste deel van de verstreken tijd in een actieve status en heel weinig tijd om te wachten op resources. In het beste geval wordt een query ook uitgevoerd binnen of onder een vooraf bepaalde basislijn. Vergelijk de verstreken tijd en CPU-tijd van de query om het probleemtype te bepalen.

Type 1: CPU-gebonden (runner)

Als de CPU-tijd bijna, gelijk is aan of hoger is dan de verstreken tijd, kunt u deze behandelen als een CPU-gebonden query. Als de verstreken tijd bijvoorbeeld 3000 milliseconden (ms) is en de CPU-tijd 2900 ms is, betekent dit dat de meeste verstreken tijd wordt besteed aan de CPU. Dan kunnen we zeggen dat het een CPU-gebonden query is.

Voorbeelden van uitgevoerde (CPU-gebonden) query's:

Verstreken tijd (ms) CPU-tijd (ms) Leesbewerkingen (logisch)
3200 3000 300000
1080 1000 20

Logische leesbewerkingen - het lezen van gegevens/indexpagina's in de cache - zijn meestal de drivers van het CPU-gebruik in SQL Server. Er kunnen scenario's zijn waarin CPU-gebruik afkomstig is van andere bronnen: een while-lus (in T-SQL of andere code zoals XProcs of SQL CRL-objecten). Het tweede voorbeeld in de tabel illustreert een dergelijk scenario, waarbij het merendeel van de CPU niet afkomstig is van leesbewerkingen.

Opmerking

Als de CPU-tijd langer is dan de duur, geeft dit aan dat er een parallelle query wordt uitgevoerd; meerdere threads gebruiken tegelijkertijd de CPU. Zie Parallelle query's - runner of ober voor meer informatie.

Type 2: Wachten op een knelpunt (ober)

Een query wacht op een knelpunt als de verstreken tijd aanzienlijk groter is dan de CPU-tijd. De verstreken tijd omvat de tijd die de query uitvoert op de CPU (CPU-tijd) en de tijd die moet worden gewacht tot een resource wordt vrijgegeven (wachttijd). Als de verstreken tijd bijvoorbeeld 2000 ms is en de CPU-tijd 300 ms is, is de wachttijd 1700 ms (2000 - 300 = 1700). Zie Typen wachttijden voor meer informatie.

Voorbeelden van wachtende query's:

Verstreken tijd (ms) CPU-tijd (ms) Leesbewerkingen (logisch)
2000 300 28000
10080 700 80000

Parallelle query's - runner of ober

Parallelle query's gebruiken mogelijk meer CPU-tijd dan de totale duur. Het doel van parallellisme is om meerdere threads toe te staan om delen van een query tegelijkertijd uit te voeren. In één seconde van de kloktijd kan een query acht seconden CPU-tijd gebruiken door acht parallelle threads uit te voeren. Daarom wordt het lastig om een CPU-gebonden of een wachtquery te bepalen op basis van het verstreken tijds- en CPU-tijdsverschil. In het algemeen geldt echter dat u de principes volgt die in de bovenstaande twee secties worden vermeld. De samenvatting is:

  • Als de verstreken tijd veel groter is dan de CPU-tijd, beschouwt u het als een ober.
  • Als de CPU-tijd veel groter is dan de verstreken tijd, beschouwt u het als een hardloper.

Voorbeelden van parallelle query's:

Verstreken tijd (ms) CPU-tijd (ms) Leesbewerkingen (logisch)
1200 8100 850000
3080 12300 1500000

Visuele weergave van de methodologie op hoog niveau

De schermopname toont een visuele weergave op hoog niveau van de methodologie voor het oplossen van problemen met trage query's.

Wachtende query's vaststellen en oplossen

Als u hebt vastgesteld dat uw interessante query's obers zijn, is de volgende stap het oplossen van knelpuntproblemen. Anders gaat u naar stap 4: Actieve query's vaststellen en oplossen.

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.

Actieve query's vaststellen en oplossen

Als de CPU-tijd (werkrol) zeer dicht bij de totale verstreken duur ligt, besteedt de query het grootste deel van de levensduur aan het uitvoeren. Wanneer de SQL Server engine een hoog CPU-gebruik aanstuurt, is het hoge CPU-gebruik meestal afkomstig van query's die een groot aantal logische leesbewerkingen aansturen (de meest voorkomende reden).

Voer de volgende instructie uit om de query's te identificeren die verantwoordelijk zijn voor de activiteit met hoog CPU-gebruik:

SELECT TOP 10 s.session_id,
           r.status,
           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
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
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time DESC

Als query's de CPU op dit moment niet aansturen, kunt u de volgende instructie uitvoeren om te zoeken naar historische CPU-gebonden query's:

SELECT TOP 10  qs.last_execution_time, st.text AS batch_text,
    SUBSTRING(st.TEXT, (qs.statement_start_offset / 2) + 1, ((CASE qs.statement_end_offset WHEN - 1 THEN DATALENGTH(st.TEXT) ELSE qs.statement_end_offset END - qs.statement_start_offset) / 2) + 1) AS statement_text,
    (qs.total_worker_time / 1000) / qs.execution_count AS avg_cpu_time_ms,
    (qs.total_elapsed_time / 1000) / qs.execution_count AS avg_elapsed_time_ms,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    (qs.total_worker_time / 1000) AS cumulative_cpu_time_all_executions_ms,
    (qs.total_elapsed_time / 1000) AS cumulative_elapsed_time_all_executions_ms
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) st
ORDER BY(qs.total_worker_time / qs.execution_count) DESC

Algemene methoden voor het oplossen van langlopende, CPU-gebonden query's