Parameter Sniffing erkennen und beheben

1. Was Parameter Sniffing ist und warum es überrascht

Parameter Sniffing beschreibt das Verhalten eines Query-Planers, den Ausführungsplan für eine parametrisierte Abfrage anhand der bei der ersten Kompilierung übergebenen Parameterwerte zu erstellen und diesen Plan anschließend für alle weiteren Aufrufe mit anderen Werten wiederzuverwenden. Der Name kommt daher, dass der Optimizer die tatsächlichen Werte beim ersten Aufruf "erschnüffelt" und seine Kostenschätzung darauf aufbaut, statt für jeden Aufruf neu zu planen. Das ist grundsätzlich ein sinnvolles Verhalten, weil es wiederholtes Kompilieren derselben Query-Struktur vermeidet und dadurch CPU-Zeit spart.

Problematisch wird Parameter Sniffing, wenn die Datenverteilung stark ungleichmäßig ist und der erste Aufruf einen untypischen Wert liefert. Ein Plan, der für eine sehr seltene Kategorie mit zehn Zeilen optimiert wurde, nutzt vielleicht einen Index Seek, derselbe Plan wird aber katastrophal langsam, wenn er auf eine sehr häufige Kategorie mit einer Million Zeilen angewendet wird, weil ein Index Seek mit anschließendem Key Lookup pro Zeile bei einer Million Treffern um Größenordnungen teurer ist als ein einfacher Table Scan. Das überrascht Entwickler regelmäßig, weil die Abfrage selbst unverändert bleibt, nur die übergebenen Werte unterscheiden sich.

2. Symptome: gleiche Query, drastisch unterschiedliche Laufzeit

Das auffälligste Symptom von Parameter Sniffing ist eine Abfrage, die in neunzig Prozent der Fälle in wenigen Millisekunden läuft, aber gelegentlich, scheinbar zufällig, mehrere Sekunden oder sogar Minuten benötigt, obwohl sich weder die Abfrage noch das Datenvolumen insgesamt verändert haben. Anders als bei klassischer Query-Optimierung, bei der eine Abfrage konstant langsam ist, zeigt sich Parameter Sniffing als intermittierendes Problem, das sich schwer reproduzieren lässt, weil es von den konkreten Parameterwerten des jeweiligen Aufrufs abhängt.

Ein zweites Symptom tritt häufig nach einem Neustart des Datenbankservers, einem Plan-Cache-Flush oder einem Failover auf: Direkt danach läuft eine bisher unauffällige Abfrage plötzlich dauerhaft langsam, weil der erste Aufruf nach dem Neustart zufällig einen untypischen Parameterwert enthielt und der daraus resultierende Plan nun für alle folgenden Aufrufe gilt, bis der Plan aus einem anderen Grund neu kompiliert wird. Dieses Muster, korreliert mit einem Neustart-Zeitpunkt, ist ein starker Hinweis auf Parameter Sniffing als Ursache.

3. Wie der Optimizer zu einem schlechten Plan kommt

Der Mechanismus hinter Parameter Sniffing liegt in der Kostenschätzung des Optimizers. Bei der ersten Kompilierung einer parametrisierten Abfrage schaut der Optimizer in die Statistiken der betroffenen Spalten und schätzt anhand des konkret übergebenen Parameterwerts, wie viele Zeilen die Abfrage voraussichtlich zurückliefert. Diese Schätzung bestimmt die gewählte Join-Strategie, die Wahl zwischen Index Seek und Table Scan sowie die Reihenfolge von Joins bei komplexeren Abfragen. Der resultierende Plan wird im Plan Cache gespeichert und bei identischer Query-Struktur, unabhängig von den tatsächlichen Parameterwerten, wiederverwendet.

Das Kernproblem ist, dass diese Kostenschätzung nur für die spezifische Datenverteilung des ersten Parameterwerts korrekt ist. Bei gleichmäßig verteilten Daten spielt das kaum eine Rolle, weil jeder Parameterwert zu einer ähnlichen Zeilenzahl führt und der gecachte Plan für alle Werte annähernd optimal bleibt. Bei stark ungleichmäßig verteilten Daten, etwa einem Status-Feld mit neunzig Prozent "abgeschlossen" und zehn Prozent "in Bearbeitung", kann derselbe Plan für den einen Wert brillant und für den anderen Wert katastrophal sein, weil die optimale Strategie fundamental von der erwarteten Zeilenzahl abhängt.

-- Highly skewed distribution: 2 rows for one status, 2 million for another
-- SELECT COUNT(*) FROM orders GROUP BY status;
--   status='cancelled'  -> 2 rows
--   status='completed'  -> 2,000,000 rows

CREATE PROCEDURE GetOrdersByStatus @Status VARCHAR(20)
AS
BEGIN
    SELECT * FROM orders WHERE status = @Status;
END;

-- First call with the RARE value compiles a plan optimized for 2 rows
EXEC GetOrdersByStatus @Status = 'cancelled';   -- compiles: Index Seek plan

-- Same cached plan gets reused for the COMMON value -> catastrophic
EXEC GetOrdersByStatus @Status = 'completed';   -- reuses Index Seek plan
-- -> 2,000,000 key lookups instead of a single table scan

4. Diagnose: Plan Cache und Ausführungsstatistiken vergleichen

Die zuverlässigste Diagnose für Parameter Sniffing beginnt mit dem Vergleich zwischen dem im Plan Cache gespeicherten geschätzten Ausführungsplan und einem frisch kompilierten Plan für den gerade problematischen Parameterwert. In SQL Server liefert sys.dm_exec_query_stats zusammen mit sys.dm_exec_query_plan den aktuell gecachten Plan samt der Parameterwerte, mit denen er ursprünglich kompiliert wurde, sichtbar über die Eigenschaft ParameterCompiledValue im XML-Plan.

In PostgreSQL äußert sich dasselbe Grundproblem anders, weil PostgreSQL bei einfachen Abfragen standardmäßig für jeden Aufruf neu plant, aber bei vorbereiteten Anweisungen (Prepared Statements) nach der fünften Ausführung auf einen generischen, parameterunabhängigen Plan umschaltet, sofern dieser nicht signifikant teurer geschätzt wird als die individuellen Pläne. Die Diagnose erfolgt hier über EXPLAIN ANALYZE mit den tatsächlich problematischen Parameterwerten im Vergleich zu PREPARE und EXPLAIN EXECUTE, um den generischen gegen den spezifischen Plan zu stellen.

-- SQL Server: inspect the cached plan and the parameter values it was compiled for
SELECT
    qs.execution_count,
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qs.plan_handle IN (
    SELECT plan_handle FROM sys.dm_exec_cached_plans
    WHERE cacheobjtype = 'Compiled Plan'
);
-- Look for "ParameterCompiledValue" in the plan XML to see the sniffed value

-- PostgreSQL: compare the generic plan against a plan for the specific value
PREPARE order_lookup (varchar) AS SELECT * FROM orders WHERE status = $1;
EXPLAIN ANALYZE EXECUTE order_lookup('completed');
-- vs. a plain, non-prepared query for the same value:
EXPLAIN ANALYZE SELECT * FROM orders WHERE status = 'completed';

5. Lösung: OPTIMIZE FOR, Query Hints und Plan Guides

Die direkteste Gegenmaßnahme in SQL Server ist der Hint OPTION (OPTIMIZE FOR (@Status = 'completed')), der den Optimizer anweist, den Plan immer für einen bestimmten, repräsentativen Wert zu kompilieren, unabhängig vom tatsächlich beim ersten Aufruf übergebenen Wert. Alternativ erzwingt OPTION (RECOMPILE) eine Neukompilierung bei jedem Aufruf, was Parameter Sniffing vollständig eliminiert, aber CPU-Overhead für die wiederholte Kompilierung erzeugt, was bei sehr häufig aufgerufenen, einfachen Abfragen unwirtschaftlich sein kann.

Eine dritte, granularere Option ist OPTION (OPTIMIZE FOR UNKNOWN), die den Optimizer anweist, die durchschnittliche Selektivität über alle Werte hinweg zu nutzen, statt sich auf einen konkreten Wert zu spezialisieren. Diese Variante liefert einen Kompromissplan, der für keinen einzelnen Wert optimal, aber für die Gesamtheit der Aufrufe robust ist, besonders geeignet, wenn keine einzelne Kategorie klar dominiert. PostgreSQL bietet mit plan_cache_mode = force_custom_plan pro Session eine ähnliche Möglichkeit, immer individuell zu planen statt auf einen generischen Plan umzuschalten.

-- SQL Server: force the plan to always assume the common value's distribution
SELECT * FROM orders WHERE status = @Status
OPTION (OPTIMIZE FOR (@Status = 'completed'));

-- Force recompilation on every execution (removes sniffing, adds CPU overhead)
SELECT * FROM orders WHERE status = @Status
OPTION (RECOMPILE);

-- Use average selectivity instead of the sniffed value
SELECT * FROM orders WHERE status = @Status
OPTION (OPTIMIZE FOR UNKNOWN);

-- PostgreSQL: force per-call custom planning instead of a generic plan
SET plan_cache_mode = force_custom_plan;

6. Lösung: Statistiken und Histogramme verbessern

Ein oft übersehener, aber nachhaltiger Ansatz gegen Parameter Sniffing ist die Verbesserung der zugrunde liegenden Statistiken selbst. Standard-Statistiken fassen die Datenverteilung in einer begrenzten Anzahl von Histogramm-Buckets zusammen, was bei stark ungleichmäßig verteilten Daten zu ungenauen Schätzungen führt. Eine feinere Auflösung, in SQL Server über CREATE STATISTICS mit erhöhter Sample-Rate oder FULLSCAN, verbessert die Genauigkeit der Kostenschätzung für jeden einzelnen Parameterwert und reduziert damit die Wahrscheinlichkeit extrem schlechter Pläne.

In PostgreSQL steuert die Spalteneinstellung ALTER TABLE tabelle ALTER SPALTE SET STATISTICS 500 die Anzahl der für diese Spalte gesammelten Histogramm-Buckets, mit einem Maximum von zehntausend gegenüber dem Standardwert von hundert. Bei Spalten mit bekannt starker Schiefe in der Verteilung, wie einem Status-Feld mit wenigen dominanten Werten, lohnt sich diese Erhöhung gezielt, weil sie die Kostenschätzung des Optimizers präzisiert, ohne die Abfrage selbst oder den Anwendungscode ändern zu müssen.

7. Lösung: Query Rewriting und lokale Variablen

Eine strukturelle Lösung gegen Parameter Sniffing ist, die Abfrage so umzuschreiben, dass sie für stark unterschiedliche Fälle unterschiedliche, spezialisierte Ausführungspfade nutzt. Statt einer einzigen parametrisierten Abfrage, die alle möglichen Statuswerte abdeckt, kann eine bedingte Verzweigung im Anwendungscode oder in einer gespeicherten Prozedur für bekannte Ausreißer-Werte einen separaten, dedizierten Pfad mit eigenem, unabhängig kompiliertem Plan bereitstellen.

Ein verwandter, in SQL Server verbreiteter Trick ist die Zuweisung des Parameters an eine lokale Variable innerhalb der gespeicherten Prozedur, bevor er in der WHERE-Klausel verwendet wird. Da der Optimizer für lokale Variablen keinen konkreten Wert kennt, fällt er auf die durchschnittliche Selektivität zurück, ähnlich wie bei OPTIMIZE FOR UNKNOWN. Dieser Trick ist inoffiziell und sollte bewusst und dokumentiert eingesetzt werden, weil er sich wie ein Nebeneffekt liest, wenn ein anderer Entwickler den Code später liest, ohne den Hintergrund zu kennen.

8. Wann Parameter Sniffing sogar hilft

Es lohnt sich zu betonen, dass Parameter Sniffing kein grundsätzlicher Designfehler ist, sondern in der überwiegenden Mehrheit der Fälle tatsächlich Performance verbessert. Bei gleichmäßig verteilten Daten oder Abfragen, bei denen alle möglichen Parameterwerte zu ähnlichen Zeilenzahlen führen, liefert der gecachte, parameterspezifische Plan durchgehend gute Ergebnisse und spart gleichzeitig die CPU-Kosten wiederholter Kompilierung. Das Problem entsteht ausschließlich bei stark schiefen Datenverteilungen kombiniert mit Abfragen, deren optimale Strategie stark von der erwarteten Zeilenzahl abhängt.

Die praktische Konsequenz: OPTION (RECOMPILE) pauschal auf alle Abfragen anzuwenden, um Parameter Sniffing zu vermeiden, ist in den meisten Fällen die falsche Lösung, weil es den eigentlichen Vorteil des Plan Cachings, reduzierte Kompilierungskosten, für die überwiegende Mehrheit der unproblematischen Abfragen zunichtemacht. Die gezielte Anwendung der in den Abschnitten fünf bis sieben gezeigten Techniken nur auf die konkret betroffenen Abfragen ist der wirtschaftlichere Ansatz.

9. Gegenmaßnahmen im Vergleich

Die folgende Tabelle vergleicht die gängigen Gegenmaßnahmen nach CPU-Overhead und Eignung für unterschiedliche Szenarien.

Für die meisten produktiven Systeme ist die Kombination aus verbesserten Statistiken als nachhaltiger Basisschutz und gezielten OPTIMIZE FOR-Hints für einzelne, bekannt problematische Abfragen der wirtschaftlichste Weg, ohne pauschal auf teures Recompiling zurückzugreifen.

10. Zusammenfassung

Parameter Sniffing entsteht, weil der Optimizer einen Ausführungsplan anhand des ersten Parameterwerts kompiliert und diesen Plan für alle folgenden Aufrufe wiederverwendet, unabhängig davon, ob deren Datenverteilung ähnlich aussieht. Bei stark ungleichmäßig verteilten Daten führt das zu intermittierend katastrophal langsamen Abfragen, während dieselbe Abfrage für andere Parameterwerte unauffällig bleibt. Die Diagnose gelingt über den Vergleich zwischen dem gecachten Plan und einem frisch kompilierten Plan für den problematischen Wert.

Die wirksamsten Gegenmaßnahmen reichen von gezielten Query Hints wie OPTIMIZE FOR über verbesserte Statistiken bis hin zu strukturellem Query Rewriting für bekannte Ausreißer. Pauschales Recompiling bei jedem Aufruf vermeidet Parameter Sniffing zwar vollständig, verursacht aber unnötigen CPU-Overhead für die überwiegende Mehrheit der unproblematischen Abfragen und sollte deshalb gezielt statt pauschal eingesetzt werden.

11. FAQ: Parameter Sniffing