Narcis Miclaus

6 min di lettura

SQL Server, dalle fondamenta Lezione 27 / 40

Parameter sniffing: il bug, la feature, il fix

Perché la query è veloce in SSMS e lenta nell'app. OPTIMIZE FOR, RECOMPILE, plan forcing in Query Store, e le feature recenti di SQL Server che rendono il problema meno doloroso.

Il tuo support engineer apre un ticket: “L’endpoint ‘find customer by email’ improvvisamente va in timeout per alcuni utenti ma non per altri.”

Esegui la query sottostante in SSMS. Istantanea. Ti arrendi nel tentativo di riprodurla in locale. L’engineer si arrende e riavvia il server applicativo. Il problema sparisce per tre giorni, poi torna.

Questo è il parameter sniffing. La lezione di oggi è quella che lo risolve.

Il meccanismo, in un paragrafo

SQL Server mette in cache i piani di esecuzione per le query parametrizzate. La prima volta che la query gira, l’ottimizzatore costruisce un piano usando i valori specifici dei parametri che vede. Quel piano viene poi riutilizzato per ogni chiamata successiva, indipendentemente dai valori dei parametri. Quando la prima chiamata aveva preso un valore “estremo” — un cliente con 20.000 ordini, diciamo — il piano è ottimizzato per quel caso. Una chiamata successiva con un cliente normale riusa il piano del caso estremo, e va selvaggiamente storto.

Quello è il bug. Si chiama anche “feature” perché per l’80% delle query lo sniffing produce un ottimo piano e risparmia il costo di ottimizzazione. Per l’altro 20%, è il motivo per cui non riesci a riprodurre un problema di produzione in dev.

La demo canonica

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

-- Prima chiamata: un cliente "piccolo"
EXEC Sales.GetOrdersForCustomer @CustomerId = 123;
-- Piano: nested loop, key lookup. Ottimo per ~5 righe.

-- Chiamata successiva: un cliente "gigante" con 50.000 ordini
EXEC Sales.GetOrdersForCustomer @CustomerId = 42;
-- Riusa il piano nested-loop. Fa 50.000 key lookup. Va in timeout.

-- Un altro utente SSMS esegue la query direttamente
SELECT * FROM Sales.Orders WHERE CustomerId = 42;
-- Compilazione fresca. L'ottimizzatore vede @CustomerId = 42, costruisce un piano migliore (scan + filter).
-- Veloce.

Ecco perché “è veloce in SSMS e lento nell’app” — un nuovo run da SSMS ottiene un piano fresco; l’app sta riusando il piano in cache di ieri.

Fix, dal meno al più invasivo

Fix 1: OPTION (RECOMPILE)

Forza un piano fresco a ogni chiamata:

SELECT * FROM Sales.Orders WHERE CustomerId = @CustomerId
OPTION (RECOMPILE);
  • Pro: usa sempre il piano migliore per il parametro corrente.
  • Contro: costo di compilazione a ogni chiamata. Per una stored proc chiamata migliaia di volte al minuto, è CPU che non ti puoi permettere.

Usa RECOMPILE su query dove lo squilibrio dei parametri è grande e il volume di chiamate è basso. Report, dashboard ad-hoc, strumenti di amministrazione — calzano a pennello. L’endpoint caldo di order-lookup chiamato 5.000 volte al minuto — non è il posto.

Fix 2: OPTIMIZE FOR UNKNOWN

Dì all’ottimizzatore di usare la densità media invece dei valori sniffati:

SELECT * FROM Sales.Orders WHERE CustomerId = @CustomerId
OPTION (OPTIMIZE FOR UNKNOWN);
  • Pro: nessun costo di compilazione per chiamata. Piano generico.
  • Contro: il piano generico è mediocre sia per i casi piccoli che per quelli grandi. Scambi il problema “ottimo per uno, terribile per l’altro” con “mediocre per entrambi.”

Utile quando la distribuzione del parametro è uniforme e nessun valore è un outlier. Altrimenti per lo più un workaround storico.

Fix 3: OPTIMIZE FOR (@p = 'qualche valore')

Forza un valore specifico al momento della compilazione del piano:

SELECT * FROM Sales.Orders WHERE CustomerId = @CustomerId
OPTION (OPTIMIZE FOR (@CustomerId = 1));
-- Assume che CustomerId 1 sia "rappresentativo del caso tipico"
  • Pro: piano deterministico, ottimizzato per il valore che hai scelto.
  • Contro: onere di manutenzione. Cosa è “tipico” può cambiare nel tempo.

Usato quando sai che un valore specifico produce la forma di piano che vuoi per la maggioranza delle chiamate.

Fix 4: parameter masking (l’hack vecchio)

Assegna il parametro a una variabile locale dentro la proc:

CREATE PROCEDURE Sales.GetOrdersForCustomer
    @CustomerId INT
AS
BEGIN
    DECLARE @c INT = @CustomerId;    -- la variabile locale rompe lo sniffing
    SELECT * FROM Sales.Orders WHERE CustomerId = @c;
END;

L’ottimizzatore non può “sniffare” una variabile locale (il suo valore non è noto al momento della compilazione), quindi usa la densità media. Effettivamente lo stesso di OPTIMIZE FOR UNKNOWN.

Questo trucco è ovunque nel vecchio codice di produzione. Funziona, è brutto. Preferisci l’hint esplicito OPTIMIZE FOR UNKNOWN.

Fix 5: plan forcing in Query Store (quello moderno)

Da SQL Server 2016, Query Store traccia ogni query, ogni piano, ogni metrica di runtime. Se trovi un piano che funziona bene, puoi fissarlo: tutte le chiamate future della stessa query usano quel piano indipendentemente dallo sniffing.

Copriremo Query Store come si deve nella lezione 28. Anteprima:

-- Forza il piano identificato come plan_id 42 per la query 19
EXEC sp_query_store_force_plan @query_id = 19, @plan_id = 42;

Questo è il modo production-safe per dire “questo piano è buono, usalo finché non dico altrimenti.” Niente cambi di codice, niente hint. Reversibile con sp_query_store_unforce_plan.

Fix 6: logica con branch (l’opzione nucleare)

Quando i parametri ricadono in range chiaramente diversi (“clienti piccoli” vs “clienti giganti”), puoi fare branch manualmente:

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;

Due proc diverse, ognuna col suo piano in cache tarato sul suo caso. Brutto. Usato solo quando gli altri fix non bastano.

Parameter Sensitive Plan optimization (SQL Server 2022+)

SQL Server 2022 ha introdotto la Parameter Sensitive Plan (PSP) optimization — l’ottimizzatore tiene più piani per la stessa query, scelti in base al valore del parametro. Risolve il bug a livello di motore.

Abilitato automaticamente sui database con compatibility level 160 (SQL 2022) o superiore. Il motore rileva i parametri sensibili allo squilibrio e mette in cache fino a tre piani per query. Quello giusto viene scelto a runtime in base al “bucket” del parametro (piccolo, medio, grande).

Se sei su SQL Server 2022 con compat 160, la maggior parte dei problemi di parameter sniffing che hanno tormentato il 2012-2019 semplicemente… spariscono. Non tutti — è un’euristica, non magia — ma una frazione significativa.

Verifica se la PSP sta facendo qualcosa per le tue query:

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;

Riconoscere il sintomo in libertà

“Parameter sniffing” è la risposta quando:

  • La stessa query è lenta in momenti apparentemente casuali.
  • È veloce quando lanciata da SSMS, lenta dall’applicazione.
  • Riavviare il servizio SQL Server (svuotare il plan cache) la sistema temporaneamente.
  • Eseguire DBCC FREEPROCCACHE o sp_recompile la sistema temporaneamente.
  • Aggiungere OPTION (RECOMPILE) la rende consistente.

Se due qualunque di queste sono vere, è quasi sicuramente parameter sniffing.

Esegui questo sulla tua macchina

USE Runehold;
GO

-- Imposta un dataset sbilanciato: un cliente con 10.000 ordini, gli altri con 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;

-- Costruisci una procedura semplice
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

-- Svuota il plan cache per partire pulito
DBCC FREEPROCCACHE;

-- Prima chiamata: CustomerId 1 (quello grosso). Piano ottimizzato per 10.000 righe.
EXEC dbo.GetOrdersForCustomer @CustomerId = 1;

-- Seconda chiamata: CustomerId 2 (piccolo). Riusa il piano grande. Potrebbe essere lento o no.
EXEC dbo.GetOrdersForCustomer @CustomerId = 2;

-- Svuota di nuovo
DBCC FREEPROCCACHE;

-- Adesso la prima chiamata è il cliente piccolo. Piano diverso compilato.
EXEC dbo.GetOrdersForCustomer @CustomerId = 2;
EXEC dbo.GetOrdersForCustomer @CustomerId = 1;

-- Fix con 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;  -- piano A
EXEC dbo.GetOrdersForCustomer @CustomerId = 2;  -- piano B (fresco)

Guarda i piani di esecuzione per ogni chiamata. Confronta. Osserva come si comporta il piano “sbagliato” quando viene riusato.

Prossima lezione: Query Store — la macchina del tempo per le performance delle tue query. Come rilevare una regressione, trovare il piano che era buono, e forzarlo.

Cerca