Narcis Miclaus

6 min de citit

SQL Server, de la zero Lecția 27 / 40

Parameter sniffing: bug-ul, feature-ul, fix-ul

De ce e query-ul rapid în SSMS și lent în aplicație. OPTIMIZE FOR, RECOMPILE, plan forcing prin Query Store și feature-urile recente SQL Server care fac asta mai puțin dureros.

Inginerul de support îți deschide un ticket: „Endpoint-ul «find customer by email» face brusc timeout pentru unii utilizatori, dar nu pentru alții.”

Rulezi query-ul de bază în SSMS. Instant. Renunți să-l mai reproduci local. Inginerul renunță și restartează app server-ul. Problema dispare trei zile, apoi revine.

Asta e parameter sniffing. Lecția de azi e cea care o rezolvă.

Mecanismul, într-un paragraf

SQL Server pune în cache planurile de execuție pentru query-uri parametrizate. Prima dată când rulează query-ul, optimizatorul construiește un plan folosind valorile specifice ale parametrilor pe care le vede. Acel plan e apoi reutilizat pentru fiecare apel ulterior, indiferent de valorile parametrilor. Când primul apel s-a întâmplat să prindă „o valoare extremă” — un client cu 20.000 de comenzi, să zicem — planul e optimizat pentru acel caz. Un apel ulterior cu un client normal reutilizează planul de caz extrem și e sălbatic de greșit.

Ăsta e bug-ul. E numit și „feature” pentru că pentru 80% din query-uri sniffing-ul produce un plan grozav și economisește costul de optimizare. Pentru celelalte 20%, e motivul pentru care nu poți reproduce o problemă de prod în dev.

Demo-ul canonic

CREATE PROCEDURE Sales.GetOrdersForCustomer
    @CustomerId INT
AS
BEGIN
    SELECT * FROM Sales.Orders WHERE CustomerId = @CustomerId;
END;

-- Primul apel: un client „mic”
EXEC Sales.GetOrdersForCustomer @CustomerId = 123;
-- Plan: nested loop, key lookup. Excelent pentru ~5 rânduri.

-- Apel ulterior: un client „uriaș” cu 50.000 de comenzi
EXEC Sales.GetOrdersForCustomer @CustomerId = 42;
-- Reutilizează planul nested-loop. Face 50.000 de key lookup-uri. Timeout.

-- Alt utilizator SSMS rulează query-ul direct
SELECT * FROM Sales.Orders WHERE CustomerId = 42;
-- Compile proaspăt. Optimizatorul vede @CustomerId = 42, construiește un plan mai bun (scan + filter).
-- Rapid.

De aceea „e rapid în SSMS și lent în aplicație” — rularea proaspătă din SSMS primește un plan proaspăt; aplicația reutilizează planul cache-uit de ieri.

Fix-uri, de la cel mai puțin la cel mai invaziv

Fix 1: OPTION (RECOMPILE)

Forțează un plan proaspăt pentru fiecare apel:

SELECT * FROM Sales.Orders WHERE CustomerId = @CustomerId
OPTION (RECOMPILE);
  • Pro: folosește mereu cel mai bun plan pentru parametrul curent.
  • Contra: cost de compile la fiecare apel. Pentru o procedură stocată apelată de mii de ori pe minut, e CPU pe care nu ți-l permiți.

Folosește RECOMPILE pe query-uri unde dezechilibrul de parametri e mare și volumul de apeluri e mic. Rapoarte, dashboard-uri ad-hoc, unelte de admin — fit perfect. Endpoint-ul hot de order-lookup apelat de 5.000 de ori pe minut — nu e locul.

Fix 2: OPTIMIZE FOR UNKNOWN

Spune-i optimizatorului să folosească densitatea medie în loc de valorile sniffed:

SELECT * FROM Sales.Orders WHERE CustomerId = @CustomerId
OPTION (OPTIMIZE FOR UNKNOWN);
  • Pro: fără cost de compile per apel. Plan generic.
  • Contra: planul generic e mediocru atât pentru cazuri mici cât și mari. Schimbi „grozav pentru unul, oribil pentru celălalt” cu „mediocru pentru ambele.”

Util când distribuția parametrului e uniformă și nicio valoare nu e outlier. Altfel, mai mult un workaround istoric.

Fix 3: OPTIMIZE FOR (@p = 'some value')

Forțează o valoare specifică la momentul compilării planului:

SELECT * FROM Sales.Orders WHERE CustomerId = @CustomerId
OPTION (OPTIMIZE FOR (@CustomerId = 1));
-- Presupune că CustomerId 1 e „reprezentativ pentru cazul tipic”
  • Pro: plan determinist, optimizat pentru valoarea aleasă de tine.
  • Contra: povara mentenanței. Ce e „tipic” poate să se schimbe în timp.

Folosit când știi că o valoare specifică produce forma de plan pe care o vrei pentru majoritatea apelurilor.

Fix 4: parameter masking (vechiul hack)

Asignează parametrul unei variabile locale în interiorul procedurii:

CREATE PROCEDURE Sales.GetOrdersForCustomer
    @CustomerId INT
AS
BEGIN
    DECLARE @c INT = @CustomerId;    -- variabila locală sparge sniffing-ul
    SELECT * FROM Sales.Orders WHERE CustomerId = @c;
END;

Optimizatorul nu poate „sniff” o variabilă locală (valoarea ei nu e cunoscută la compile), așa că folosește densitatea medie. Efectiv același lucru ca OPTIMIZE FOR UNKNOWN.

Trick-ul ăsta e peste tot în cod vechi de producție. Funcționează, e urât. Preferă hint-ul explicit OPTIMIZE FOR UNKNOWN.

Fix 5: plan forcing prin Query Store (cel modern)

Începând cu SQL Server 2016, Query Store urmărește fiecare query, fiecare plan, fiecare metrică de runtime. Dacă găsești un plan care merge bine, îl poți fixa: toate apelurile viitoare ale aceluiași query folosesc acel plan, indiferent de sniffing.

Vom acoperi Query Store cum trebuie în lecția 28. Teaser:

-- Forțează planul identificat ca plan_id 42 pentru query-ul 19
EXEC sp_query_store_force_plan @query_id = 19, @plan_id = 42;

E modul production-safe de a spune „planul ăsta e bun, folosește-l până când spun altfel.” Fără modificări de cod, fără hints. Reversibil cu sp_query_store_unforce_plan.

Fix 6: logică ramificată (opțiunea nucleară)

Când parametrii cad în range-uri clar diferite („clienți mici” vs „clienți uriași”), poți ramifica manual:

CREATE PROCEDURE Sales.GetOrdersForCustomer
    @CustomerId INT
AS
BEGIN
    IF EXISTS (SELECT 1 FROM Sales.Customer WHERE CustomerId = @CustomerId AND OrderCount > 10000)
        EXEC Sales.GetOrdersForLargeCustomer @CustomerId;
    ELSE
        EXEC Sales.GetOrdersForSmallCustomer @CustomerId;
END;

Două proceduri diferite, fiecare cu propriul plan cache-uit, ajustat pentru cazul ei. Urât. Folosit doar când celelalte fix-uri nu sunt suficiente.

Parameter Sensitive Plan optimization (SQL Server 2022+)

SQL Server 2022 a introdus Parameter Sensitive Plan (PSP) optimization — optimizatorul ține mai multe planuri pentru același query, alese în funcție de valoarea parametrului. Repară bug-ul la nivel de engine.

Activat automat pe baze de date cu compatibility level 160 (SQL 2022) sau mai mare. Engine-ul detectează parametrii sensibili la skew și pune în cache până la trei planuri per query. Cel potrivit e ales la runtime în funcție de „bucket-ul” parametrului (mic, mediu, mare).

Dacă ești pe SQL Server 2022 cu compat 160, majoritatea problemelor de parameter sniffing care au chinuit 2012-2019 pur și simplu… dispar. Nu toate — e o euristică, nu magie — dar o fracțiune semnificativă.

Verifică dacă PSP face ceva pentru query-urile tale:

SELECT TOP (20) query_id, query_text_id, plan_id, count_executions
FROM sys.query_store_plan
WHERE is_plan_for_psp = 1
ORDER BY count_executions DESC;

Recunoașterea simptomului în sălbăticie

„Parameter sniffing” e răspunsul când:

  • Același query e lent la momente aparent aleatoare.
  • E rapid când rulează din SSMS, lent din aplicație.
  • Restartarea serviciului SQL Server (golirea plan cache-ului) îl repară temporar.
  • Rularea DBCC FREEPROCCACHE sau sp_recompile îl repară temporar.
  • Adăugarea OPTION (RECOMPILE) îl face consistent.

Dacă oricare două dintre astea sunt adevărate, e aproape sigur parameter sniffing.

Rulează asta pe propria mașină

USE Runehold;
GO

-- Pregătește un dataset dezechilibrat: un client cu 10.000 de comenzi, restul cu 5-10
INSERT INTO Sales.Orders (CustomerId, OrderDate, Total, CountryCode, VatRate)
SELECT TOP (10000) 1, DATEADD(DAY, -v.n, GETDATE()), 49.99, 'NL', 0.2100
FROM (SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS n
      FROM sys.all_objects a CROSS JOIN sys.all_objects b) AS v;

-- Construiește o procedură simplă
CREATE OR ALTER PROCEDURE dbo.GetOrdersForCustomer
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderId, OrderDate, Total
    FROM Sales.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderDate DESC;
END;
GO

-- Golește plan cache-ul ca să pornești curat
DBCC FREEPROCCACHE;

-- Primul apel: CustomerId 1 (cel mare). Plan optimizat pentru 10.000 de rânduri.
EXEC dbo.GetOrdersForCustomer @CustomerId = 1;

-- Al doilea apel: CustomerId 2 (mic). Reutilizează planul mare. Poate fi lent sau OK.
EXEC dbo.GetOrdersForCustomer @CustomerId = 2;

-- Golește din nou
DBCC FREEPROCCACHE;

-- Acum primul apel cu clientul mic. Se compilează un plan diferit.
EXEC dbo.GetOrdersForCustomer @CustomerId = 2;
EXEC dbo.GetOrdersForCustomer @CustomerId = 1;

-- Fix cu RECOMPILE
CREATE OR ALTER PROCEDURE dbo.GetOrdersForCustomer
    @CustomerId INT
AS
BEGIN
    SET NOCOUNT ON;
    SELECT OrderId, OrderDate, Total
    FROM Sales.Orders
    WHERE CustomerId = @CustomerId
    ORDER BY OrderDate DESC
    OPTION (RECOMPILE);
END;

EXEC dbo.GetOrdersForCustomer @CustomerId = 1;  -- plan A
EXEC dbo.GetOrdersForCustomer @CustomerId = 2;  -- plan B (proaspăt)

Urmărește planurile de execuție pentru fiecare apel. Compară. Observă cum se comportă planul „greșit” când e reutilizat.

Lecția următoare: Query Store — mașina timpului pentru performanța query-urilor tale. Cum detectezi o regresie, găsești planul care era bun și îl forțezi.

Caută