Freigeben über

Fehlerbehebung von Abfragen, die in SQL Server scheinbar endlos ausgeführt werden

Dieser Artikel enthält Anleitungen zur Problembehandlung bei Problemen, bei denen eine Microsoft SQL Server-Abfrage eine übermäßige Zeit bis zum Ende (Stunden oder Tage) benötigt.

Symptome

Dieser Artikel konzentriert sich auf Abfragen, die scheinbar ohne Ende ausgeführt oder kompiliert werden. Das heißt, ihre CPU-Auslastung nimmt weiter zu. Dieser Artikel gilt nicht für Abfragen, die blockiert oder auf eine Ressource warten, die nie freigegeben wurde. In diesen Fällen bleibt die CPU-Auslastung konstant oder ändert sich nur geringfügig.

Wichtig

Wenn eine Abfrage weiterlaufen gelassen wird, kann sie schließlich erfolgreich abgeschlossen werden. Dieser Vorgang kann nur ein paar Sekunden oder mehrere Tage dauern. In einigen Situationen kann die Abfrage wirklich endlos sein, z. B. wenn eine WHILE-Schleife nicht beendet wird. Der Begriff "never-ending" wird hier verwendet, um die Wahrnehmung einer Abfrage zu beschreiben, die nicht beendet wird.

Ursache

Häufige Ursachen für lang laufende Abfragen (nie endend) sind:

  • Geschachtelte Schleife (NL) wird in sehr großen Tabellen verknüpft: Aufgrund der Art von NL-Verknüpfungen kann eine Abfrage, die Tabellen verknüpft, die viele Zeilen enthalten, möglicherweise lange ausgeführt werden. Weitere Informationen finden Sie unter "Verknüpfungen".
    • Ein Beispiel für eine NL-Verknüpfung ist die Verwendung von TOP, , FASToder EXISTS. Auch wenn ein Hash- oder Merge-Join schneller ist, kann der Optimierer keinen Operator aufgrund des Zeilen-Ziels verwenden.
    • Ein weiteres Beispiel für eine NL-Verknüpfung ist die Verwendung eines Ungleichheitsverknüpfungs-Prädikats in einer Abfrage. Beispiel: SELECT .. FROM tab1 AS a JOIN tab 2 AS b ON a.id > b.id. Der Optimierer kann hier weder Zusammenführungs- noch Hash-Verknüpfungen verwenden.
  • Veraltete Statistiken: Abfragen, die einen Plan basierend auf veralteten Statistiken auswählen, sind möglicherweise suboptimal und dauern lange.
  • Endlosschleifen: T-SQL-Abfragen, die WHILE-Schleifen verwenden, sind möglicherweise falsch geschrieben. Der resultierende Code verlässt die Schleife nie und wird endlos ausgeführt. Diese Abfragen enden wirklich nie. Sie laufen, bis sie manuell getötet werden.
  • Komplexe Abfragen mit vielen Verknüpfungen und großen Tabellen: Abfragen, die viele verknüpfte Tabellen umfassen, weisen in der Regel komplexe Abfragepläne auf, die eine lange Ausführung dauern können. Dieses Szenario ist in analytischen Abfragen üblich, die keine Zeilen herausfiltern und eine große Anzahl von Tabellen umfassen.
  • Fehlende Indizes: Abfragen können erheblich schneller ausgeführt werden, wenn entsprechende Indizes in Tabellen verwendet werden. Indizes ermöglichen die Auswahl einer Teilmenge der Daten, um einen schnelleren Zugriff zu ermöglichen.

Lösung

Schritt 1: Entdecken von nie endenden Abfragen

Suchen Sie nach einer nie endenden Abfrage, die auf dem System ausgeführt wird. Sie müssen ermitteln, ob eine Abfrage über eine lange Ausführungszeit, eine lange Wartezeit (bei einem Engpass hängen geblieben) oder eine lange Kompilierungszeit verfügt.

1.1 Ausführen einer Diagnose

Führen Sie die folgende Diagnoseabfrage in Ihrer SQL Server-Instanz aus, in der die nie endende Abfrage aktiv ist:

DECLARE @cntr INT = 0

WHILE (@cntr < 3)
BEGIN
    SELECT TOP 10 s.session_id,
                    r.status,
                    CAST(r.cpu_time / (1000 * 60.0) AS DECIMAL(10,2)) AS cpu_time_minutes,
                    CAST(r.total_elapsed_time / (1000 * 60.0) AS DECIMAL(10,2)) AS elapsed_minutes,
                    r.logical_reads,
                    r.wait_time,
                    r.wait_type,
                    r.wait_resource,
                    r.reads,
                    r.writes,
                    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

1.2 Untersuchen der Ausgabe

Es gibt mehrere Szenarien, die dazu führen können, dass eine Abfrage lange ausgeführt wird: lange Ausführung, lange Wartezeit und lange Kompilierung. Weitere Informationen dazu, warum eine Abfrage möglicherweise langsam ausgeführt wird, finden Sie unter "Ausführen" und "Warten": Warum werden Abfragen langsam?

Lange Ausführungszeit

Die Schritte zur Problembehandlung in diesem Artikel sind anwendbar, wenn Sie eine ähnliche Ausgabe wie die folgende erhalten, wobei die CPU-Zeit proportional zur verstrichenen Zeit ohne erhebliche Wartezeiten erhöht wird.

session_id status CPU-Zeit_in_Minuten verstrichene_Zeit_Minuten logical_reads Wartezeit_Minuten wait_type
56 „Wird ausgeführt“ 64.40 23.50 0 0.00 NULL

Die Abfrage wird kontinuierlich ausgeführt, wenn sie Folgendes hat:

  • Eine zunehmende CPU-Zeit
  • Ein Status von running oder runnable
  • Minimale oder null Wartezeit
  • Keine wait_type

In diesem Fall liest die Abfrage Zeilen, verknüpfen, Ergebnisse verarbeiten, berechnen oder formatieren. Diese Aktivitäten sind alle CPU-gebundenen Aktionen.

Notiz

Änderungen in logical_reads sind in diesem Fall nicht relevant, da einige CPU-gebundene T-SQL-Anforderungen, wie z. B. das Ausführen von Berechnungen oder einer WHILE Schleife, überhaupt keine logischen Lesevorgänge durchführen.

Wenn die langsame Abfrage diese Kriterien erfüllt, konzentrieren Sie sich auf die Reduzierung der Laufzeit. Das Reduzieren der Laufzeit umfasst typischerweise eine Verringerung der Anzahl der Zeilen, die die Abfrage im gesamten Verlauf verarbeiten muss, indem Indizes angewendet, die Abfrage umgeschrieben oder Statistiken aktualisiert werden. Weitere Informationen finden Sie im Abschnitt "Entschließung" .

Lange Wartezeit

Dieser Artikel gilt nicht für Szenarien mit langen Wartezeiten. In einem Wartezeitszenario erhalten Sie möglicherweise eine Ausgabe, die dem folgenden Beispiel ähnelt, in dem sich die CPU-Auslastung nicht ändert oder sich geringfügig ändert, da die Sitzung auf eine Ressource wartet:

session_id status CPU-Zeit_in_Minuten verstrichene_Zeit_Minuten logical_reads Wartezeit_Minuten wait_type
56 Angehalten 0.03 4.20 50 4.10 LCK_M_U

Der Wartetyp gibt an, dass die Sitzung auf eine Ressource wartet. Eine lange verstrichene Zeit und eine lange Wartezeit deuten darauf hin, dass die Sitzung den Großteil ihrer Dauer auf diese Ressource wartet. Тhe kurze CPU-Zeit gibt an, dass nur wenig Zeit für die Verarbeitung der Abfrage aufgewendet wurde.

Informationen zur Problembehandlung von Abfragen, die aufgrund von Wartezeiten lang sind, finden Sie unter Problembehandlung für langsam ausgeführte Abfragen in SQL Server.

Lange Kompilierungszeit

In seltenen Fällen können Sie feststellen, dass die CPU-Auslastung im Laufe der Zeit kontinuierlich steigt, aber nicht von der Abfrageausführung gesteuert wird. Stattdessen kann eine übermäßig lange Kompilierung (die Analyse und Kompilierung einer Abfrage) die Ursache sein. Überprüfen Sie in diesen Fällen die transaction_name Ausgabespalte auf einen Wert von sqlsource_transform. Dieser Transaktionsname gibt eine Kompilierung an.

Schritt 2: Manuelles Sammeln von Diagnoseprotokollen

Nachdem Sie festgestellt haben, dass eine nie endende Abfrage im System vorhanden ist, können Sie die Plandaten der Abfrage sammeln, um weitere Problembehandlungen durchzuführen. Verwenden Sie zum Sammeln der Daten je nach Sql Server-Version eine der folgenden Methoden.

Führen Sie die folgenden Schritte aus, um Diagnosedaten mithilfe von SQL Server Management Studio (SSMS) zu sammeln:

  1. Erfassen Sie den XML-Code für den geschätzten Abfrageausführungsplan .

  2. Überprüfen Sie den Abfrageplan, um zu erfahren, ob die Daten offensichtliche Anzeichen dafür zeigen, was die Langsamkeit verursacht. Beispiele für typische Indikationen sind:

    • Tabellen- oder Indexüberprüfungen (geschätzte Zeilen anzeigen)
    • Geschachtelte Schleifen, die von einem riesigen äußeren Tabellendatensatz gesteuert werden
    • Geschachtelte Schleifen mit einer großen Verzweigung auf der Innenseite der Schleife
    • Tabellenspools
    • Funktionen in der SELECT-Liste, die lange benötigen, um jede Zeile zu verarbeiten.

  3. Wenn die Abfrage jederzeit schneller ausgeführt wird, können Sie die "schnellen" Ausführungen (tatsächlicher XML-Ausführungsplan) erfassen, um Ergebnisse zu vergleichen.

Verwenden von SQL LogFinder zum Erfassen von nie endenden Abfragen

Sie können SQL LogFinder verwenden, um Protokolle zu erfassen, während eine nie endende Abfrage ausgeführt wird. Verwenden Sie das Nie endende Abfrageszenario mit dem folgenden Befehl:

.\SQL_LogScout.ps1 -Scenario "NeverEndingQuery" -ServerName "SQLInstance"

Notiz

Für diesen Protokollerfassungsprozess muss die lange Abfrage mindestens 60 Sekunden CPU-Zeit verbrauchen.

SQL LogFinder erfasst mindestens drei Abfragepläne für jede hoch CPU-verbrauchende Abfrage. Sie können Dateinamen finden, die ähnlich aussehen servername_datetime_NeverEnding_statistics_QueryPlansXml_Startup_sessionId_#.sqlplan. Sie können diese Dateien im nächsten Schritt verwenden, wenn Sie Pläne überprüfen, um den Grund für die lange Abfrageausführung zu identifizieren.

Schritt 3: Überprüfen der gesammelten Pläne

In diesem Abschnitt wird erläutert, wie Sie die gesammelten Daten überprüfen. Es verwendet die mehreren XML-Abfragepläne (mithilfe der Erweiterung .sqlplan), die in Microsoft SQL Server 2016 SP1 und höheren Builds und Versionen gesammelt werden.

Vergleichen Sie Ausführungspläne , indem Sie die folgenden Schritte ausführen:

  1. Öffnen Einer zuvor gespeicherten Abfrageausführungsplandatei (.sqlplan).

  2. Klicken Sie mit der rechten Maustaste in einen leeren Bereich des Ausführungsplans, und wählen Sie "Showplan vergleichen" aus.

  3. Wählen Sie die zweite Abfrageplandatei aus, die Sie vergleichen möchten.

  4. Suchen Sie nach dicken Pfeilen, die eine große Anzahl von Zeilen angeben, die zwischen Operatoren fließen. Wählen Sie dann den Operator vor oder nach dem Pfeil aus, und vergleichen Sie die Anzahl der tatsächlichen Zeilen über die beiden Pläne.

  5. Vergleichen Sie den zweiten und dritten Plan, um zu erfahren, ob der größte Zeilenfluss in denselben Operatoren erfolgt.

    Beispiel:

    Screenshot des Vergleichs von Abfrageplänen in SSMS.

Schritt 4: Auflösung

  1. Stellen Sie sicher, dass Statistiken für die Tabellen aktualisiert werden, die in der Abfrage verwendet werden.

  2. Suchen Sie im Abfrageplan nach fehlenden Indexempfehlungen, und wenden Sie alle gefundenen Indexempfehlungen an.

  3. Vereinfachen Sie die Abfrage:

    • Verwenden Sie selektivere WHERE Prädikate, um die Daten zu reduzieren, die im Vorfeld verarbeitet werden.
    • Trennen Sie es.
    • Wählen Sie einige Teile in temporäre Tabellen aus, und verknüpfen Sie sie später.
    • Entfernen Sie TOP, EXISTSund FAST (T-SQL) in den Abfragen, die aufgrund eines Optimiererzeilenziels lange ausgeführt werden.
    • Vermeiden Sie Common Table Expressions (CTEs) in solchen Fällen, da sie Anweisungen zu einer einzigen großen Abfrage kombinieren.

  4. Versuchen Sie, Abfragehinweise zu verwenden, um einen besseren Plan zu erstellen:

    • HASH JOIN oder MERGE JOIN Hinweis
    • FORCE ORDER-Hinweis
    • FORCESEEK-Hinweis
    • RECOMPILE
    • USE PLAN N'<xml_plan>' (wenn Sie über einen schnellen Abfrageplan verfügen, den Sie erzwingen können)

  5. Verwenden Sie Abfragespeicher (QDS), um einen gut bekannten Plan zu erzwingen, wenn ein solcher Plan vorhanden ist und ob Ihre SQL Server-Version Abfragespeicher unterstützt.

Verwandte Inhalte

  • Last updated on