SQL Server Parameter Sniffing: Erst diagnostizieren, dann beheben

Eine gespeicherte Prozedur läuft für einen Kunden in 80 Millisekunden und für einen anderen in 40 Sekunden. Der Code ist unverändert, der Server nicht überlastet und manchmal verschwindet das Problem nach einer erneuten Ausführung. Das ist ein typisches Zeichen für einen parameterabhängigen Plan, häufig Parameter Sniffing genannt.

Parameter Sniffing ist nicht automatisch ein Fehler. Wenn SQL Server eine parametrisierte Abfrage kompiliert, verwendet er die aktuellen Parameterwerte, um Zeilenzahlen zu schätzen und einen Plan auszuwählen. Die Wiederverwendung dieses Plans spart Kompilierungsaufwand und ist meistens sinnvoll. Probleme entstehen, wenn ein Plan sehr unterschiedliche Datenverteilungen bedienen muss.

Warum ein Plan scheitern kann

Angenommen, die meisten Kunden besitzen weniger als 100 Bestellungen, ein Marktplatzkunde aber 8 Millionen. Ein für einen kleinen Kunden kompilierter Plan kann Index Seeks und Nested Loops wählen. Für den großen Kunden wird derselbe Plan extrem langsam. Ein für den großen Kunden kompilierter Plan kann dagegen scannen und hashen und damit für alle anderen unnötige Arbeit verursachen.

Ungleich verteilte Daten, optionale Filter, veraltete Statistiken und korrelierte Spalten erhöhen dieses Risiko.

Verhalten sicher reproduzieren

Beginnen Sie mit einer repräsentativen Prozedur:

CREATE OR ALTER PROCEDURE dbo.GetOrdersByCustomer
    @CustomerId int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT OrderId, OrderDate, TotalAmount
    FROM dbo.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderDate DESC;
END;

Testen Sie in einer Nichtproduktionsumgebung einen selektiven und einen nicht selektiven Wert. Erfassen Sie für beide den tatsächlichen Ausführungsplan und die Laufzeitstatistiken. Hängt die Leistung davon ab, welcher Wert den zwischengespeicherten Plan kompiliert hat, ist das ein starker Nachweis.

Leeren Sie nicht den gesamten Produktions-Plan-Cache. Das erzeugt eine Kompilierungsspitze und trifft unabhängige Workloads.

Praktischer Diagnoseablauf

1. Suchen Sie im Query Store nach großen Schwankungen bei Dauer, CPU oder logischen Lesevorgängen.
2. Vergleichen Sie tatsächliche und geschätzte Zeilenzahlen am ersten wichtigen Join oder Lookup.
3. Notieren Sie Parameterwerte bei Kompilierung und Ausführung.
4. Prüfen Sie Histogramm und Aktualisierungszeitpunkt der relevanten Statistiken.
5. Schließen Sie implizite Konvertierungen, nicht sargable Prädikate und fehlende Indizes aus.
6. Bestätigen Sie, dass schnelle und langsame Ausführungen dieselbe Abfrage und Planidentität verwenden.

Die entscheidende Frage lautet nicht: "Ist der Plan schlecht?" Sondern: "Ist er für eine wichtige Datenform gut und für eine andere schlecht?"

Die kleinste verlässliche Lösung wählen

Beginnen Sie mit den Grundlagen. Aktualisieren Sie ungenaue Statistiken und erstellen Sie einen Index nur, wenn das Zugriffsmuster ihn rechtfertigt. Haben Abfragen wirklich unterschiedliche Formen, ist ihre Trennung oft klarer als ein erzwungener Kompromissplan.

Ab SQL Server 2022 kann Parameter Sensitive Plan Optimization für geeignete Gleichheitsprädikate mehrere Planvarianten speichern. Prüfen Sie Kompatibilitätsgrad und Abfrageform und kontrollieren Sie die Varianten im Query Store.

Ein Query-Store-Hinweis oder erzwungener Plan kann kurzfristig stabilisieren. Überwachen Sie ihn, denn geänderte Daten können einen früher guten Plan verschlechtern.

Verwenden Sie OPTION (RECOMPILE), wenn die Kompilierung günstig ist und jede Ausführung einen passenden Plan benötigt. Begrenzen Sie die Option auf die kleinste mögliche Anweisung. OPTIMIZE FOR passt nur bei einem stabilen repräsentativen Wert oder einem bewusst gewünschten Durchschnittsplan. Dynamisches SQL mit sp_executesql eignet sich für optionale Suchfilter, weil unterschiedliche Abfrageformen getrennte Pläne erhalten und trotzdem parametrisiert bleiben.

Häufige Fehler vermeiden

• Parameter Sniffing nicht global wegen einer Abfrage deaktivieren.
• Empfohlene Indizes nicht ohne Messung von Schreib- und Speicherkosten übernehmen.
• Einen Plan nicht ohne Verantwortlichen, Überwachung und Entfernungskriterium erzwingen.
• Nicht jede sporadische Verlangsamung als Parameter Sniffing einstufen.

Produktionscheckliste

Erfassen Sie eine Baseline, sichern Sie den ursprünglichen Plan, testen Sie repräsentative Parametergruppen, messen Sie CPU und logische Lesevorgänge, stellen Sie die engste Änderung bereit und beobachten Sie danach den Query Store. Definieren Sie vorher einen Rollback.

Parameter Sniffing ist am besten als Datenverteilungsproblem zu behandeln, nicht als mysteriöser Cachefehler. Belegen Sie die konkurrierenden Datenformen und wählen Sie eine dazu passende Lösung. Für eine zweite Meinung können Sie das Frageformular unten verwenden und die Prozedur, repräsentative Parameter sowie einen anonymisierten Ausführungsplan mitsenden.