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 Gruppe von Abfragen. Die folgende Methodik hilft Ihnen, die Ursache für das Problem mit langsamen Abfragen einzugrenzen und Sie auf die Lösung zu leiten.

Suchen nach langsamen Abfragen

Um festzustellen, dass Abfrageleistungsprobleme in Ihrer SQL Server-Instanz auftreten, untersuchen Sie zunächst Abfragen anhand ihrer Ausführungszeit (verstrichene Zeit). Überprüfen Sie, ob die Zeit einen von Ihnen festgelegten Schwellenwert (in Millisekunden) basierend auf einer festgelegten Leistungsbasislinie überschreitet. In einer Stresstestumgebung haben Sie beispielsweise einen Schwellenwert für Ihre Workload festgelegt, der nicht mehr als 300 ms beträgt, 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 deren vordefinierte Leistungsbasisplandauer konzentrieren. Letztendlich kümmern sich Geschäftsbenutzer um die Gesamtdauer von Datenbankabfragen; Daher liegt der Schwerpunkt 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 bei früheren 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

    Notiz

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

  • Wenn Sie die Abfrage bei Bedarf in SQL Server Management Studio (SSMS) oder Azure Data Studio ausführen können, führen Sie sie mit SET STATISTICS TIME ON und SET STATISTICS IO ONaus.

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

    Dann sehen Sie aus Nachrichten die CPU-Zeit, die verstrichene Zeit und logische Lesevorgänge wie folgt:

      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 "Ist-Ausführungsplan einschließen" aus.

    2. Wählen Sie den am weitesten links angeordneten Operator aus dem Ausführungsplan aus.

    3. Erweitern Sie in "Properties" die QueryTimeStats-Eigenschaft.

    4. Überprüfen Sie "ElapsedTime " und "CpuTime".

      Screenshot des Sql Server-Ausführungsplaneigenschaftenfensters mit erweiterter QueryTimeStats-Eigenschaft.

Ausführen im Vergleich zu Warten: Warum sind Abfragen langsam?

Wenn Sie Abfragen finden, die Ihren vordefinierten Schwellenwert überschreiten, überprüfen Sie, warum sie langsam sein könnten. Die Ursache von Leistungsproblemen kann in zwei Kategorien unterteilt werden:

  • WARTEN: Abfragen können langsam sein, da sie lange auf einen Engpass warten. Eine detaillierte Liste von Engpässen in Denkarten finden Sie unter "Waits".See a detailed list of bottlenecks in types of Waits.

  • AUSFÜHREN: Abfragen können langsam sein, da sie lange ausgeführt werden (ausführen). Mit anderen Worten: Diese Abfragen verwenden aktiv CPU-Ressourcen.

Eine Abfrage kann in ihrer Lebensdauer eine gewisse Zeit ausgeführt werden und eine gewisse Zeit warten. Ihr Fokus liegt jedoch darin, zu bestimmen, welche die dominante Kategorie ist, die zu ihrer langen verstrichenen Zeit beiträgt. Daher müssen Sie zunächst ermitteln, 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 beim Warten auf Ressourcen. Im besten Fall wird eine Abfrage auch innerhalb oder unterhalb eines vordefinierten Basisplans 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 geschlossen ist, 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, es ist eine CPU-gebundene Abfrage.

Beispiele für die Ausführung von (CPU-gebundenen) Abfragen:

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

Logische Lesevorgänge – 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-Verwendung aus anderen Quellen stammt: eine While-Schleife (in T-SQL oder einem anderen Code wie XProcs oder SQL-CRL-Objekte). Das zweite Beispiel in der Tabelle veranschaulicht ein solches Szenario, bei dem der Großteil der CPU nicht aus Lesevorgängen stammt.

Notiz

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

Typ 2: Warten auf einen Engpass (Waiter)

Eine Abfrage wartet auf einen Engpass, wenn die verstrichene Zeit deutlich größer als die CPU-Zeit ist. Die verstrichene Zeit umfasst die Ausführung 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 beträgt und die CPU-Zeit 300 ms beträgt, beträgt die Wartezeit 1700 ms (2000 - 300 = 1700). Weitere Informationen finden Sie unter "Waits Types".

Beispiele für Warteabfragen:

Verstrichene Zeit (ms) CPU-Zeit (ms) Lesevorgänge (logisch)
2.000 300 28000
10080 700 80.000

Parallele Abfragen – Runner oder Waiter

Parallele Abfragen verwenden möglicherweise mehr CPU-Zeit als die Gesamtdauer. Das Ziel der Parallelität besteht darin, mehreren Threads gleichzeitig die Ausführung von Teilen einer Abfrage zu ermöglichen. In einer Sekunde der Taktzeit kann eine Abfrage acht Sekunden CPU-Zeit verwenden, indem sie acht parallele Threads ausführen. Daher wird es schwierig, eine CPU-gebundene oder eine Warteabfrage basierend auf der verstrichenen Zeit und CPU-Zeitdifferenz zu ermitteln. Beachten Sie jedoch in der Regel die in den obigen beiden Abschnitten aufgeführten Grundsätze. Die Zusammenfassung lautet:

  • Wenn die verstrichene Zeit viel größer als die CPU-Zeit ist, sollten Sie es als Warteprogramm betrachten.
  • Wenn die CPU-Zeit viel größer als die verstrichene Zeit ist, sollten Sie es als Läufer betrachten.

Beispiele für parallele Abfragen:

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

Allgemeine visuelle Darstellung der Methodik

Der Screenshot zeigt eine allgemeine visuelle Darstellung der Methodik zur Problembehandlung für langsame Abfragen.

Diagnostizieren und Auflösen von Abfragen im Wartezustand

Wenn Sie festgestellt haben, dass Ihre Interessenabfragen Waiters sind, besteht ihr nächster Schritt darin, sich auf die Behebung von Engpassproblemen zu konzentrieren. Wechseln Sie andernfalls zu Schritt 4: Diagnostizieren und Auflösen von ausgeführten Abfragen.

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

Um die ungefähre Wartezeit zu berechnen, subtrahieren Sie die CPU-Zeit (Arbeitszeit) 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 1.000 6080

Identifizieren des Engpasses oder Wartens

  • Führen Sie die folgende Abfrage aus, >um historische Langewarteabfragen zu identifizieren (z. B. 20 % der gesamt verstrichenen Wartezeit). Diese Abfrage verwendet Seit dem Start von SQL Server Leistungsstatistiken für zwischengespeicherte Abfragepläne.

    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 derzeit ausgeführte Abfragen mit einer Wartezeit 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 sammeln können, überprüfen Sie die WaitStats aus den Ausführungsplaneigenschaften in SSMS:

    1. Führen Sie die Abfrage mit "Ist-Ausführungsplan einschließen" aus.
    2. Klicken Sie mit der rechten Maustaste auf den operator ganz links auf der Registerkarte "Ausführungsplan "
    3. Wählen Sie "Eigenschaften" und dann "WaitStats "-Eigenschaft aus.
    4. Überprüfen Sie die WaitTimeMs und WaitType.

  • Wenn Sie mit PSSDiag/SQLdiag oder SQL LogFinder LightPerf/GeneralPerf-Szenarien vertraut sind, sollten Sie eine dieser Szenarien verwenden, um Leistungsstatistiken zu sammeln und Wartende Abfragen auf Ihrer SQL Server-Instanz zu identifizieren. Sie können die gesammelten Datendateien importieren und die Leistungsdaten mit SQL Nexus analysieren.

Verweise zur Beseitigung oder Reduzierung von Wartezeiten

Die Ursachen und Auflösungen für jeden Wartetyp variieren. Es gibt keine allgemeine Methode zum Auflösen aller Wartetypen. Im Folgenden finden Sie Artikel zur Problembehandlung und Behebung häufig auftretender Wartetypprobleme:

Beschreibungen vieler Wait-Typen und deren Angabe finden Sie in der Tabelle unter "Waits".

Diagnostizieren und Auflösen von Abfragen im Ausführungszustand

Wenn die CPU-Zeit (Worker) sehr nahe an der gesamt verstrichenen Dauer liegt, verbringt die Abfrage den größten Teil der Ausführungsdauer. Wenn das SQL Server-Modul eine hohe CPU-Auslastung steuert, kommt die hohe CPU-Auslastung 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 eine cpu-hohe CPU-Aktivität 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 Abfragen die CPU derzeit nicht steuern, 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

Häufige Methoden zum Auflösen von zeitintensiven CPU-gebundenen Abfragen