ronald@dbaronald:~$
EN
← Alle SQL tips

Parameter sniffing: als één plan niet voor iedereen past

-- performance tuning · Ronald de Groot

Het klassieke telefoontje: "die procedure was altijd snel, en sinds vanochtend duurt hij minuten — en we hebben níets veranderd." Negen van de tien keer is dit parameter sniffing. Geen bug, maar een ontwerpkeuze van SQL Server die meestal goed uitpakt — en soms desastreus.

Wat er gebeurt

Bij de eerste uitvoering van een stored procedure (of geparametriseerde query) "snuift" de optimizer de waarden van de parameters op en bouwt daar het beste plan voor. Dat plan gaat de plan cache in en wordt hergebruikt voor alle volgende aanroepen — ongeacht hun parameterwaarden.

Dat is meestal precies wat je wilt: compileren is duur, en waarom zou je het plan opnieuw bouwen voor elke aanroep? Het gaat mis als de datadistributie scheef is:

CREATE PROCEDURE dbo.GetOrders @CustomerID int
AS
SELECT OrderID, OrderDate, TotalAmount
FROM   dbo.Orders
WHERE  CustomerID = @CustomerID;

-- Klant 42: 12 orders     → ideaal: Index Seek + Key Lookup
-- Klant 1001: 4,2 miljoen → ideaal: Clustered Index Scan

Wordt het plan gecompileerd voor klant 42, dan doet diezelfde seek-met-lookup er bij klant 1001 miljoenen lookups — minuten in plaats van milliseconden. Andersom net zo goed: een scan-plan gecompileerd voor klant 1001 leest voor klant 42 de hele tabel om 12 rijen te vinden.

Zo herken je het

Vijf oplossingen — en wanneer welke past

1. OPTION (RECOMPILE): altijd het beste plan, altijd compileren

SELECT ... FROM dbo.Orders
WHERE  CustomerID = @CustomerID
OPTION (RECOMPILE);

Elke uitvoering krijgt een vers plan voor de échte waarden. Perfect voor rapportage-queries die enkele keren per minuut draaien; te duur voor een OLTP-query die duizend keer per seconde langskomt — dan betaal je continu CPU voor het compileren.

2. OPTIMIZE FOR: kies bewust één scenario

OPTION (OPTIMIZE FOR (@CustomerID = 1001));   -- plan voor de grote klant
-- of:
OPTION (OPTIMIZE FOR UNKNOWN);                -- plan voor het "gemiddelde"

OPTIMIZE FOR UNKNOWN negeert het snuiven en pakt de gemiddelde dichtheid uit de statistieken. Dat maakt de performance voorspelbaar — niet per se optimaal. Kies dit als een stabiel middenplan beter is dan gokken.

3. Herschrijven: splits de scenario's

De nette structurele oplossing bij echt scheve data: stuur grote en kleine klanten naar aparte (sub)procedures, zodat elk zijn eigen gecachte plan krijgt. Meer code, maar beide paden krijgen blijvend hun ideale plan.

4. Query Store: forceer het goede plan

Vanaf SQL Server 2016 vaak de snelste productie-fix: in Query Store het goed presterende plan aanwijzen en Force Plan klikken (of sp_query_store_force_plan). Vanaf SQL 2022 doet Automatic Plan Correction dit zelfs automatisch bij een aantoonbare regressie.

5. De noodrem: één plan eruit gooien

-- Haal alleen het plan van deze procedure uit de cache:
EXEC sp_recompile 'dbo.GetOrders';

Geen structurele oplossing — het lost niets op voor de volgende keer — maar om 3 uur 's nachts is het een prima eerste hulp. Gebruik nooit DBCC FREEPROCCACHE zonder plan handle op een drukke server: dan hercompileert álles.

Wat ik in de praktijk doe

  1. Bevestig de diagnose in Query Store: meerdere plannen, sterk wisselende looptijden, afwijkende ParameterCompiledValue.
  2. Acute pijn? Plan forceren (of sp_recompile) om de productie te ontlasten.
  3. Daarna structureel kiezen: RECOMPILE voor laagfrequente rapportages, OPTIMIZE FOR of een herschrijf voor hoogfrequente OLTP-paden.
  4. En check de indexen: soms is de echte oorzaak dat er voor het "grote" scenario gewoon een betere index zou moeten bestaan — dan verdwijnt het verschil tussen de plannen vanzelf.

Een procedure die zich onvoorspelbaar gedraagt en je komt er niet uit? Stuur me het plan — daar kijk ik graag even naar.