Bewertung der Abfrageleistung mit Ausführungsplänen und DMVs

  • 10 Minuten

Wenn eine Abfrage langsamer als erwartet ausgeführt wird, besteht der erste Schritt darin zu verstehen, wie die Datenbank-Engine sie ausführt. Ausführungspläne zeigen Ihnen die genauen Operatoren, Datenzugriffsmethoden und Ressourcenkosten, die der Optimierer für eine Abfrage ausgewählt hat. Dynamische Verwaltungsansichten (DYNAMIC Management Views, DMVs) ergänzen dies, indem Laufzeitleistungsdaten für alle Abfragen in der Datenbank verfügbar sind, sodass Sie die teuersten finden können, bevor Sie in einen einzelnen Plan eintauchen.

Lesen von Ausführungsplänen

Ein Ausführungsplan ist der Satz von Anweisungen, die der Abfrageoptimierer zum Abrufen und Verarbeiten von Daten erzeugt. Es definiert, auf welche Tabellen zuerst zugegriffen werden soll, ob Indizes oder Scantabellen verwendet werden sollen, und wie Sie Ergebnisse verknüpfen, filtern, sortieren und aggregieren. Der Optimierer wertet mehrere Kandidatenpläne aus und wählt die Pläne mit den niedrigsten geschätzten Kosten aus.

Screenshot eines grafischen tatsächlichen Ausführungsplans in SQL Server Management Studio mit Operatoren, Pfeilen, die den Datenfluss angeben, und Kostenprozentsätze für jeden Schritt.

Es gibt zwei Arten von Ausführungsplänen:

  • Geschätzter Ausführungsplan: Generiert, ohne die Abfrage auszuführen. Zeigt geplante Operatoren und geschätzte Zeilenanzahlen basierend auf Statistiken an. Verwenden Sie geschätzte Pläne für eine schnelle Analyse, ohne dass sich dies auf die Datenbank auswirkt.
  • Tatsächlicher Ausführungsplan: Während der Abfrageausführung erfasst. Es umfasst den geschätzten Plan plus tatsächliche Zeilenanzahlen, tatsächliche Ausführungszeiten, Speicherzuweisungen und Warnungen. Der tatsächliche Ausführungsplan zeigt Diskrepanzen zwischen dem, was der Optimierer erwartete, und dem, was tatsächlich geschah.

Um einen geschätzten Plan anzuzeigen, führen Sie SET SHOWPLAN_XML ON vor der Abfrage aus, oder wählen Sie " Geschätzten Ausführungsplan anzeigen " in SQL Server Management Studio (SSMS) aus. Um einen tatsächlichen Plan zu erfassen, führen Sie SET STATISTICS XML ON aus oder wählen Sie in SSMS tatsächlichen Ausführungsplan einschließen, bevor Sie die Abfrage ausführen.

Obwohl die geschätzten und tatsächlichen Pläne ähnlich aussehen, sind die Laufzeitmetriken des tatsächlichen Plans für die Diagnose von Leistungsproblemen entscheidend. Wenn beispielsweise die geschätzte Zeilenanzahl für einen Tabellenscan 100 beträgt, die tatsächliche Zeilenanzahl jedoch 10.000 beträgt, kann dies auf veraltete Statistiken hinweisen, die zu einer schlechten Planauswahl führen. Der Optimierer kompiliert den Plan basierend auf Statistiken, wenn er zum ersten Mal auf eine Abfrage trifft. Wenn diese Statistiken nicht die aktuelle Datenverteilung widerspiegeln, kann der Plan schlecht ausgeführt werden.

Identifizieren häufiger Probleme in Ausführungsplänen

Ausführungspläne werden von links nach rechts, von oben nach unten gelesen. Die ersten Operatoren greifen auf die Basistabellen zu, und der endgültige Operator erzeugt das Abfrageergebnis. Suchen Sie nach den folgenden häufigen Problemen:

Operatortypen informieren Sie, wie die Engine auf Daten zugreift. Es gibt viele Operatortypen, die jeweils eine andere Methode zum Abrufen oder Verarbeiten von Daten darstellen. Beispielsweise stellt ein Index Seek-Operator eine hocheffiziente Methode dar, die auf bestimmte Zeilen mithilfe von Indexschlüsseln ausgerichtet ist. Ein Tabellenscan - oder Indexscanoperator stellt dagegen eine weniger effiziente Methode dar, die jede Zeile liest. Wenn ein Scan auf einer großen Tabelle angezeigt wird, benötigen Sie wahrscheinlich einen Index. Wenn die E-Commerce-Anwendung beispielsweise Bestellungen nach Datum abfragt und der Plan einen Gruppierten Indexscan in der Orders Tabelle anzeigt, könnte das Hinzufügen eines nicht gruppierten Indexes in der OrderDate Spalte diesen Scan in eine Suche ändern. Beachten Sie, dass nicht alle Scans schlecht sind. Wenn eine Tabelle klein ist oder die Suchbedingung die meisten Zeilen in einer Tabelle zurückgibt, ist ein Scan möglicherweise die effizienteste Zugriffsmethode. Berücksichtigen Sie immer den Kontext der Abfrage und die Größe der Daten. Kennen Sie Ihre Daten und verwenden Sie Ausführungspläne, um zu überprüfen, ob die Zugriffsmethode sinnvoll ist.

Geschätzte im Vergleich zu tatsächlichen Zeilenanzahlen zeigen an, ob die Annahmen des Optimierers mit der Realität übereinstimmen. Der Optimierer basiert auf Statistiken, Metadaten, die die Verteilung und Dichte von Daten in Ihren Tabellen beschreiben. Wenn diese Statistiken veraltet sind, unterscheiden sich die geschätzten und tatsächlichen Zeilenanzahlen. Wenn der Optimierer die Zeilenanzahl unterschätzt, kann es eine geschachtelte Schleifen-Verknüpfung (die jeweils eine Zeile aus der inneren Tabelle einer Verknüpfung verarbeitet) auswählen, wenn eine Hash-Verknüpfung (die eine Hashtabelle im Speicher für schnelle Nachschlagevorgänge erstellt) schneller ist oder zu wenig Arbeitsspeicher für einen Sortiervorgang zuweist. Statistiken können nach signifikanten Datenänderungen veraltet werden, sodass das Aktualisieren von Statistiken mit UPDATE STATISTICS oder das Aktivieren von automatischen Statistikaktualisierungen dazu beitragen kann, dass der Optimierer bessere Entscheidungen treffen kann.

Schlüsselsuchoperatoren werden angezeigt, wenn das Modul Zeilen über einen nicht gruppierten Index findet, aber zusätzliche Spalten aus dem gruppierten Index benötigt. Für jede entsprechende Zeile führt das Modul einen zusätzlichen Roundtrip zum gruppierten Index durch, um diese Spalten abzurufen. Wenn der Filter viele Zeilen zurückgibt, steigt die Anzahl dieser zusätzlichen Suchoperationen schnell an. Wenn die E-Commerce-Anwendung beispielsweise Bestellungen nach CustomerID filtert, aber auch OrderDate, TotalAmount und ShippingAddress auswählt, und der nicht-gruppierte Index auf CustomerID diese Spalten nicht enthält, zeigt der Plan einen Key Lookup für jede übereinstimmende Bestellung an. Sie können die Key Lookups beseitigen, indem Sie die fehlenden Spalten als inkludierte Spalten in den Index einfügen. Denken Sie daran, dass enthaltene Spalten die Indexgröße erhöhen, wodurch Schreibvorgänge verlangsamt werden können, sodass der Leseleistungsvorteil gegenüber dem Schreibaufwand abgewogen wird.

Dicke Pfeile zwischen Operatoren stellen die Anzahl der Zeilen dar, die zwischen ihnen fließen. Ein unerwartet dicker Pfeil am Anfang des Plans (beim Lesen von links nach rechts, oben nach unten) bedeutet oft, dass ein fehlender Filter oder Index zu viele Zeilen durchlässt.

Fehlende Indexvorschläge werden als grüner hervorgehobener Text oben im grafischen Ausführungsplan in SSMS angezeigt. Wenn der Optimierer erkennt, dass ein Index die Kosten einer Abfrage erheblich reduzieren könnte, wird direkt im Plan eine Empfehlung angezeigt. Klicken Sie mit der rechten Maustaste auf den Vorschlag, und wählen Sie "Fehlende Indexdetails" aus, um eine CREATE INDEX Anweisung zu generieren, die Sie überprüfen und ausführen können. Diese Vorschläge sind einer der einfachsten Gewinne, die Sie aus dem Lesen eines Ausführungsplans erhalten können.

Warnungen werden als gelbes Dreieck mit einem Ausrufezeichen (⚠) auf dem betroffenen Operator angezeigt. Jede Warnung verweist auf eine Optimierungschance. Zu den allgemeinen Warnungen gehören:

  • Fehlende Statistiken: Der Optimierer konnte keine Statistiken für eine Spalte finden, sodass er geschätzte Zeilenanzahlen verwendet hat, anstatt die tatsächliche Datenverteilung zu verwenden. Um dieses Problem zu beheben, erstellen Sie Statistiken zu den Spalten, die in Ihren Abfragen verwendet werden, oder aktualisieren Sie vorhandene Statistiken, wenn sie veraltet sind.
  • Übermäßige Speichererteilung: Die Abfrage hat mehr Arbeitsspeicher angefordert, als erforderlich war, wodurch Ressourcen verschwendet werden, die andere Abfragen verwenden könnten. Dieses Problem tritt häufig auf, wenn der Optimierer die Zeilenanzahl überhebt. Das Aktualisieren von Statistiken oder das Umschreiben der Abfrage zum früheren Filtern von Zeilen kann dazu beitragen, Arbeitsspeichererteilungen zu reduzieren.
  • Kein Join-Prädikat: Zwei Tabellen werden ohne eine ordnungsgemäße Bedingung verknüpft, wodurch ein kartesisches Produkt erzeugt wird, das jede mögliche Zeilenkombination zurückgibt. Überprüfen Sie Ihre Abfrage auf eine fehlende oder falsche ON Klausel.
  • Implizite Konvertierung: Eine Datentypabweichung zwingt die Engine, Werte zur Laufzeit zu konvertieren, wodurch eine Indexsuche in einen Scan umgewandelt werden kann. Wenn beispielsweise eine WHERE Klausel einen nvarchar Parameter mit einer varchar Spalte vergleicht, konvertiert der Motor jede Zeile in der Spalte zu nvarchar, bevor der Vergleich stattfindet. Um implizite Konvertierungen zu beheben, stimmen Sie den Datentypen in Ihren Abfrageparametern den Spaltendefinitionen zu.
  • Sortier- oder Hash-Überlauf: Ein Sortier- oder Hashvorgang hat den gewährten Arbeitsspeicher überschritten, und daher wurden Zwischenergebnisse in die tempdb ausgelagert. Diese Vorgänge sind die zweithäufigste Ursache einer hohen CPU-Auslastung nach Scans. Wenn eine Spill-Warnung angezeigt wird, hat der Optimierer wahrscheinlich die Zeilenanzahl unterschätzt und zu wenig Arbeitsspeicher angefordert. Durch das Ausführen von UPDATE STATISTICS können die statistischen Daten der Tabelle aktualisiert oder kann die Abfrage umgeschrieben werden, um die Anzahl der Zeilen vor der Sortierung zu reduzieren. Der Überlauf wird dadurch in vielen Fällen eliminiert.

Ausführungspläne sind ein leistungsfähiges Tool zum Verständnis der Abfrageleistung. Sie zeigen Ihnen genau, wie das Modul eine Abfrage ausführt und wo sich die Engpässe befinden. Indem Sie lernen, Ausführungspläne effektiv zu lesen, können Sie Leistungsprobleme in Ihren Datenbankabfragen schnell erkennen und beheben.

Abfrage von DMVs nach Laufzeitleistungsdaten

DMVs machen Echtzeit- und akkumulierte Leistungsdaten aus dem Datenbankmodul verfügbar. Azure SQL-Datenbank benötigt VIEW DATABASE STATE Berechtigung, um sie abzufragen. Während Ausführungspläne zeigen, wie eine einzelne Abfrage ausgeführt wird, zeigen DMVs, was in allen Abfragen passiert, was Ihnen hilft, die teuersten zuerst zu finden.

Finden Sie die teuersten Abfragen

CPU-Zeit, logische Lesevorgänge und Ausführungsanzahl sind die am häufigsten verwendeten Metriken, um teure Abfragen zu identifizieren. Hohe CPU-Zeit oder logische Lesevorgänge deuten darauf hin, dass eine Abfrage ressourcenintensiv ist, während eine hohe Ausführungsanzahl bedeutet, dass sogar eine moderate teure Abfrage große Auswirkungen auf die Gesamtleistung haben kann. Überprüfen Sie zunächst die wichtigsten Abfragen nach durchschnittlicher CPU-Zeit oder logischen Lesevorgängen, um Kandidaten für die Optimierung zu finden.

sys.dm_exec_query_stats gibt aggregierte Leistungsstatistiken für zwischengespeicherte Abfragepläne zurück. Verbinden Sie es mit sys.dm_exec_sql_text, um den Abfragetext anzuzeigen und mit sys.dm_exec_query_plan, um den Ausführungsplan abzurufen.

Die folgende Abfrage findet die wichtigsten 10 Abfragen nach durchschnittlicher CPU-Zeit:

SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    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 query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY avg_cpu_time DESC;

Mit diesem Skript können Sie ermitteln, welche Abfragen Ihre Aufmerksamkeit verdienen. Hohe avg_logical_reads im Verhältnis zur Größe des Resultsets deuten häufig auf fehlende Indizes oder ineffiziente Pläne hin. Seien Sie jedoch vorsichtig, wenn Sie diese Ergebnisse interpretieren. Eine Abfrage mit hoher durchschnittlicher CPU-Zeit, die nur einmal pro Tag ausgeführt wird, kann weniger als eine moderate Abfrage sein, die tausende Mal pro Stunde ausgeführt wird. Berücksichtigen Sie beim Priorisieren immer sowohl die durchschnittlichen Kosten als auch die Ausführungsanzahl. Sie können außerdem nach avg_logical_reads sortieren, um Abfragen zu ermitteln, die eine hohe E/A-Last aufweisen, weil dies häufig auf fehlende Indizes oder ineffiziente Zugriffsmethoden hinweist.

Überprüfen der derzeit ausgeführten Abfragen

Während die vorherige Abfrage die teuersten früheren Abfragen im Plancache anzeigt, liefert sys.dm_exec_requests eine Momentaufnahme jeder aktuell ausgeführten Anforderung. Sie enthält Spalten für CPU-Zeit, Lesevorgänge, Schreibvorgänge, Wartetyp, Wartezeit und Blockierung der Sitzungs-ID. Verwenden Sie diese Ansicht, um aktive Abfragen zu ermitteln, die zu viele Ressourcen benötigen oder hängen, weil sie auf Sperren warten. Diese Ansicht ist eine der wichtigsten DMVs für die Echtzeit-Leistungsüberwachung und -Problembehandlung.

SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.cpu_time,
    r.logical_reads,
    t.text AS query_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id > 50
ORDER BY r.cpu_time DESC;

Diese Abfrage filtert Systemsitzungen (Sitzungs-IDs 1-50) und Bestellungen nach CPU-Zeit aus. Sie können auch nach logical_reads I/O-intensiven Abfragen sortieren. Anhand der Spalten wait_type und wait_time können Sie feststellen, ob eine Abfrage auf Sperren, E/A oder andere Ressourcen wartet.

Ermitteln fehlender Indizes

Früher haben wir gesehen, wie Ausführungspläne Ihnen fehlende Indexvorschläge für eine einzelne Abfrage anzeigen können. Die fehlenden Index-DMVs geben Ihnen eine umfassendere Ansicht, welche Indizes der Optimierer für alle Abfragen verwenden würde, wenn sie vorhanden sind. Diese Ansichten sind eine großartige Möglichkeit, Optimierungsmöglichkeiten zu finden, die sich auf mehrere Abfragen auswirken. sys.dm_db_missing_index_details zeigt die Tabelle sowie die Spalten 'Gleichheit', 'Ungleichheit' und 'Eingeschlossene Spalten' an. sys.dm_db_missing_index_group_stats stellt eine Verbesserungsmaßnahme bereit, die die Kostenreduzierung schätzt.

SELECT
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_total_user_cost * migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans) AS improvement_measure
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

Diese Abfrage berechnet eine improvement_measure für jede fehlende Indexempfehlung, bei der es sich um ein Produkt der durchschnittlichen Kosten von Abfragen handelt, die vom Index, der durchschnittlichen Verbesserung des Prozentsatzes und der Häufigkeit der Ausführung dieser Abfragen profitieren würden. Durch Sortieren nach diesem Kriterium können Sie festlegen, welche fehlenden Indizes zuerst erstellt werden sollen. Denken Sie jedoch daran, dass diese Ergebnisse nur Empfehlungen sind, die auf den Abfragen basieren, die sich derzeit im Plancache befinden. Überprüfen Sie immer die vorgeschlagenen Indexspalten, und testen Sie deren Auswirkungen sowohl auf die Abfrageleistung als auch auf den Schreibaufwand, bevor Sie sie zur Produktion hinzufügen.

Hinweis

Fehlende Indexempfehlungen sind Vorschläge, keine Direktiven. Testen Sie immer die Auswirkungen eines neuen Indexes sowohl auf die Abfrageleistung als auch auf den Schreibaufwand, bevor Sie ihn zur Produktion hinzufügen.

Überwachen aktiver Sitzungen und wartender Aufgaben

sys.dm_exec_sessions bietet Informationen zu allen authentifizierten Sitzungen, einschließlich Anmeldezeit, Host-Name, Programmname sowie kumulative CPU-Zeit und Lesevorgänge. Durch die Kombination mit sys.dm_os_waiting_tasks können Sie wartende Aufgaben und die von diesen benötigten Ressourcen anzeigen. Diese Ansichten sind entscheidend, wenn Sie Blockierungen und Ressourcenkonflikte in einer späteren Einheit diagnostizieren.

Alles zusammenfügen

Ausführungspläne und DMVs geben Ihnen ein vollständiges Bild des Abfrageverhaltens. Beginnen Sie mit DMVs, um die teuersten Abfragen zu identifizieren. Führen Sie dann einen Drilldown in ihre Ausführungspläne durch, um zu verstehen , warum sie teuer sind. Ist es ein fehlender Index, der einen Scan verursacht? Veraltete Statistiken verursachen Fehler bei Zeilenschätzungen. Eine Schlüsselsuche, die Sie eliminieren können? Dieser systematische Ansatz von systemweiter Sicht bis hin zur individuellen Abfrageanalyse ist die effizienteste Möglichkeit, Leistungsengpässe zu finden und zu beheben.

Wichtige Erkenntnisse

Ausführungspläne zeigen die Strategie des Optimierers für eine Abfrage an, und tatsächliche Pläne umfassen Laufzeitmetriken, die Diskrepanzen zwischen geschätzten und tatsächlichen Zeilenanzahlen verfügbar machen. Konzentrieren Sie sich beim Lesen eines Plans auf Operatortypen (seek vs. scan), Zeilenanzahlschätzungen, Warnungen und Schlüsselsuchoperatoren. DMVs stellen systemweite Leistungsdaten bereit: Verwenden Sie sys.dm_exec_query_stats, um die teuersten Abfragen zu ermitteln, sys.dm_exec_requests für derzeit ausgeführte Abfragen und die DMVs zu fehlenden Indizes, um Optimierungsmöglichkeiten zu erkennen. Starten Sie die Übertragung mit DMVs, um zu ermitteln, wo die größten Probleme liegen. Schlüsseln Sie dann die einzelnen Ausführungspläne auf, um die Gründe in Erfahrung zu bringen.