Eine Abfrage, viele Pläne.
Dieselbe Prozedur läuft mal in Millisekunden und mal quälend lange? Oft liegt das nicht an der Hardware. Es liegt daran, welchen Ausführungsplan der Optimizer gewählt hat, und welcher gerade im Cache liegt. Probieren Sie es aus: mit echten Plänen aus unserem Testlab.
Erst schätzen, dann ausführen.
Der Optimizer entscheidet über den Plan, bevor die Abfrage läuft. In diesem Moment hat er nur Schätzungen: wie viele Zeilen jeder Schritt liefern wird und was die möglichen Wege kosten. Was die Abfrage tatsächlich braucht, zeigt sich erst bei der Ausführung.
- Statistiken und Parameterwertwas SQL Server über die Daten weiß
- Geschätzte Zeilenfür jeden Schritt des Plans
- Geschätzte Kostenfür die möglichen Wege
- Gewählter Plander günstigste gefundene Weg
- Aktueller Parameterwertoft ein ganz anderer Kunde
- Zeilen, Reads, CPU, Dauererst jetzt tatsächlich bekannt
Experiment 1 zeigt den linken Teil: Für unterschiedliche Kunden schätzt der Optimizer unterschiedliche Mengen und wählt ab einer bestimmten Menge einen anderen Plan.
Experiment 2 zeigt den rechten Teil: Ein Plan aus dem Cache läuft auch für Kunden, für die er nie gewählt wurde.
Ab einer bestimmten Menge wählt der Optimizer einen anderen Weg.
In unserem Testlab liegen 1.510.967 Bestellungen mit 4.535.871 Positionen von 50.000 Kunden. Die Verteilung ist bewusst ungleich, wie im echten Leben: Manche Kunden haben eine einzige Bestellung, einer hat 500.000. Die Prozedur rechnet den Umsatz je Bestellung eines Kunden aus. Es gibt einen Index auf der Kundennummer.
SELECT o.OrderID, o.OrderDate, SUM(l.Amount) AS Umsatz
FROM dbo.Orders o
JOIN dbo.OrderLines l ON l.OrderID = o.OrderID
WHERE o.CustomerID = @CustomerID
GROUP BY o.OrderID, o.OrderDate;Für Interessierte: Tabellen, Indizes und Datenverteilung
CREATE TABLE dbo.Customers (
CustomerID int NOT NULL PRIMARY KEY,
Name nvarchar(100) NOT NULL,
Region char(2) NOT NULL);
CREATE TABLE dbo.Orders (
OrderID int IDENTITY NOT NULL PRIMARY KEY CLUSTERED,
CustomerID int NOT NULL,
OrderDate date NOT NULL,
Status tinyint NOT NULL,
Total decimal(12,2) NOT NULL,
Filler char(200) NOT NULL DEFAULT 'x');
CREATE TABLE dbo.OrderLines (
OrderID int NOT NULL,
[LineNo] tinyint NOT NULL,
ProductID int NOT NULL,
Qty int NOT NULL,
Amount decimal(12,2) NOT NULL,
PRIMARY KEY CLUSTERED (OrderID, [LineNo]));
CREATE INDEX IX_Orders_CustomerID ON dbo.Orders (CustomerID);- 50.000 Kunden
- 1.510.967 Bestellungen, davon sieben Stufenkunden mit 1, 10, 100, 1.000, 10.000, 100.000, 500.000 Bestellungen; alle anderen haben 1 bis etwa 40
- 4.535.871 Positionen, 1 bis 5 je Bestellung
- Statistiken mit
FULLSCANaktualisiert; der Index aufCustomerIDdeckt die Abfrage bewusst nicht ab, deshalb der Key Lookup
Schieben Sie den Regler. Jede Stufe zeigt den Plan, den SQL Server für genau diesen Kunden kompiliert.
Ein Plan lässt sich in beide Richtungen lesen. Von rechts nach links folgen Sie den Daten: Rechts werden sie gelesen, links steht das Ergebnis. Von links nach rechts folgen Sie der Ausführung: Jeder Operator fordert seine Zeilen beim rechten Nachbarn an. Die Pfeilstärke zeigt, wie viele Zeilen fließen.
- Geschätzte Zeilen
- –
- Logische Reads
- –
- CPU
- –
- Dauer
- –
Und dann bleibt ein Plan im Cache.
SQL Server kompiliert eine Prozedur beim ersten Aufruf mit dem Wert, der gerade übergeben wird. Das heißt Parameter Sniffing. Alle weiteren Aufrufe verwenden diesen Plan wieder, solange er im Cache liegt: Plan Reuse. Der erste Kunde prägt so die Strategie für alle, die nach ihm kommen.
Warum der Optimizer so arbeitet
Der Optimizer von SQL Server ist kostenbasiert. Er schätzt, wie viele Zeilen jeder Schritt liefert, bewertet die Planalternativen nach geschätzten Kosten und nimmt die günstigste, die er in vertretbarer Zeit findet. Grundlage der Schätzung sind Statistiken über die Verteilung der Daten und, bei Parametern, der Wert beim Kompilieren.
Für wenige Zeilen ist der gezielte Zugriff über einen Index meist am günstigsten. Für sehr viele Zeilen ist es günstiger, die Tabelle einmal ganz zu lesen und per Hash zu verknüpfen. Keiner der beiden Pläne ist falsch. Teuer wird es erst, wenn ein Plan für Datenmengen wiederverwendet wird, für die er nicht gewählt wurde.
Eine Beobachtung am Rande: Beim Kunden mit 10.000 Bestellungen wählte der Optimizer einen parallelen Plan. Er war in unserer Messung nicht schneller als der wiederverwendete Plan des kleinsten Kunden (288 ms gegenüber 242 ms), brauchte aber ein Vielfaches an CPU (1,1 s gegenüber 242 ms). Der Optimizer vergleicht geschätzte Kosten, keine gemessenen Laufzeiten. Auch ein für den Wert kompilierter Plan ist nur der mit den niedrigsten Schätzkosten, nicht garantiert der schnellste.
Was dagegen hilft, und was es kostet
| Ansatz | Wirkung | Preis |
|---|---|---|
OPTION (RECOMPILE) | neue Planwahl mit dem aktuellen Wert bei jedem Aufruf | Kompilierung bei jedem Aufruf; bei häufig ausgeführten Abfragen kostet das erheblich CPU, dafür muss Budget da sein |
OPTIMIZE FOR | ein bewusst gewählter Plan für alle | Kompromiss; passt für einen Teil der Werte schlechter |
| Dynamisches SQL je Abfrageform | eigene, wiederverwendbare Pläne je Form | Pflegeaufwand; mit unserem Open-Source-Projekt T-Lift deutlich geringer |
| Parameter Sensitive Plan Optimization | ab SQL Server 2022 eigene Planvarianten für unterschiedliche Wertebereiche | greift nur unter bestimmten Bedingungen, siehe unten |
| Query Store: Plan erzwingen oder Hint setzen | gezielte Korrektur ohne Codeänderung | muss beobachtet werden, wenn sich Daten oder Version ändern |
Und die Parameter Sensitive Plan Optimization?
Seit SQL Server 2022 (Kompatibilitätsgrad 160) kann SQL Server für eine Abfrage mit ungleich verteilten Parameterwerten mehrere Planvarianten für unterschiedliche Wertebereiche vorhalten. Das zielt genau auf das Problem aus Experiment 2.
Mit eingeschalteter PSP-Optimierung legte unser Testlab für die Prozedur einen Verteilerplan und 3 Varianten an:
- Variante 1 für Kunden mit einer Bestellung: Nested Loops, seriell
- Variante 2 für Kunden mit 10 bis 10.000 Bestellungen: Nested Loops, seriell
- Variante 3 für Kunden mit 100.000 bis 500.000 Bestellungen: Hash Match, parallel
Das entschärft Experiment 2 deutlich, aber nicht vollständig: Kunden mit 10 und mit 10.000 Bestellungen teilen sich eine Variante. Welche Variante greift, entscheidet die Schätzung, nicht die tatsächliche Zeilenzahl. Die Kunden mit 10, 100 und 1.000 Bestellungen schätzt SQL Server hier alle auf 370 Zeilen, weil sie im selben Schritt des Statistik-Histogramms liegen. Genau darum geht es in Kapitel 2.
Wie wir solche Fälle finden
Der Query Store von SQL Server hält fest, welche Pläne eine Abfrage im Lauf der Zeit hatte und wie teuer jeder war. PSG QX wertet genau diese Historie aus: welche Abfrage mehrere Pläne hatte, welcher teurer war und seit wann. Ist der Query Store noch nicht aktiv, schalten wir ihn zu Beginn ein und lassen ihn sich füllen.
Alle Zahlen des Experiments
Logische Reads und Dauer je Kunde, mit dem für seinen Wert kompilierten Plan und mit einem wiederverwendeten Plan aus dem Cache. Die gestrichelte Linie markiert, wo der für den Wert kompilierte Plan wechselt.
| Bestellungen des Kunden | Für diesen Wert kompiliert | Mit eigenem Plan | Plan des kleinsten Kunden | Plan des größten Kunden |
|---|---|---|---|---|
| 1 | Plan A Nested Loops · Index Seek · Key Lookup · Clustered Index Seek | 9 < 1 ms | 9 < 1 ms | 54.094 1,8 s |
| 10 | Plan A Nested Loops · Index Seek · Key Lookup · Clustered Index Seek | 53 < 1 ms | 63 < 1 ms | 47.424 915 ms |
| 100 | Plan A Nested Loops · Index Seek · Key Lookup · Clustered Index Seek | 574 2 ms | 604 2 ms | 58.224 2,9 s |
| 1.000 | Plan A Nested Loops · Index Seek · Key Lookup · Clustered Index Seek | 5.879 43 ms | 6.021 41 ms | 51.677 6,6 s |
| 10.000 | Plan B Hash Match · Index Seek · Key Lookup · Clustered Index Scan · parallel | 46.475 288 ms | 60.165 242 ms | 46.570 794 ms |
| 100.000 | Plan C Hash Match · Clustered Index Scan · parallel | 61.686 626 ms | 601.649 1,4 s | 60.782 2,7 s |
| 500.000 | Plan D Merge Join · Clustered Index Scan | 60.782 4,0 s | 3.008.513 6,0 s | 60.782 3,7 s |
Optimizer verstehen: die Kapitel
Dieses Thema wächst. Kapitel, die noch in Arbeit sind, stehen hier bereits, damit Sie sehen, wohin es geht.
- 01Eine Abfrage, viele PläneDieses Kapitel
- 02Wie der Optimizer schätztKardinalität, Statistiken, Histogramme · in Arbeit
- 03Parameter Sniffing im DetailErkennen und die Gegenmittel im Vergleich · in Arbeit
- 04Plan Cache und WiederverwendungWarum ein Neustart scheinbar hilft · in Arbeit
- 05Kompatibilitätsgrad und SchätzermodellWas sich beim Upgrade ändert, siehe SQL-Server-Versionen · in Arbeit
- 06Neuere Optimizer-FunktionenIntelligent Query Processing ab SQL Server 2022 · in Arbeit
- 07Pläne sichtbar machenQuery Store und PSG QX · in Arbeit
Methode und Änderungsprotokoll
Gemessen am 29.09.2026 auf Microsoft SQL Server 2025 (RTM-CU7) (KB5096981) - 17.0.4065.4 (X64) in einem Container mit 8 CPUs und 6.144 MB Speicher, Kompatibilitätsgrad 170, MAXDOP 0. Warmer Cache, Dauer und CPU als Median aus zehn Läufen (zwei Runden mit je fünf Läufen, jeweils nach einem nicht gezählten Kompilierlauf); bei gleichem Plan streuen die Zeiten bis Faktor 2, Werte unter einer Millisekunde sind nur Größenordnungen. Die Dauer enthält das Senden der Ergebniszeilen an den Client. Für die Experimente 1 und 2 war die Parameter Sensitive Plan Optimization ausgeschaltet, damit das klassische Verhalten sichtbar wird.
Ihre Zahlen werden anders sein: andere Hardware, andere Daten. Der Mechanismus bleibt; wie stark er wirkt und wo der Plan wechselt, hängt von Ihren Daten ab. Die Skripte zum Nachstellen geben wir auf Anfrage gern weiter.
Änderungsprotokoll
- Modell vor Experiment 1 ergänzt (erst schätzen, dann ausführen); Begriffe präzisiert: „für jeden Kunden kompiliert“ statt „passender Plan“, Parameter Sniffing und Plan Reuse getrennt; Messmethode genauer beschrieben.
- Kapitel 1 angelegt: zwei Experimente mit echten Plänen aus SQL Server 2025 CU7, Messwerte, Einordnung der Gegenmittel und Ergebnis zur Parameter Sensitive Plan Optimization.