Behandeln von Problemen mit langsam ausgeführten Abfragen in SQL Server

  • Artikel

Ursprüngliche Produktversion: SQL Server
Ursprüngliche KB-Nummer: 243589

Einführung

In diesem Artikel wird beschrieben, wie Sie ein Leistungsproblem behandeln, das bei Datenbankanwendungen auftreten kann, wenn sie SQL Server verwenden: langsame Leistung einer bestimmten Abfrage oder einer Gruppe von Abfragen. Die folgende Methodik hilft Ihnen, die Ursache des Problems mit langsamen Abfragen einzugrenzen und Sie zur Lösung zu leiten.

Suchen nach langsamen Abfragen

Um festzustellen, dass sie Probleme mit der Abfrageleistung auf Ihrem SQL Server instance haben, untersuchen Sie zunächst Abfragen nach ihrer Ausführungszeit (verstrichene Zeit). Überprüfen Sie, ob die Zeit einen Schwellenwert überschreitet, den Sie basierend auf einer festgelegten Leistungsbaseline (in Millisekunden) festgelegt haben. In einer Belastungstestumgebung haben Sie z. B. möglicherweise einen Schwellenwert für Ihre Workload auf maximal 300 ms festgelegt, und Sie können diesen Schwellenwert verwenden. Anschließend können Sie alle Abfragen identifizieren, die diesen Schwellenwert überschreiten, wobei sie sich auf jede einzelne Abfrage und ihre vorab festgelegte Leistungsbaselinedauer konzentrieren. Letztendlich kümmern sich Geschäftskunden um die Gesamtdauer von Datenbankabfragen; Daher liegt der Standard Fokus auf der Ausführungsdauer. Andere Metriken wie CPU-Zeit und logische Lesevorgänge werden gesammelt, um die Untersuchung einzugrenzen.

  • Überprüfen Sie für derzeit ausgeführte Anweisungen total_elapsed_time - und cpu_time Spalten in sys.dm_exec_requests. Führen Sie die folgende Abfrage aus, um die Daten abzurufen:

    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;

  • Überprüfen Sie für frühere Ausführungen der Abfrage last_elapsed_time und last_worker_time Spalten in sys.dm_exec_query_stats. Führen Sie die folgende Abfrage aus, um die Daten abzurufen:

    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

    Hinweis

    Wenn avg_wait_time ein negativer Wert angezeigt wird, handelt es sich um eine parallele Abfrage.

  • Wenn Sie die Abfrage bedarfsgesteuert in SQL Server Management Studio (SSMS) oder Azure Data Studio ausführen können, führen Sie sie mit SET STATISTICS TIMEON und SET STATISTICS IOON aus.

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

    In Nachrichten werden dann die CPU-Zeit, die verstrichene Zeit und logische Lesevorgänge wie folgt angezeigt:

      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.

  • Wenn Sie einen Abfrageplan sammeln können, überprüfen Sie die Daten aus den Eigenschaften des Ausführungsplans.

    1. Führen Sie die Abfrage mit Include Actual Execution Plan (Tatsächlichen Ausführungsplan einschließen ) aus.

    2. Wählen Sie unter Ausführungsplan den Operator ganz links aus.

    3. Erweitern Sie unter Eigenschaften die QueryTimeStats-Eigenschaft .

    4. Überprüfen Sie ElapsedTime und CpuTime.

      Screenshot des Fensters mit den Eigenschaften des SQL Server Ausführungsplans mit der erweiterten Eigenschaft QueryTimeStats

Ausführen oder Warten: Warum sind Abfragen langsam?

Wenn Sie Abfragen finden, die Ihren vordefinierten Schwellenwert überschreiten, untersuchen Sie, warum sie langsam sein können. Die Ursache von Leistungsproblemen kann in zwei Kategorien gruppiert werden: "Running" oder "Waiting":

  • WARTEN: Abfragen können langsam sein, da sie lange auf einen Engpass warten. Eine ausführliche Liste der Engpässe bei Wartetypen finden Sie hier.

  • RUNNING: Abfragen können langsam sein, da sie über einen langen Zeitraum ausgeführt (ausgeführt) werden. Anders ausgedrückt: Diese Abfragen verwenden aktiv CPU-Ressourcen.

Eine Abfrage kann für einige Zeit ausgeführt werden und für einige Zeit in ihrer Lebensdauer (Dauer) warten. Ihr Fokus liegt jedoch darauf, zu bestimmen, welche Kategorie die dominante Kategorie ist, die zu ihrer lange verstrichenen Zeit beiträgt. Daher besteht die erste Aufgabe darin, festzulegen, in welche Kategorie die Abfragen fallen. Es ist einfach: Wenn eine Abfrage nicht ausgeführt wird, wartet sie. Im Idealfall verbringt eine Abfrage den größten Teil ihrer verstrichenen Zeit in einem ausgeführten Zustand und sehr wenig Zeit mit dem Warten auf Ressourcen. Außerdem wird eine Abfrage im besten Fall innerhalb oder unterhalb einer vordefinierten Baseline ausgeführt. Vergleichen Sie die verstrichene Zeit und die CPU-Zeit der Abfrage, um den Problemtyp zu ermitteln.

Typ 1: CPU-gebunden (Runner)

Wenn die CPU-Zeit nahe, gleich oder höher als die verstrichene Zeit ist, können Sie sie als CPU-gebundene Abfrage behandeln. Wenn die verstrichene Zeit beispielsweise 3000 Millisekunden (ms) beträgt und die CPU-Zeit 2900 ms beträgt, bedeutet dies, dass der Großteil der verstrichenen Zeit für die CPU aufgewendet wird. Dann können wir sagen, dass es sich um eine CPU-gebundene Abfrage handelt.

Beispiele für ausgeführte (CPU-gebundene) Abfragen:

Verstrichene Zeit (ms) CPU-Zeit (ms) Lesevorgänge (logisch)
3200 3000 300000
1080 1000 20

Logische Lesevorgänge – Das Lesen von Daten-/Indexseiten im Cache – sind am häufigsten die Treiber der CPU-Auslastung in SQL Server. Es kann Szenarien geben, in denen die CPU-Nutzung aus anderen Quellen stammt: einer While-Schleife (in T-SQL oder anderem Code wie XProcs oder SQL CRL-Objekten). Das zweite Beispiel in der Tabelle veranschaulicht ein solches Szenario, bei dem der Großteil der CPU nicht aus Lesevorgängen stammt.

Hinweis

Wenn die CPU-Zeit größer als die Dauer ist, deutet dies darauf hin, dass eine parallele Abfrage ausgeführt wird. Mehrere Threads verwenden die CPU gleichzeitig. Weitere Informationen finden Sie unter Parallele Abfragen – Runner oder Kellner.

Typ 2: Warten auf einen Engpass (Kellner)

Eine Abfrage wartet auf einen Engpass, wenn die verstrichene Zeit erheblich größer als die CPU-Zeit ist. Die verstrichene Zeit umfasst die Zeit zum Ausführen der Abfrage auf der CPU (CPU-Zeit) und die Zeit, die auf die Freigabe einer Ressource (Wartezeit) wartet. Wenn die verstrichene Zeit beispielsweise 2000 ms und die CPU-Zeit 300 ms beträgt, beträgt die Wartezeit 1700 ms (2000 - 300 = 1700). Weitere Informationen finden Sie unter Typen von Wartevorgängen.

Beispiele für wartende Abfragen:

Verstrichene Zeit (ms) CPU-Zeit (ms) Lesevorgänge (logisch)
2000 300 28000
10080 700 80000

Parallele Abfragen: Runner oder Kellner

Parallele Abfragen verbrauchen möglicherweise mehr CPU-Zeit als die Gesamtdauer. Das Ziel der Parallelität besteht darin, mehreren Threads die gleichzeitige Ausführung von Teilen einer Abfrage zu ermöglichen. In einer Sekunde der Uhr kann eine Abfrage acht Sekunden CPU-Zeit verwenden, indem acht parallele Threads ausgeführt werden. Daher ist es schwierig, eine CPU-gebundene oder wartende Abfrage basierend auf der verstrichenen Zeit und der CPU-Zeitdifferenz zu ermitteln. Befolgen Sie jedoch in der Regel die in den beiden obigen Abschnitten aufgeführten Prinzipien. Die Zusammenfassung lautet:

  • Wenn die verstrichene Zeit viel größer als die CPU-Zeit ist, betrachten Sie es als Kellner.
  • Wenn die CPU-Zeit viel größer als die verstrichene Zeit ist, betrachten Sie sie als Runner.

Beispiele für parallele Abfragen:

Verstrichene Zeit (ms) CPU-Zeit (ms) Lesevorgänge (logisch)
1200 8100 850000
3080 12300 1500000

Visuelle Darstellung der Methodik auf hoher Ebene

Der Screenshot zeigt eine allgemeine visuelle Darstellung der Methodik für die Problembehandlung bei langsamen Abfragen.

Diagnostizieren und Beheben von wartenden Abfragen

Wenn Sie festgestellt haben, dass Ihre Abfragen von Interesse Kellner sind, besteht Ihr nächster Schritt darin, sich auf die Behebung von Engpassproblemen zu konzentrieren. Fahren Sie andernfalls mit Schritt 4: Diagnostizieren und Auflösen ausgeführter Abfragen fort.

Um eine Abfrage zu optimieren, die auf Engpässe wartet, identifizieren Sie, wie lange die Wartezeit dauert und wo der Engpass ist (der Wartetyp). Nachdem der Wartetyp bestätigt wurde, verringern Sie die Wartezeit, oder beseitigen Sie die Wartezeit vollständig.

Um die ungefähre Wartezeit zu berechnen, subtrahieren Sie die CPU-Zeit (Workerzeit) von der verstrichenen Zeit einer Abfrage. In der Regel ist die CPU-Zeit die tatsächliche Ausführungszeit, und der verbleibende Teil der Lebensdauer der Abfrage wartet.

Beispiele für die Berechnung der ungefähren Wartezeit:

Verstrichene Zeit (ms) CPU-Zeit (ms) Wartezeit (ms)
3200 3000 200
7080 1000 6080

Identifizieren des Engpasses oder Wartens

  • Führen Sie die folgende Abfrage aus, >um Abfragen mit langer Wartezeit zu identifizieren (z. B. 20 % der insgesamt verstrichenen Wartezeit). Diese Abfrage verwendet Leistungsstatistiken für zwischengespeicherte Abfragepläne seit Beginn der 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

  • Führen Sie die folgende Abfrage aus, um aktuell ausgeführte Abfragen mit Wartezeiten von mehr als 500 ms zu identifizieren:

    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

  • Wenn Sie einen Abfrageplan erfassen können, überprüfen Sie die WaitStats aus den Ausführungsplaneigenschaften in SSMS:

    1. Führen Sie die Abfrage mit Include Actual Execution Plan (Tatsächlichen Ausführungsplan einschließen ) aus.
    2. Klicken Sie auf der Registerkarte Ausführungsplan mit der rechten Maustaste auf den Operator ganz links.
    3. Wählen Sie Eigenschaften und dann WaitStats-Eigenschaft aus.
    4. Überprüfen Sie waitTimeMs und WaitType.

  • Wenn Sie mit PSSDiag/SQLdiag- oder SQL LogScout LightPerf/GeneralPerf-Szenarien vertraut sind, sollten Sie eines dieser Szenarien verwenden, um Leistungsstatistiken zu sammeln und wartende Abfragen für Ihre SQL Server instance zu identifizieren. Sie können die gesammelten Datendateien importieren und die Leistungsdaten mit SQL Nexus analysieren.

Verweise zur Vermeidung oder Reduzierung von Wartezeiten

Die Ursachen und Lösungen für jeden Wartetyp variieren. Es gibt keine allgemeine Methode, um alle Wartetypen aufzulösen. Hier finden Sie Artikel zur Problembehandlung und Behebung häufiger Wartetypprobleme:

Beschreibungen vieler Wartetypen und deren Angabe finden Sie in der Tabelle unter Typen von Wartevorgängen.

Diagnostizieren und Auflösen von ausgeführten Abfragen

Wenn die CPU-Zeit (Worker) sehr nahe an der gesamt verstrichenen Dauer liegt, verbringt die Abfrage den größten Teil ihrer Lebensdauer mit der Ausführung. Wenn die SQL Server-Engine eine hohe CPU-Auslastung antreibt, kommt die hohe CPU-Auslastung in der Regel aus Abfragen, die eine große Anzahl logischer Lesevorgänge steuern (der häufigste Grund).

Führen Sie die folgende Anweisung aus, um die Abfragen zu identifizieren, die derzeit für hohe CPU-Aktivitäten verantwortlich sind:

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

Wenn die CPU im Moment nicht durch Abfragen belastet wird, können Sie die folgende Anweisung ausführen, um nach historischen, CPU-gebundenen Abfragen zu suchen:

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

Gängige Methoden zum Auflösen von ABFRAGEN mit langer Ausführungszeit, CPU-gebundene Abfragen