Parameter sniffing – jeden plán pre rôzne hodnoty (PSP 2022+, OPPO 2025)

> [!TIP]
> Recept z [kuchárky exekučných plánov](script.php?id=64&s=exekucne-plany-kucharka-zacni-tu). Ukážky bežia v testovacej databáze [PlanLab](script.php?id=65&s=planlab-testovacia-databaza-pre-recepty-exekucnych-planov) – nikdy ich nespúšťaj na produkcii.

## Ako to vyzerá v pláne

![Časová os parameter sniffingu a hodnoty parametra v pláne](uploads/2026/11/plan-p4.svg "Plán z prvého volania sa použije pre všetky ďalšie hodnoty")

Na koreňovom SELECT v skutočnom pláne: **Parameter List** → *Parameter Compiled Value* a *Parameter Runtime Value*. Ak sa líšia a pri operátoroch je veľký rozdiel odhad / skutočnosť, ide o parameter sniffing.

## Čo to znamená

Pri prvom spustení procedúry alebo parametrizovaného dotazu optimalizátor „očuchá“ (*sniff*) hodnoty parametrov a plán pripraví **pre ne**. Plán sa uloží do cache a použije sa aj pre ďalšie hodnoty. Väčšinou je to výhoda (žiadna nová kompilácia). Pri **nerovnomerných dátach** (zákazník 1 má 100 000 objednávok, ostatní 10) je plán pre jednu hodnotu katastrofou pre inú – a „dnes rýchle, zajtra pomalé“ podľa toho, kto zavolal procedúru prvý po rekompilácii.

### Do hĺbky

- Týka sa procedúr, `sp_executesql` s parametrami, parametrizovaných dotazov z aplikácie (ORM). **Lokálne premenné** sa nesniffujú – použije sa priemerná hustota ([zlé odhady](script.php?id=84&s=zle-odhady-riadkov-odhad-vs-skutocnost-a-statistiky)).
- Plán vypadne z cache (a vznikne nový pre iné hodnoty) po update štatistík, reštarte, `sp_recompile`, zmene schémy, tlaku na pamäť – preto problém „náhodne“ prichádza a odchádza.
- **PSP optimization** (*Parameter Sensitive Plan*, SQL Server 2022, compat 160): pri rovnostných predikátoch s nerovnomernými dátami vytvorí *dispatcher* a až 3 varianty plánu podľa počtu riadkov. V pláne vidíš `PLAN PER VALUE(… QueryVariantID …)` a vlastnosť *Dispatcher*. Dostupné aj v Standard edícii.
- **Optional parameter plan optimization** (OPPO, SQL Server 2025, compat 170): pre vzor `WHERE col = @p OR @p IS NULL` vytvorí varianty pre NULL a nie-NULL hodnotu.
- [Adaptive Join](script.php?id=74&s=adaptive-join-hash-match-alebo-nested-loops-az-za-behu-2017) a memory grant feedback zmierňujú časť dôsledkov, ale neriešia výber indexu.

## Kedy je to v poriadku / kedy problém

| V poriadku | Problém |
|---|---|
| rovnomerné dáta, compiled ≈ runtime správanie | nerovnomerné dáta a výrazne rozdielne časy podľa parametra |
| PSP / OPPO vytvorili varianty | dotaz „sám od seba“ spomalí po update štatistík alebo reštarte |

## Ako si to vyskúšať

```sql
USE PlanLab;
GO
CREATE OR ALTER PROCEDURE dbo.Lab_OrdersByCustomer @CustomerID int
AS
SELECT OrderID, OrderDate, Amount, Note FROM dbo.Orders WHERE CustomerID = @CustomerID;
GO
SET STATISTICS IO ON;
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;   -- len plány PlanLab
EXEC dbo.Lab_OrdersByCustomer @CustomerID = 42;   -- plán pre malého zákazníka (seek + key lookup)
EXEC dbo.Lab_OrdersByCustomer @CustomerID = 1;    -- ten istý plán: 100 000 key lookupov

ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
EXEC dbo.Lab_OrdersByCustomer @CustomerID = 1;    -- plán pre veľkého (sken)
EXEC dbo.Lab_OrdersByCustomer @CustomerID = 42;   -- sken aj pre 10 riadkov

-- SQL Server 2022+: PSP optimization (compat 160)
-- ALTER DATABASE PlanLab SET COMPATIBILITY_LEVEL = 160;  potom zopakuj volania vyššie
GO
DROP PROCEDURE dbo.Lab_OrdersByCustomer;
```

Spusti s **Include Actual Execution Plan** (Ctrl+M). Na SELECT porovnaj *Parameter Compiled Value* a *Parameter Runtime Value*, v *Messages* logical reads.

## Čo s tým

1. **Potvrď diagnózu**: compiled ≠ runtime a rozdiel v odhade. Plány z Query Store ukážu, že dotaz má viac plánov s rôznym výkonom.
2. **Index**, ktorý robí obe varianty lacnými (napr. pokrývajúci – zmizne Key Lookup), je najlepšie riešenie.
3. `OPTION (RECOMPILE)` – nový plán pri každom volaní. Vhodné, keď dotaz nebeží stokrát za sekundu (cena kompilácie, nič v plan cache).
4. `OPTION (OPTIMIZE FOR (@p = …))` – plán pre typickú hodnotu; `OPTIMIZE FOR UNKNOWN` – priemerný odhad. Obe sú kompromis.
5. Rozdelenie logiky: iná procedúra / vetva pre „veľkých“ zákazníkov.
6. SQL Server 2022+: compat 160 (PSP); 2025: compat 170 (OPPO). Stabilný dobrý plán sa dá vynútiť cez [Query Store](script.php?id=95&s=plan-regression-novy-horsi-plan-a-vynutenie-cez-query-store).

## Súvisiace

- [Zlé odhady riadkov](script.php?id=84&s=zle-odhady-riadkov-odhad-vs-skutocnost-a-statistiky)
- [Key Lookup a RID Lookup](script.php?id=70&s=key-lookup-a-rid-lookup-dohladanie-chybajucich-stlpcov)
- [Plan regression a Query Store](script.php?id=95&s=plan-regression-novy-horsi-plan-a-vynutenie-cez-query-store)
- [Adaptive Join](script.php?id=74&s=adaptive-join-hash-match-alebo-nested-loops-az-za-behu-2017)
- [Najdrahšie stored procedures (plan cache)](script.php?id=30&s=najdrahsie-stored-procedures-plan-cache)
- [Query Store – regresie](script.php?id=26&s=query-store-regresie-24-h-vs-predoslych-24-h)

## Zdroje

- [Microsoft Learn – Parameter Sensitive Plan optimization](https://learn.microsoft.com/sql/relational-databases/performance/parameter-sensitive-plan-optimization)
- [Microsoft Learn – Optional parameter plan optimization](https://learn.microsoft.com/sql/relational-databases/performance/optional-parameter-optimization)
- [Microsoft Learn – Query hints (Transact-SQL)](https://learn.microsoft.com/sql/t-sql/queries/hints-transact-sql-query)
- [Erik Darling – blog (plány, memory grants, parameter sniffing)](https://erikdarling.com/)
- [Brent Ozar – blog a nástroje First Responder Kit](https://www.brentozar.com/)

_Texty sú vlastné, tvrdenia overené proti uvedeným zdrojom (11/2026). T-SQL je staticky skontrolovaný, nie spustený na živom serveri – vyskúšaj ho najprv v PlanLab._