Tatsächliche SQL-Server-Pläne mit Messwerten verstehen

Ein Ausführungsplan scheint eine einfache Diagnose zu erlauben: den größten Prozentwert suchen und den zugehörigen Operator entfernen. Diese Prozentwerte sind jedoch geschätzte Optimierungskosten und keine gemessenen Laufzeitanteile. Eine belastbare Untersuchung verbindet die konkrete Anfrage, den Datenfluss und den tatsächlich beobachteten Ressourcenverbrauch.

Die ausgeführte Anfrage festhalten

Dokumentieren Sie SQL-Text, Parameterwerte, Parametertypen, Kompatibilitätsgrad und relevante Sitzungseinstellungen. Ein in ein anderes Fenster kopierter Text mit anderen Werten ist ein anderes Experiment. Aktivieren Sie den tatsächlichen Plan in SSMS oder verwenden Sie STATISTICS XML. Dabei wird die Anweisung ausgeführt. Bei schreibenden Batches ersetzt diese Funktion daher keine vorsichtige Prüfung der Auswirkungen.

Das Beispiel vergleicht dieselbe Aggregation vor und nach dem Anlegen eines Index. Aktivieren Sie den tatsächlichen Plan und verwenden Sie eine Testsitzung.

CREATE TABLE #PlanOrders
(
    OrderId int NOT NULL PRIMARY KEY,
    CustomerId int NOT NULL,
    Amount decimal(12,2) NOT NULL
);
;WITH N AS
(
    SELECT TOP (10000)
        CONVERT(int, ROW_NUMBER() OVER (ORDER BY (SELECT NULL))) AS n
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
)
INSERT #PlanOrders (OrderId, CustomerId, Amount)
SELECT n, n % 100, CONVERT(decimal(12,2), n % 250)
FROM N;

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
SELECT SUM(Amount) AS Total
FROM #PlanOrders WHERE CustomerId = 42;

CREATE INDEX IX_PlanOrders_Customer
ON #PlanOrders (CustomerId) INCLUDE (Amount);

SELECT SUM(Amount) AS Total
FROM #PlanOrders WHERE CustomerId = 42;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
DROP TABLE #PlanOrders;

Beide SELECT-Anweisungen müssen denselben Gesamtwert liefern. Vergleichen Sie die logischen Zugriffe in der Meldungsausgabe und untersuchen Sie die Zugriffsoperatoren. Der zweite SELECT hat einen abdeckenden Zugriffspfad für CustomerId und Amount zur Verfügung. Dokumentieren Sie trotzdem die tatsächliche Auswahl des Optimierers, statt ein bestimmtes Plansymbol vorauszusetzen.

Die kleine Testtabelle hält den Versuch überschaubar. In der Produktion beeinflussen Selektivität und Zeilenbreite dieselbe Entscheidung. Trennen Sie außerdem Aufbau und Abfrage: Der Indexaufbau erscheint ebenfalls im Batch, sein Plan beschreibt jedoch nicht den SELECT. Speichern Sie beide Abfragepläne samt Messwerten. Leeren Sie für diesen Vergleich keinen gemeinsam genutzten Plan- oder Datencache.

Die erste wesentliche Abweichung suchen

Verfolgen Sie den Datenfluss von den Tabellenzugriffen zum Ergebnis. Vergleichen Sie geschätzte und tatsächliche Zeilenzahlen an wichtigen Übergängen. Werden nach einem Filter statt geschätzter 100 Zeilen tatsächlich 200.000 weitergereicht, kann ein später teurer Join nur die Folge dieser frühen Fehleinschätzung sein. Eine Änderung ausschließlich am abschließenden Sortieren beseitigt die Ursache dann nicht.

Unterscheiden Sie gelesene von zurückgegebenen Zeilen. Ein Seek kann einen großen Bereich lesen und anschließend fast alles durch ein Restprädikat verwerfen. Das Symbol allein beweist keine geringe Arbeit. Öffnen Sie die Eigenschaften und vergleichen Sie Suchbedingungen, Restprädikate und beide Zeilenmengen. Ein Scan einer kleinen Tabelle kann dagegen günstiger sein als sehr viele einzelne Nachschlagevorgänge.

Bei Nested Loops zählt auch die Anzahl der Ausführungen. Ein scheinbar billiger innerer Zugriff kann bei 100.000 Wiederholungen den Gesamtaufwand bestimmen. Prüfen Sie, ob eine angezeigte Schätzung pro Ausführung gilt oder bereits zusammengefasst ist. Bei parallelen Plänen müssen zudem die Angaben über Threads sinnvoll verglichen werden.

Lookup, Sort, Spool und Hash Join sind keine Fehlerkategorien. Fragen Sie, welche Aufgabe der Operator erfüllt und wie viel Arbeit dabei entsteht. Ein zusätzlicher Index kann Lookups vermeiden, kostet aber Speicher und Schreibarbeit. Ein Spool kann Wiederholungen sparen, und eine Sortierung kann eine notwendige fachliche Reihenfolge herstellen.

Eine überprüfbare Änderung ableiten

Formulieren Sie eine konkrete Hypothese: Ein Restprädikat verwirft zu viele Zeilen, ein schiefer Datenbereich wird falsch geschätzt oder unnötige Ausgabespalten vergrößern eine Sortierung. Ändern Sie eine passende Sache und vergleichen Sie repräsentative Parameterfälle. Prüfen Sie Ergebnisgleichheit, logische Zugriffe, CPU, Laufzeit und Auswirkungen unter gleichzeitiger Last.

Laufzeitwarnungen sind Hinweise, keine vollständige Diagnose. Ein Spill kann im Lastbetrieb wichtig sein und bei einer kleinen Einzelabfrage kaum ins Gewicht fallen. Auch ein Vorschlag für einen fehlenden Index berücksichtigt nicht automatisch alle vorhandenen Indizes und Schreibanforderungen Ihrer Anwendung.

Vergleichen Sie zum Abschluss die Serverarbeit mit der vom Benutzer beobachteten Wartezeit. Wenig CPU und wenige Zugriffe schließen Blockierung, Warten auf Arbeitsspeicher oder einen langsam lesenden Client nicht aus. Bewahren Sie Ausgangsplan, Parameter und Begründung gemeinsam auf. Damit lässt sich später prüfen, ob dieselbe Ursache wieder aufgetreten ist oder ein neuer Engpass vorliegt.

Technische Referenzen: Microsoft Learn: Actual execution plans · Microsoft Learn: Showplan operators · Microsoft Learn: STATISTICS IO.