Parameter Sniffing & Plan Cache:

Wenn der erste Aufruf die Regeln macht — für alle anderen

Stell dir vor, ein Arzt untersucht seinen ersten Patienten des Tages — einen 25-jährigen Sportler mit Knieschmerzen nach dem Joggen. Der Arzt erstellt einen Behandlungsplan: Ibuprofen, Kühlung, drei Tage Ruhe. Dann kommt der zweite Patient: ein 78-jähriger mit Herzinsuffizienz und Ödemen in den Beinen. Der Arzt schaut kurz auf seinen Zettel und sagt: "Ibuprofen, Kühlung, drei Tage Ruhe." — Genau das ist Parameter Sniffing, wenn es schlecht läuft.

SQL Server optimiert Abfragepläne anhand der Parameter, die beim ersten Aufruf übergeben werden. Der daraus resultierende Plan wird gecacht und für alle nachfolgenden Aufrufe wiederverwendet — unabhängig davon, ob die neuen Parameter ähnliche oder völlig andere Ergebnismengen liefern. Meistens ist das eine gute Sache: Kompilierung kostet CPU, und wenn 90 % der Aufrufe ähnliche Parameter verwenden, spart ein gecachter Plan erhebliche Ressourcen. Aber, wenn die Datenverteilung schief ist — und sie ist in der Praxis fast immer schief — dann kann dieser gecachte Plan für bestimmte Parameter-Kombinationen eine Katastrophe sein.

In diesem Kapitel schauen wir uns an, wie Parameter Sniffing funktioniert, wie du es erkennst, bevor der Anruf um 2 Uhr nachts kommt, und welche Lösungsstrategien in welcher Situation tatsächlich helfen — und welche nur das Problem verstecken. Außerdem beleuchten wir den Plan Cache als Ganzes: was ihn aufbläht, warum zu viele Single-Use-Pläne deine CPU unter Dauerstress setzen, und wann DBCC FREEPROCCACHE eine legitime Waffe ist und wann es die Situation nur verschlimmert. Querverweise zu Kapitel 15 (Ausführungspläne lesen), Kapitel 16 (Plan Regression und Statistiken) und Kapitel 19 (Query Store) helfen dir, das große Bild zusammenzusetzen.

Was ist Parameter Sniffing — und warum macht SQL Server das?

Wenn SQL Server eine Stored Procedure oder eine parametrisierte Abfrage zum ersten Mal ausführt, durchläuft der Query Optimizer die vollständige Kompilierungsphase: Er liest die Statistiken, schätzt die Zeilenzahlen für jeden Operator, bewertet verschiedene Join-Strategien und wählt den Plan mit den geringsten geschätzten Kosten. Diese Kompilierung kostet CPU-Zeit — bei komplexen Abfragen mit vielen Joins und Subqueries können das durchaus 5 bis 50 Millisekunden sein. Multiply das mit tausend Aufrufen pro Minute, und du hast ein ernstes CPU-Problem, nur durch Kompilierung.

SQL Server löst das, indem er den fertigen Plan im Plan Cache speichert und bei nachfolgenden Aufrufen direkt wiederverwendet. Der entscheidende Punkt: Der Plan wird für die konkreten Parameter des ERSTEN Aufrufs optimiert. Wenn dieser erste Aufruf mit @RegionID = 99 kommt und Region 99 nur drei Kunden enthält, wählt der Optimizer einen Nested Loop Join — schnell und effizient für drei Zeilen. Dieser Plan wird gecacht.

Jetzt kommt @RegionID = 1 — Region 1 hat 500.000 Kunden. SQL Server schaut in den Cache, findet den Plan für die Prozedur, und führt ihn aus. Nested Loop mit 500.000 Zeilen. Für jeden der 500.000 Kunden ein einzelner Index-Lookup. Die CPU läuft auf 100 %, die Abfrage, die bei @RegionID = 99 in 2 ms fertig war, braucht jetzt 47 Sekunden. Die Nutzer in Region 1 rufen an. Der DBA guckt auf den Ausführungsplan und sieht… einen Nested Loop. "Warum nimmt der keinen Hash Join?" — Weil der Plan für drei Zeilen erstellt wurde, nicht für 500.000.

 

Definition: Parameter Sniffing
Parameter Sniffing bezeichnet das Verhalten des SQL Server Query Optimizers, bei der Kompilierung einer Stored Procedure oder parametrisierten Abfrage die tatsächlichen Parameterwerte des aktuellen Aufrufs zu "erschnüffeln" und den resultierenden Plan im Cache zu speichern.
Parameter Sniffing ist per se kein Bug — es ist ein Feature. Das Problem entsteht, wenn der gecachte Plan für eine atypische Parameterverteilung optimiert wurde und für andere, häufigere Parameter-Kombinationen suboptimal ist.

 

Das Tückische: Parameter Sniffing ist meistens völlig unsichtbar, weil es funktioniert. Eine Prozedur, die typischerweise mit ähnlichen Parametern aufgerufen wird, profitiert massiv vom gecachten Plan. Das Problem taucht erst auf, wenn jemand die Prozedur mit einem "Ausreißer"-Parameter aufruft — und zwar als Erster, sodass dieser Ausreißer-Plan den Cache vergiftet. In der Praxis passiert das oft nach einem Neustart des SQL Servers (Plan Cache wird geleert), bei dem die erste Ausführung zufällig von einem ungewöhnlichen Parameterwert stammt. Dann wundern sich alle, warum der Server nach dem Neustart langsamer ist als davor.

Parameter Sniffing erkennen — bevor du im Dunkeln tappst

Das klassische Symptom: Eine Stored Procedure ist manchmal blitzschnell und manchmal quälend langsam — und das mit denselben Parametern, je, nachdem wann man fragt. Das ist der erste Hinweis. Der zweite: In sys.dm_exec_query_stats divergieren min_worker_time und max_worker_time extrem. Wenn eine Prozedur mal 2 ms und mal 45.000 ms braucht, liegt Parameter Sniffing nahe.

Divergenz-Analyse in sys.dm_exec_query_stats

sys.dm_exec_query_stats speichert Laufzeitstatistiken pro gecachtem Plan. Die Felder min_worker_time und max_worker_time (in Mikrosekunden) verraten, ob eine Query konsistent läuft oder ob es extreme Ausreißer gibt. Eine große Differenz zwischen Minimum und Maximum ist ein starker Indikator für Parameter Sniffing.

-- Diagnose: Prozeduren mit starker Laufzeit-Divergenz

-- Zeigt Kandidaten für Parameter Sniffing an

SELECT TOP 20

    OBJECT_NAME(qs.object_id)           AS Prozedurname,

    qs.execution_count                  AS Aufrufe,

    qs.plan_generation_num              AS Plan_Neugenerierungen,

    -- Laufzeit in Millisekunden umrechnen (Originalwert in Mikrosekunden)

    qs.min_worker_time / 1000           AS Min_ms,

    qs.max_worker_time / 1000           AS Max_ms,

    qs.total_worker_time / 1000 / qs.execution_count AS Avg_ms,

    -- Verhältnis Max zu Min: Werte > 100 sind verdächtig

    CASE WHEN qs.min_worker_time > 0

         THEN qs.max_worker_time / qs.min_worker_time

         ELSE NULL

    END                                 AS Max_Min_Verhaeltnis,

    -- Estimated vs. Actual Rows aus dem gecachten Plan holen

    qp.query_plan

FROM sys.dm_exec_procedure_stats qs

CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

WHERE qs.database_id = DB_ID()

  AND qs.execution_count > 10  -- Nur Prozeduren mit ausreichend Aufrufen

  AND qs.min_worker_time > 0

ORDER BY

    -- Sortierung nach Verhältnis: extremste Ausreißer zuerst

    CASE WHEN qs.min_worker_time > 0

         THEN qs.max_worker_time / qs.min_worker_time

         ELSE 0

    END DESC;

Der Wert plan_generation_num ist besonders aufschlussreich: Wenn er größer als 1 ist, wurde der Plan für diese Prozedur bereits mehrmals neu generiert — ein Hinweis, dass SQL Server selbst erkannt hat, dass der ursprüngliche Plan nicht mehr passt. Das passiert zum Beispiel nach UPDATE STATISTICS oder, wenn die Statistiken automatisch aktualisiert wurden (Verweis auf Kapitel 16 für den Zusammenhang zwischen Statistiken und Plan Regression).

Den gecachten Plan extrahieren und mit dem Ist-Plan vergleichen

Der entscheidende Schritt bei der Diagnose ist der Vergleich zwischen dem gecachten (geschätzten) Plan und dem tatsächlich verwendeten Plan. In SQL Server Management Studio kannst du einen Plan mit "Include Actual Execution Plan" ausführen — dann siehst du bei jedem Operator "Estimated Number of Rows" und "Actual Number of Rows". Eine große Abweichung — sagen wir, Estimated = 3 und Actual = 847.000 — ist fast immer entweder Parameter Sniffing oder veraltete Statistiken (beides hängt zusammen, Kapitel 16 geht tiefer darauf ein).

-- Plan-Handle für eine bestimmte Prozedur aus dem Cache holen

SELECT

    cp.plan_handle,

    cp.usecounts        AS Wiederverwendungen,

    cp.size_in_bytes    AS Plan_Groesse_Bytes,

    -- Den eigentlichen XML-Plan für SSMS

    qp.query_plan

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) qp

WHERE st.objectid = OBJECT_ID('dbo.usp_GetBestellungen')

  AND cp.objtype = 'Proc';

 

-- Alternativ: Plan-Handle gezielt für einen einzelnen Plan löschen

-- (VIEL besser als DBCC FREEPROCCACHE ohne Parameter!)

-- DBCC FREEPROCCACHE (<plan_handle>);

 

Warnung: DBCC FREEPROCCACHE — das Skalpell, das kein Skalpell ist
DBCC FREEPROCCACHE ohne Parameter löscht den gesamten Plan Cache. Danach müssen ALLE Prozeduren und Abfragen neu kompiliert werden. Das erzeugt auf einem Produktionssystem unmittelbar nach der Ausführung einen massiven CPU-Spike — der Server ist für 30 bis 120 Sekunden unter Volllast, weil alle Pläne gleichzeitig neu kompiliert werden.
In Testumgebungen: unproblematisch. In Produktion: nur nach sorgfältiger Überlegung und idealerweise im Wartungsfenster. Die gezielte Variante DBCC FREEPROCCACHE (<plan_handle>) löscht nur einen einzelnen Plan — das ist fast immer die bessere Wahl.

 

Der Plan Cache — Anatomie eines oft unterschätzten Speicherbereichs

Der Plan Cache ist ein Teilbereich des Buffer Pools (Kapitel 11). Er speichert kompilierte Abfragepläne, damit sie nicht bei jedem Aufruf neu erstellt werden müssen. Was viele nicht wissen: Der Plan Cache kann auf einem produktiven System leicht mehrere Gigabyte groß werden — und er konkurriert direkt mit dem Data Cache um Speicher. Je mehr Plan Cache, desto weniger Platz für Datenseiten, desto mehr IO. Ein überfüllter Plan Cache ist also nicht nur ein Kompilierungsproblem, sondern indirekt auch ein IO-Problem.

sys.dm_exec_cached_plans — was im Cache steckt

Das wichtigste Feld in sys.dm_exec_cached_plans ist objtype — es zeigt, von welchem Typ der gecachte Plan ist. Die häufigsten Werte:

 

Objtype	Bedeutung	Typische Größe
Proc	Stored Procedure — explizit gespeicherter Plan	10–500 KB
Prepared	Explizit parametrisierte Abfrage via sp_executesql	5–100 KB
Adhoc	Ad-hoc SQL — oft Single-Use-Pläne	1–50 KB (×tausende)
ReplProc	Replikations-Prozedur	5–50 KB
Trigger	DML-Trigger	5–200 KB
View	Indizierte View	10–100 KB
Check	CHECK-Constraint	1–10 KB

Plan Cache Objekttypen und typische Größen

 

Der Typ Adhoc ist der Problemkind unter den Plan-Cache-Einträgen. Jede Abfrage, die Literal-Werte statt Parameter verwendet, bekommt einen eigenen Cache-Eintrag. "WHERE KundeID = 42", "WHERE KundeID = 43", "WHERE KundeID = 44" — drei separate Pläne, obwohl der Query-Text bis auf die Zahl identisch ist. Bei einer Applikation, die Tausende verschiedener Kundennummern direkt in den SQL-Text einfügt, entstehen Tausende von Single-Use-Plänen.

Single-Use-Pläne: Der stille CPU-Killer

Hier liegt die Verbindung zur CPU-Last: Jeder neue Ad-hoc-Plan, der nicht im Cache ist, erfordert eine vollständige Kompilierung. Kompilierung ist CPU-intensiv und nimmt außerdem eine kurze Latch-Sperre auf dem Query-Kompilierungs-Mutex. Bei hunderten von Kompilierungen pro Sekunde führt das zu Contention auf dem Scheduler (SOS_SCHEDULER_YIELD, wie wir in Kapitel 9 bei den Wait Statistics gesehen haben) und zu spürbarer CPU-Last — selbst, wenn die einzelnen Abfragen trivial sind.

-- Single-Use-Pläne und Plan Cache Bloat analysieren

-- Zeigt den Anteil einmalig genutzter Pläne am Gesamtcache

SELECT

    COUNT(*)                            AS Pläne_Gesamt,

    SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END)

                                        AS Single_Use_Pläne,

    -- Anteil in Prozent berechnen

    CAST(SUM(CASE WHEN usecounts = 1 THEN 1 ELSE 0 END) * 100.0

         / COUNT(*) AS DECIMAL(5,1))    AS Single_Use_Anteil_Pct,

    -- Speicherverbrauch für Single-Use-Pläne

    SUM(CASE WHEN usecounts = 1

             THEN size_in_bytes ELSE 0 END) / 1048576

                                        AS Single_Use_MB,

    -- Gesamter Plan Cache Speicher

    SUM(size_in_bytes) / 1048576        AS Gesamt_MB

FROM sys.dm_exec_cached_plans

WHERE cacheobjtype = 'Compiled Plan';

 

-- Faustregel: Single-Use-Anteil > 40% ist ein Warnsignal

-- > 60% bedeutet dringender Handlungsbedarf

-- Top-10 Plan Cache Einträge nach Speicherverbrauch

-- Hilft zu verstehen, was den Cache dominiert

SELECT TOP 10

    cp.objtype                          AS Typ,

    cp.usecounts                        AS Wiederverwendungen,

    cp.size_in_bytes / 1024             AS Groesse_KB,

    -- Query-Text auf 200 Zeichen kürzen für Übersicht

    LEFT(st.text, 200)                  AS SQL_Text

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

WHERE cp.cacheobjtype = 'Compiled Plan'

ORDER BY cp.size_in_bytes DESC;

Wie viel Speicher verbraucht der Plan Cache insgesamt? Das verrät sys.dm_os_memory_clerks. Die relevanten Einträge sind CACHESTORE_SQLCP (SQL Plan Cache für Ad-hoc und parametrisierte Abfragen) und CACHESTORE_OBJCP (Object Plan Cache für Stored Procedures, Trigger und Views).

-- Plan Cache Speicherverbrauch über Memory Clerks

SELECT

    type                                AS Clerk_Typ,

    name                                AS Clerk_Name,

    pages_kb / 1024                     AS Speicher_MB

FROM sys.dm_os_memory_clerks

WHERE type IN ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')

ORDER BY pages_kb DESC;

 

Tipp: Optimize for Ad Hoc Workloads aktivieren
sp_configure "optimize for ad hoc workloads" (Kapitel 05) ist die einfachste Gegenmaßnahme gegen Plan Cache Bloat durch Ad-hoc SQL. Mit dieser Einstellung speichert SQL Server beim ersten Aufruf nur einen kleinen "Stub" statt des vollen Plans. Erst beim zweiten Aufruf mit demselben Query-Text wird der vollständige Plan gecacht.
Das reduziert den Speicherverbrauch für Single-Use-Pläne massiv — in der Praxis oft um 60 bis 80 % — ohne die Performance häufig genutzter Abfragen zu beeinträchtigen. Diese Einstellung sollte auf jedem SQL Server aktiviert sein, der Ad-hoc Workloads verarbeitet.

 

Lösungsstrategien für Parameter Sniffing — das Werkzeugkasten

Es gibt nicht die eine Lösung für Parameter Sniffing — es gibt einen Werkzeugkasten, und welches Werkzeug passt, hängt von der konkreten Situation ab. Hier sind die wichtigsten Strategien mit ihren jeweiligen Vor- und Nachteilen.

OPTION (RECOMPILE) — immer frisch, immer teuer

OPTION (RECOMPILE) auf Statement-Ebene zwingt SQL Server, den Plan bei jedem Aufruf neu zu kompilieren. Der Plan wird nicht gecacht — jeder Aufruf bekommt seinen eigenen, für die aktuellen Parameter optimierten Plan. Das klingt nach der perfekten Lösung. Der Haken: Kompilierung kostet CPU. Bei einer Prozedur, die 10.000 Mal pro Minute aufgerufen wird, sind das 10.000 Kompilierungen pro Minute. Das kann die CPU-Last messbar erhöhen — je nach Komplexität der Abfrage um 5 bis 30 %.

OPTION (RECOMPILE) ist die richtige Wahl, wenn: die Prozedur relativ selten aufgerufen wird (weniger als einige hundert Mal pro Minute), die Parameterwerte sich stark unterscheiden und keine "typische" Verteilung haben, oder, wenn die Kosten eines suboptimalen Plans bei ungünstigen Parametern so hoch sind, dass die Kompilierungskosten dagegen vernachlässigbar sind.

-- OPTION (RECOMPILE) auf Statement-Ebene

-- Der Plan für dieses Statement wird bei jedem Aufruf neu kompiliert

-- Richtig: nur auf das problematische Statement anwenden, nicht auf die ganze Prozedur

CREATE PROCEDURE dbo.usp_GetKundenBestellungen

    @KundeID INT

AS

BEGIN

    -- Dieser SELECT bekommt RECOMPILE — er ist das Problem

    SELECT

        b.BestellID,

        b.BestellDatum,

        b.GesamtBetrag

    FROM dbo.Bestellungen b

    WHERE b.KundeID = @KundeID

    OPTION (RECOMPILE);  -- Nur dieses Statement neu kompilieren

END;

OPTIMIZE FOR — der Kompromissweg

OPTIMIZE FOR teilt dem Optimizer mit, welchen Parameterwert er für die Schätzung verwenden soll. Es gibt zwei Varianten: OPTIMIZE FOR (@param = Wert) für einen konkreten "typischen" Wert, und OPTIMIZE FOR (@param UNKNOWN) für die statistische Durchschnittsdichte. Die zweite Variante ist häufig die praktischere: Der Optimizer nutzt die in den Statistiken gespeicherte durchschnittliche Selektivität — das ergibt einen Plan, der für keinen Parameter perfekt ist, aber für alle zumindest akzeptabel.

-- OPTIMIZE FOR UNKNOWN — Optimizer nutzt Durchschnittswert aus Statistiken

-- Gut, wenn keine einzelne Parameter-Gruppe dominiert

CREATE PROCEDURE dbo.usp_GetKundenBestellungen

    @KundeID INT

AS

BEGIN

    SELECT

        b.BestellID,

        b.BestellDatum,

        b.GesamtBetrag

    FROM dbo.Bestellungen b

    WHERE b.KundeID = @KundeID

    OPTION (OPTIMIZE FOR (@KundeID UNKNOWN));

END;

 

-- Alternativ: für einen bekannten "typischen" Wert optimieren

-- Wenn KundeID = 1000 die typische Ergebnismenge repräsentiert:

-- OPTION (OPTIMIZE FOR (@KundeID = 1000));

Lokale Variablen — der Trick mit dem Informationsentzug

Eine etwas unorthodoxe, aber manchmal effektive Methode: Parameter in lokale Variablen kopieren, bevor sie in der Abfrage verwendet werden. Der Optimizer kennt den Wert einer lokalen Variablen zur Compile-Zeit nicht — er muss die Durchschnittsdichte aus den Statistiken verwenden, ähnlich wie bei OPTIMIZE FOR UNKNOWN. Der Unterschied: Diese Methode wirkt implizit und ist schwerer zu verstehen, wenn jemand anderes den Code liest. Explizit ist besser — lieber OPTIMIZE FOR UNKNOWN schreiben und damit klar signalisieren, was hier passiert.

-- Lokale Variable als "Parameter Sniffing Blocker"

-- Der Optimizer sieht @localKundeID und weiß nicht, welchen Wert sie hat

CREATE PROCEDURE dbo.usp_GetKundenBestellungen

    @KundeID INT

AS

BEGIN

    -- Lokale Variable: Optimizer verwendet Durchschnittsdichte

    DECLARE @localKundeID INT = @KundeID;

 

    SELECT

        b.BestellID,

        b.BestellDatum,

        b.GesamtBetrag

    FROM dbo.Bestellungen b

    WHERE b.KundeID = @localKundeID;  -- Kein Parameter-Sniffing möglich

    -- Nachteil: Bei schiefer Verteilung kann der Durchschnitt trotzdem

    -- suboptimal sein — OPTIMIZE FOR UNKNOWN ist expliziter und gleichwertig

END;

WITH RECOMPILE auf Prozedur-Ebene — der Schuss, der nach hinten losgeht

Hier eine explizite Warnung: WITH RECOMPILE auf der CREATE PROCEDURE-Anweisung selbst ist fast nie die richtige Antwort. Diese Option kompiliert die gesamte Prozedur bei jedem Aufruf neu — auch, wenn die Prozedur zwanzig Statements enthält und nur ein einziges davon ein Parameter-Sniffing-Problem hat. Das ist wie das gesamte Haus renovieren, weil ein Lichtschalter klemmt. Die richtige Lösung ist OPTION (RECOMPILE) auf Statement-Ebene.

 

Warnung: WITH RECOMPILE auf Prozedur-Ebene
CREATE PROCEDURE dbo.MeineProzedur WITH RECOMPILE AS … kompiliert JEDES Statement der Prozedur bei JEDEM Aufruf neu. Keine Plan-Wiederverwendung, keine Cache-Einträge, maximale CPU-Belastung durch Kompilierungen.
Ausnahme: Maintenance-Prozeduren wie Statistik-Updates oder Index-Rebuilds, die sich auf unterschiedliche Tabellen beziehen und ohnehin selten aufgerufen werden. Für reguläre Stored Procedures: NIEMALS. Verwende stattdessen OPTION (RECOMPILE) auf dem problematischen Statement.

 

Spezialisierte Prozeduren — die chirurgische Lösung

Bei extremen Verteilungen — wenn es wirklich zwei komplett verschiedene Welten gibt, zum Beispiel "Kleinkunde mit 1–5 Bestellungen" und "Großkunde mit 100.000+ Bestellungen" — kann es sinnvoll sein, zwei separate Prozeduren zu erstellen: eine, die für kleine Ergebnismengen optimiert ist (Nested Loop, Seek-orientiert), und eine für große (Hash Join, Scan-orientiert). Eine Dispatcher-Prozedur ermittelt anhand der erwarteten Ergebnismenge (zum Beispiel über eine schnelle COUNT-Abfrage oder eine Kundenkategorie aus der Stammdatentabelle), welche der beiden Prozeduren aufgerufen wird. Aufwändig, aber oft die einzige Lösung, die dauerhaft funktioniert.

Query Store Plan Forcing — die moderne Antwort

Ab SQL Server 2016 bietet der Query Store eine elegante Möglichkeit, Parameter Sniffing zu beherrschen: Plan Forcing. Du identifizierst den Plan, der für die typische Workload gut funktioniert, und weist SQL Server an, immer diesen Plan zu verwenden — unabhängig von den übergebenen Parametern. Der Vorteil gegenüber OPTION (RECOMPILE): kein CPU-Overhead durch ständige Neukompilierung. Kapitel 19 geht ausführlich auf den Query Store und Plan Forcing ein.

Ad-hoc Workloads und Plan Cache Pollution — das Trendforge-Problem

Parameter Sniffing in Stored Procedures ist überschaubar — es gibt einen konkreten Plan, eine klare Ursache, gezielte Lösungen. Deutlich ungemütlicher ist das Problem bei Ad-hoc Workloads, die von ORMs oder schlecht parametrisiertem Applikationscode generiert werden. Hier kommt Trendforge Digital GmbH ins Spiel — ein Paradebeispiel dafür, was passiert, wenn Entity Framework ohne Rücksicht auf den Plan Cache eingesetzt wird (ausführliche Fallstudie in Kapitel 34).

Entity Framework und andere ORMs erzeugen SQL-Code aus Objekt-Abfragen. In der Standardkonfiguration parametrisiert EF zwar grundsätzlich, aber die generierten Abfragen können sich je nach LINQ-Query erheblich unterscheiden: verschiedene WHERE-Klauseln, verschiedene JOINs, verschiedene ORDER BY-Ausdrücke. Jede dieser Varianten bekommt einen eigenen Plan-Cache-Eintrag.

Bei Trendforge haben wir in sys.dm_exec_cached_plans 847 verschiedene Varianten einer logisch ähnlichen Abfrage gefunden — weil die Entwickler LINQ dynamisch zusammenbauten und je nach Filteroptionen unterschiedliche WHERE-Bedingungen generierten. Der Plan Cache belegte 2,3 GB. Da der Buffer Pool auf 12 GB konfiguriert war, bedeutete das 2,3 GB weniger für den Data Cache — und entsprechend mehr IO, weil Datenseiten nicht mehr im Speicher gehalten werden konnten (Verweis auf Kapitel 11 für Buffer Pool und Memory-Druck).

Dazu kam der CPU-Overhead: Viele dieser 847 Varianten wurden pro Tag nur einmal oder zweimal aufgerufen. Jeder Aufruf einer Variante, die noch nicht im Cache war, erforderte eine vollständige Kompilierung. Bei 2.000 verschiedenen Ad-hoc-Varianten pro Stunde — gemessen über sys.dm_exec_query_stats — betrug die CPU-Last durch Kompilierungen allein etwa 8 % der Gesamtlast. Nicht dramatisch, aber messbar, und vermeidbar.

Parameterisierung — wie SQL Server Queries aufbereitet

SQL Server hat zwei automatische Mechanismen, um Ad-hoc-Queries zu parametrisieren und damit Cache-Wiederverwendung zu ermöglichen:

Simple Parameterization ist das Standard-Verhalten. SQL Server versucht, einfache Abfragen wie "SELECT * FROM Kunden WHERE KundeID = 42" automatisch zu parametrisieren und als "SELECT * FROM Kunden WHERE KundeID = @1" zu cachen. Das funktioniert für triviale Abfragen, scheitert aber bei komplexeren Strukturen, Subqueries oder bestimmten Operator-Kombinationen.

Forced Parameterization (Datenbankeinstellung, ALTER DATABASE … SET PARAMETERIZATION FORCED) weitet dieses Verhalten auf fast alle Abfragen aus. Das klingt verlockend, hat aber Tücken: Forced Parameterization kann dazu führen, dass Abfragen mit unterschiedlichen Literalwerten, die eigentlich verschiedene optimale Pläne benötigen, denselben suboptimalen Plan bekommen. Es ist quasi systemweites Parameter Sniffing — gelegentlich hilfreich, öfter problematisch.

Die zuverlässigste Methode ist explizite Parameterisierung via sp_executesql. Dabei wird der Query-Text als Template definiert, und Parameter werden separat übergeben. SQL Server cacht den Plan unter dem parametrisierten Template — alle Aufrufe mit unterschiedlichen Werten teilen sich denselben Plan-Cache-Eintrag.

-- Explizite Parameterisierung mit sp_executesql

-- Alle Aufrufe, egal mit welchem KundeID-Wert, teilen sich diesen Plan

DECLARE @sql NVARCHAR(500);

DECLARE @params NVARCHAR(100);

 

-- Query-Template: @KundeID ist Platzhalter, kein Literal

SET @sql = N'SELECT BestellID, BestellDatum

             FROM dbo.Bestellungen

             WHERE KundeID = @KundeID';

 

-- Parameter-Typ-Deklaration

SET @params = N'@KundeID INT';

 

-- Ausführung: SQL Server erstellt Plan für @KundeID als Parameter

-- Beim zweiten Aufruf mit anderem KundeID-Wert: Plan-Cache-Treffer

EXEC sp_executesql @sql, @params, @KundeID = 42;

 

-- Vergleich: direkte Ausführung mit Literal → KEIN Cache-Treffer

-- EXEC ('SELECT BestellID FROM dbo.Bestellungen WHERE KundeID = 42');

-- EXEC ('SELECT BestellID FROM dbo.Bestellungen WHERE KundeID = 43');

-- → zwei separate Plan-Cache-Einträge!

Plan Cache leeren — wann ja, wann nein, und wie richtig

Die Verlockung ist groß: "Der Server ist langsam, ich leere einfach den Plan Cache, dann werden alle Pläne neu kompiliert und die schlechten verschwinden." Das ist ungefähr so clever wie das Löschen des Browser-Caches, wenn eine Website nicht lädt. Manchmal hilft es tatsächlich — aber öfter erzeugt es nur einen neuen, anderen Schmerz.

Das Problem: Nach DBCC FREEPROCCACHE ist der Plan Cache leer. Der erste Aufruf jeder Prozedur, jeder parametrisierten Abfrage, jedes Views wird eine Kompilierung auslösen. Auf einem aktiven Produktionssystem passiert das für Hunderte oder Tausende verschiedener Queries innerhalb der ersten Minute nach dem Befehl. Die CPU läuft für 30 bis 120 Sekunden auf deutlich erhöhtem Niveau — in Spitzenzeiten kann das zu einem messbaren Performance-Einbruch führen.

Hinzu kommt das Parameter-Sniffing-Risiko: Wenn nach dem Cache-Flush die ersten Aufrufe von untypischen Clients kommen — etwa ein Batch-Job, der morgens um 6 Uhr mit ungewöhnlichen Parametern läuft, bevor die normalen Benutzer einloggen — werden genau diese Werte "gesnifft" und die resultierenden Pläne für den normalen Tagesgeschäft-Traffic gecacht. Das Problem ist möglicherweise nach dem Flush schlimmer als davor.

Wann DBCC FREEPROCCACHE legitim ist

Es gibt tatsächlich Situationen, in denen FREEPROCCACHE das richtige Werkzeug ist:

Nach einem großen Massenimport oder einer Bulk-Operation, bei der Statistiken danach aktualisiert wurden — gecachte Pläne basieren auf veralteten Statistiken

Nach umfangreichen Schema-Änderungen (neue Indizes, DROP und CREATE von Tabellen) — SQL Server invalidiert nicht alle betroffenen Pläne automatisch

Nach UPDATE STATISTICS WITH FULLSCAN auf kritischen Tabellen, wenn bekannt ist, dass ein spezifischer Plan falsch ist

In Testumgebungen vor Performance-Messungen — um reproduzierbare Startbedingungen herzustellen

Für alle anderen Fälle gilt: gezieltes Löschen ist besser. Wenn du weißt, welcher Plan das Problem ist (durch die Diagnose-Queries oben), lösche nur diesen einen Plan.

-- Gezieltes Löschen eines einzelnen Plans aus dem Cache

-- plan_handle vorher aus sys.dm_exec_cached_plans holen

DECLARE @plan_handle VARBINARY(64);

 

-- Plan-Handle für eine bestimmte Prozedur finden

SELECT TOP 1 @plan_handle = cp.plan_handle

FROM sys.dm_exec_cached_plans cp

CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st

WHERE st.objectid = OBJECT_ID('dbo.usp_GetBestellungen')

  AND cp.objtype = 'Proc';

 

-- Nur diesen einen Plan entfernen — der Rest bleibt im Cache

IF @plan_handle IS NOT NULL

    DBCC FREEPROCCACHE (@plan_handle);

 

-- Bestätigung: Plan ist nicht mehr im Cache

SELECT COUNT(*)

FROM sys.dm_exec_cached_plans cp

WHERE cp.plan_handle = @plan_handle;

-- Ergebnis: 0 = Plan erfolgreich entfernt

Fallstudie Musterwerk GmbH — klassisches Parameter Sniffing in der Praxis

Bei der Musterwerk GmbH (MWSQL01) stießen wir auf ein Lehrbuchbeispiel für Parameter Sniffing. Die Prozedur usp_GetBestellungen, die täglich von der Faktura-Applikation aufgerufen wird, verhielt sich völlig unberechenbar: Manchmal 8 ms, manchmal 4.200 ms — für denselben Aufruf aus demselben Applikationscode. Die Supportanrufe häuften sich, der DBA fand im Taskmanager keine auffällige CPU-Last und keine IO-Bottlenecks, und das Monitoring zeigte… gar nichts Ungewöhnliches. Weil das Problem intermittierend war.

Die Diagnose via sys.dm_exec_procedure_stats zeigte sofort das Muster: min_worker_time = 6.400 µs, max_worker_time = 3.980.000 µs — ein Verhältnis von 622. Gleichzeitig plan_generation_num = 1, also nur ein einziger Plan im Cache, der seit dem letzten Serverstart unverändert läuft.

Der Ausführungsplan aus dem Cache zeigte einen Hash Join über die Bestellungen-Tabelle, optimiert für eine große Ergebnismenge. Estimated Rows = 847 für die innere Seite des Joins. Die tatsächlichen Aufrufe für Kleinkunden lieferten aber Actual Rows = 2 bis 15. SQL Server baute für jeden dieser Kleinkunden-Aufrufe einen Hash Join auf — Hash Table erstellen, Probephase durchführen, alles für zwei Zeilen. Der Overhead des Hash Joins für zwei Zeilen ist um Faktoren teurer als ein Nested Loop, der zweimal einen Index-Seek macht.

Was war passiert? Der täglich laufende Batch-Job zur Rechnungsverarbeitung des Großkunden (KundeID = 1, über 800 Bestellungen pro Tag) lief um 6:00 Uhr — und damit war er typischerweise der erste Aufrufer von usp_GetBestellungen nach dem Nacht-Wartungsfenster, in dem die Statistiken aktualisiert und der Plan Cache geleert worden waren. Der Großkunden-Plan wurde gecacht, alle anderen Kunden litten darunter.

Die Lösung war eine einzige Zeile Code: OPTIMIZE FOR (@KundeID UNKNOWN) am Ende des problematischen Statements. Der resultierende Plan nutzt die durchschnittliche Selektivität aus den Statistiken — ca. 45 Bestellungen pro Kunde im Mittel. Der Optimizer wählt für diesen Durchschnittswert einen Index-Seek mit einem effizienten Nested Loop. Großkunden sind damit nicht mehr optimal bedient, aber der Hash Join ist trotzdem schneller für sie als der alte Nested Loop für die Kleinkunden war.

 

Praxisbeispiel: Musterwerk: Ergebnis nach Optimierung
Vorher: min 8 ms / max 4.200 ms — Schwankungsfaktor 525
Nachher: min 6 ms / max 340 ms — Schwankungsfaktor 57
Die maximale Laufzeit ist immer noch höher als das Minimum, weil Großkunden nun einen Plan bekommen, der für den Durchschnitt optimiert ist. Aber der Plan ist konsistent und vorhersagbar — keine Ausreißer mehr im vierstelligen Millisekunden-Bereich. Nutzer-Beschwerden: null.

 

Diagnose-Kästen: Parameter Sniffing & Plan Cache

 

Warnung: Symptome
Stored Procedure manchmal blitzschnell (< 10 ms), manchmal extrem langsam (> 5.000 ms) — mit denselben Parametern, je, nachdem wann man fragt.
CPU-Spikes ohne neue Last auf dem System — besonders kurz nach Serverstart oder nach DBCC FREEPROCCACHE, wenn alle Pläne gleichzeitig neu kompiliert werden.
In sys.dm_exec_procedure_stats: min_worker_time und max_worker_time divergieren um mehr als Faktor 50. plan_generation_num > 1 bei einer kritischen Prozedur.
Plan Cache verbraucht über 1 GB (sys.dm_os_memory_clerks, CACHESTORE_SQLCP + CACHESTORE_OBJCP).
Im Ausführungsplan: Estimated Rows weicht stark von Actual Rows ab — zum Beispiel Estimated = 3 bei Actual = 500.000 oder umgekehrt.
Intermittierende Performance-Probleme, die nach Server-Neustart oder Statistik-Update kurzzeitig verschwinden und dann wiederkehren.

 

 

Hinweis: So misst du das
Schritt 1 — Divergenz-Analyse: sys.dm_exec_procedure_stats nach min_worker_time / max_worker_time Verhältnis sortieren. Werte > 100 sind verdächtig.
Schritt 2 — Plan aus Cache extrahieren: sys.dm_exec_cached_plans + sys.dm_exec_query_plan + sys.dm_exec_sql_text für die verdächtige Prozedur.
Schritt 3 — Estimated vs. Actual vergleichen: Plan in SSMS öffnen und bei jedem Operator die geschätzten gegen die tatsächlichen Zeilenzahlen prüfen. Abweichung > Faktor 10 ist ein starkes Signal.
Schritt 4 — Plan Cache Gesundheit: Single-Use-Anteil berechnen (usecounts = 1 in sys.dm_exec_cached_plans). > 40% ist ein Warnsignal, > 60% dringend.
Schritt 5 — Memory-Verbrauch: sys.dm_os_memory_clerks für CACHESTORE_SQLCP und CACHESTORE_OBJCP. Wenn beide zusammen mehr als 20% des konfigurierten Max Server Memory belegen, ist Handlungsbedarf vorhanden.

 

 

Hintergrund: Typische Fehlinterpretationen
"Parameter Sniffing ist immer schlecht." — Falsch. Parameter Sniffing funktioniert in der großen Mehrheit der Fälle korrekt und spart erhebliche CPU-Zeit durch Plan-Wiederverwendung. Das Problem tritt nur bei schiefer Datenverteilung auf.
"DBCC FREEPROCCACHE löst Parameter Sniffing." — Temporär ja, dauerhaft nein. Das Problem ist nicht der gecachte Plan, sondern die ungleichmäßige Datenverteilung. Nach dem Flush tritt das Problem bei der nächsten ungünstigen Erstausführung wieder auf. Dazu kommt der CPU-Spike durch gleichzeitige Neukompilierung aller Pläne.
"WITH RECOMPILE auf der Prozedur behebt Parameter Sniffing." — Ja, aber auf Kosten jeder Plan-Wiederverwendung für die gesamte Prozedur. Alle Statements werden bei jedem Aufruf neu kompiliert — auch die, die kein Problem haben. CPU-Overhead kann erheblich sein. OPTION (RECOMPILE) auf Statement-Ebene ist fast immer besser.
"Estimated Rows = Actual Rows bedeutet kein Sniffing." — Falsch. Wenn ein Sniffing-Plan gecacht ist und du ihn mit demselben Parameter ausführst, für den er erstellt wurde, stimmen die Werte überein. Das Problem tritt nur bei anderen Parametern auf.
"OPTIMIZE FOR UNKNOWN ist immer die beste Lösung." — Nein. Bei sehr schiefer Verteilung mit wenigen Ausreißern kann OPTION (RECOMPILE) oder sogar spezialisierte Prozeduren deutlich besser sein. OPTIMIZE FOR UNKNOWN liefert einen "mittelmäßigen" Plan für alle — manchmal ist ein optimaler Plan für die häufigen Fälle wichtiger.

 

 

Tipp: Erste Gegenmaßnahmen
1. Optimize for Ad Hoc Workloads aktivieren (falls nicht bereits aktiv — Kapitel 05). Sofort wirksam, kein Neustart, reduziert Single-Use-Plan-Bloat erheblich.
2. Für die identifizierte Problem-Prozedur: OPTION (RECOMPILE) auf das problematische Statement oder OPTIMIZE FOR (@param UNKNOWN) als ersten Test. OPTIMIZE FOR UNKNOWN ist risikoärmer, weil kein CPU-Overhead durch Neukompilierung.
3. Gezieltes Cache-Flush für den problematischen Plan: DBCC FREEPROCCACHE (<plan_handle>). Damit wird nur dieser eine Plan entfernt, und der nächste Aufruf erzeugt einen neuen Plan — hoffentlich mit einem besseren Parameter.
4. Query Store einschalten (falls nicht aktiv) und Plan Forcing als mittelfristige Lösung evaluieren — Kapitel 19 für Details.
5. Statistiken auf den betroffenen Tabellen prüfen und ggf. UPDATE STATISTICS WITH FULLSCAN ausführen. Veraltete Statistiken und Parameter Sniffing treten häufig gemeinsam auf (Kapitel 16 für Statistiken und Plan Regression).

 

Zusammenfassung

Parameter Sniffing ist eines dieser Themen, die auf Anhieb logisch klingen — SQL Server speichert einen optimierten Plan und verwendet ihn wieder, statt bei jedem Aufruf neu zu kompilieren. Das ist gut. Das Problem entsteht, wenn der "erste Patient" ein Extremfall ist und sein Behandlungsplan dann für alle anderen gilt.

Die wichtigsten Erkenntnisse aus diesem Kapitel:

Parameter Sniffing ist meistens ein Feature, nicht ein Bug — nur bei schiefer Datenverteilung wird es zum Problem.

Das verlässlichste Diagnosewerkzeug ist die Divergenz zwischen min_worker_time und max_worker_time in sys.dm_exec_procedure_stats kombiniert mit dem Vergleich von Estimated vs. Actual Rows im Ausführungsplan.

OPTION (RECOMPILE) auf Statement-Ebene ist fast immer besser als WITH RECOMPILE auf Prozedur-Ebene — beide lösen das Problem, aber letzteres ist viel teurer.

OPTIMIZE FOR (@param UNKNOWN) ist ein guter Kompromiss bei mittlerer Datenschiefe — kein CPU-Overhead, kein perfekter Plan, aber ein konsistenter Plan.

Plan Cache Bloat durch Ad-hoc SQL ist ein eigenständiges Problem: Single-Use-Pläne fressen Speicher und erzeugen CPU-Last durch ständige Neukompilierungen.

Optimize for Ad Hoc Workloads (sp_configure) sollte auf jedem SQL Server aktiviert sein — es ist die billigste und effektivste Gegenmaßnahme gegen Plan Cache Bloat.

DBCC FREEPROCCACHE ohne Parameter ist eine Waffe mit Rückstoß: Sie löst das kurzfristige Problem, erzeugt aber einen CPU-Spike und setzt das Sniffing-Problem zurück auf null — der nächste ungünstige Erstaufruf produziert denselben schlechten Plan.

Die CPU-Perspektive auf Parameter Sniffing ist dabei oft unterbewertet. Nicht nur der suboptimale gecachte Plan kostet CPU — auch die Reaktion darauf (ständige Recompilierungen, FREEPROCCACHE, schlechte Workarounds) kann die CPU-Last erheblich erhöhen. SOS_SCHEDULER_YIELD in den Wait Statistics (Kapitel 09) kann ein Hinweis sein, dass Kompilierungsoverhead zum CPU-Druck beiträgt.

Das Zusammenspiel zwischen Parameter Sniffing, Statistiken und Plan Regression (Kapitel 16) ist eng: Veraltete Statistiken verschlimmern Sniffing, weil der Optimizer mit falschen Grundannahmen arbeitet. Und Plan Regression nach einem Statistik-Update kann dazu führen, dass ein bisher guter Plan plötzlich schlechter wird — oft ist Parameter Sniffing der zugrundeliegende Mechanismus.

Bei Musterwerk haben wir das klassische Muster gesehen: Batch-Job als erster Aufrufer nach Wartungsfenster, Großkunden-Plan gecacht, alle anderen Nutzer leiden. Die Lösung war mit einer Zeile Code getan — aber erst nach der richtigen Diagnose. Blind FREEPROCCACHE auszuführen hätte das Problem nur bis zum nächsten Morgen um 6 Uhr verschoben.

Ausblick: Kapitel 19 — Query Store

Das nächste Kapitel widmet sich dem Query Store — SQL Servers eingebautem Performance-Analyse-Werkzeug, das seit SQL Server 2016 verfügbar ist und ab 2022 standardmäßig aktiviert ist. Der Query Store löst viele der Probleme, die wir in diesem Kapitel nur mit DMV-Abfragen und manuellen Workarounds adressieren konnten: Plan Forcing, automatische Regression-Erkennung, historische Plan-Verläufe.

Insbesondere für Parameter Sniffing bietet der Query Store eine elegante Lösung: Du kannst den Plan, der für die typische Workload optimal ist, direkt forcen — SQL Server verwendet dann immer diesen Plan, unabhängig vom ersten Aufrufer. Das ist Plan Forcing ohne manuellen Workaround-Code, persistent über Server-Neustarts, und überwacht durch automatische Tuning-Funktionen. Kapitel 19 zeigt, wie das in der Praxis aussieht.

 

 

Abb. 1: Parameter Sniffing: Ablauf und Problem

 

Abb. 2: Lösungsstrategien im Vergleich

 

Kapitel 19