SQL: jak wykrywać „parameter sniffing” w produkcji i stabilizować plany bez przepisywania zapytań

1. Czym jest parameter sniffing i dlaczego boli w produkcji

Parameter sniffing to zjawisko, w którym SQL Server (i inne silniki z podobnym mechanizmem cache’owania planów) tworzy plan wykonania zapytania na podstawie konkretnych wartości parametrów użytych przy pierwszej kompilacji, a następnie ponownie używa tego planu dla kolejnych wywołań — nawet jeśli późniejsze wartości parametrów mają zupełnie inną selektywność i prowadzą do innego, optymalnego sposobu wykonania.

W praktyce oznacza to, że raz „trafiony” plan może być świetny dla jednego zestawu parametrów, ale bardzo kosztowny dla innego. Problem nie polega na tym, że silnik „źle liczy” — tylko na tym, że jeden uśredniony plan jest czasem z natury rzeczy niewystarczający, gdy rozkład danych i wartości parametrów są skrajnie różne.

Mechanizm: skąd bierze się niestabilność

Żeby ograniczać koszt kompilacji, silnik bazodanowy przechowuje plany w pamięci podręcznej. Gdy zapytanie jest parametryzowane (np. procedura składowana albo zapytanie z parametrami z aplikacji), kompilator w momencie tworzenia planu „widzi” konkretne wartości parametrów i używa ich do oszacowania kardynalności (ile wierszy będzie przetwarzanych) oraz doboru strategii dostępu do danych. To „powiązanie” planu z parametrami kompilacji jest właśnie sednem parameter sniffingu.

Najbardziej boli to wtedy, gdy istnieją co najmniej dwa sensowne, ale różne plany dla tego samego zapytania i każdy z nich jest dobry dla innego zakresu parametrów. Wtedy kolejność pierwszych wywołań po kompilacji (lub po „wyczyszczeniu” planu) może zdecydować, czy system będzie działał płynnie, czy zacznie się dławić.

Kiedy parameter sniffing występuje najczęściej

• Procedury składowane i parametryzowane zapytania wywoływane z aplikacji, gdzie plan jest chętnie współdzielony pomiędzy wywołaniami.
• Duża zmienność parametrów: raz filtr zwraca pojedynczy rekord, innym razem setki tysięcy.
• Nierówny rozkład danych (silna skośność): „popularne” wartości występują ekstremalnie często, a inne są rzadkie.
• Zapytania z opcjonalnymi filtrami (parametr może być podany albo nie) i tym samym mocno zmienia się selektywność.
• Po zdarzeniach wymuszających ponowną kompilację (np. po aktualizacji statystyk, zmianach schematu, restarcie usługi): pierwszy „sniff” po takim zdarzeniu potrafi ustawić system na dobry albo zły tor.

Dlaczego to boli w produkcji

W środowisku produkcyjnym parameter sniffing jest szczególnie dotkliwy, bo zmienność obciążenia i danych jest realna: różni użytkownicy, różne okresy dnia, kampanie, batch’e, integracje, a do tego stale rosnąca baza. W efekcie:

• Losowość i trudna powtarzalność: problem może pojawiać się tylko dla niektórych parametrów i tylko wtedy, gdy „zły” plan jest akurat w cache.
• Wrażliwość na chwilowe warunki: to, co było szybkie rano, może spowolnić po południu, mimo że kod się nie zmienił.
• Efekt domina: jeden nieoptymalny plan potrafi podnieść czasy odpowiedzi, zająć CPU/IO, wydłużyć blokady i kolejki, a finalnie spowolnić także inne, niezwiązane transakcje.
• Ryzyko regresji po „niewinnych” zmianach: nawet bez przepisywania zapytań, zmiany w danych, statystykach czy środowisku mogą spowodować, że kolejna kompilacja „złapie” inną wartość i wybierze inny plan.

Typowe scenariusze, w których plan „pasuje” tylko czasem

• Wyszukiwanie po atrybucie o wysokiej skośności: np. filtr po statusie, kategorii, regionie czy typie, gdzie jedna wartość dominuje w danych.
• Raportowanie vs. transakcje: to samo zapytanie bywa użyte zarówno do pobrania pojedynczego rekordu (ścieżka „OLTP”), jak i do wygenerowania większego zestawu (ścieżka „raportowa”).
• Parametry dat: zapytania po zakresie czasu, gdzie czasem obejmują dzień, a czasem miesiące; rozmiar zakresu drastycznie zmienia koszt.
• Opcjonalne filtry i „tryby” działania: jedna procedura obsługuje wiele wariantów logiki, a parametr przełącza selektywność i kształt danych wejściowych.

Warto podkreślić, że parameter sniffing nie jest „błędem” silnika — to konsekwencja optymalizacji pod wydajność (reuse planu) w połączeniu z faktem, że niektóre zapytania nie mają jednego planu, który byłby równie dobry dla wszystkich parametrów. Problem zaczyna się wtedy, gdy ta nieprzewidywalność przekłada się na odczuwalne wahania czasu odpowiedzi i zasobów w produkcji.

2. Symptomy i wzorce problemu

„Parameter sniffing” rzadko wygląda jak stała, przewidywalna degradacja. Częściej objawia się niestabilnością: ten sam endpoint, job lub procedura potrafi działać raz świetnie, a innym razem dramatycznie wolno, mimo że infrastruktura i kod się nie zmieniły. Podczas szkoleń Cognity ten temat wraca regularnie – dlatego zdecydowaliśmy się go omówić również tutaj. Poniżej są najczęstsze symptomy i powtarzalne wzorce, które w praktyce odróżniają ten problem od typowych braków indeksów czy permanentnie zbyt wolnych zapytań.

Niestabilne czasy wykonania „tego samego” zapytania

Najbardziej charakterystyczny sygnał to duży rozrzut czasów dla jednego zapytania (lub tej samej procedury) przy podobnym obciążeniu systemu. W logach aplikacji lub monitoringu widać „zęby piły”: większość wywołań mieści się w oczekiwanym SLA, ale co jakiś czas pojawiają się pojedyncze lub serie wywołań wolniejsze o rząd wielkości.

• Wzorzec „czasem szybkie, czasem koszmar”: 95% wykonań jest akceptowalne, a 5% ma ekstremalne czasy.
• Wzorzec „zależy od użytkownika/klienta/regionu”: wolne są wywołania z konkretną wartością parametru (np. popularny klient z dużą liczbą rekordów), a inne są szybkie.
• Wzorzec „po przerwie jest gorzej”: po restarcie usługi, failoverze lub czyszczeniu cache pierwsze wywołania potrafią ustawić plan, który później „ciągnie” wszystkie kolejne.

Skoki CPU i/lub IO bez proporcjonalnego wzrostu ruchu

Parameter sniffing często przekłada się na nagłe skoki zużycia CPU albo gwałtowny wzrost odczytów logicznych/fizycznych, które nie korelują wprost z liczbą zapytań. System ma tyle samo requestów, ale jedno z nich zaczyna wykonywać znacznie cięższy plan.

• CPU spike: rośnie czas CPU na pojedyncze wykonanie i/lub rośnie równoległość, bo plan próbuje „ratować się” większą pracą procesora.
• IO spike: pojawia się więcej skanów, większe odczyty z pamięci i dysku, a bufor cache zaczyna być wypierany przez jedno dominujące zapytanie.
• „Zatykanie” zasobów: jedno zapytanie z „niewłaściwym” planem podbija obciążenie tak, że inne, poprawne zapytania zaczynają cierpieć wtórnie.

Różne plany dla „tego samego” zapytania lub jeden plan, który pasuje tylko czasem

W praktyce spotkasz dwa warianty:

• Plan „uniwersalny” tylko z nazwy: w cache jest jeden plan, ale sprawdza się świetnie dla jednych wartości parametrów i fatalnie dla innych (bo został skompilowany pod „nietypowy” przypadek).
• Plan zmienny: w różnych momentach pojawiają się różne plany dla tego samego wzorca zapytania (np. po rekompilacji, po odświeżeniu statystyk, po restarcie). Każdy z nich ma inną charakterystykę kosztów i zasobów.

Typowy sygnał operacyjny to sytuacja, w której zespół mówi: „przecież to zapytanie czasem jest szybkie”, a jednocześnie incydenty wydajnościowe wracają cyklicznie bez oczywistych zmian w kodzie.

Regresje po wdrożeniach, zmianach danych lub utrzymaniu — bez zmian w samym zapytaniu

Parameter sniffing bywa mylony z „wdrożenie spowolniło bazę”, bo regresja często pojawia się w tym samym czasie co deploy. Różnica polega na tym, że źródłem nie musi być nowy SQL, tylko zmiana warunków kompilacji i doboru planu.

• Regresja po wdrożeniu: nowe uruchomienia procesu/aplikacji powodują inny rozkład pierwszych parametrów, przez co plan „ustawia się” na gorszy wariant.
• Regresja po odświeżeniu statystyk/indeksów: to może zmienić estymacje i spowodować wybór innego planu, który nie jest stabilny dla całego zakresu parametrów.
• Regresja po zmianie wolumenu danych: wzrost tabel, sezonowość lub nierównomierny rozkład danych sprawiają, że wartości parametrów częściej trafiają w skrajne przypadki.

Wzorce „kiedy boli najbardziej”

Choć parameter sniffing może dotknąć niemal każdego zapytania, najczęściej „wychodzi na wierzch” w powtarzalnych sytuacjach:

• Duża nierównomierność danych: niektóre wartości parametru zwracają promil wierszy, inne miliony.
• Zapytania o podwójnej naturze: ten sam SQL bywa używany raz do „szukania jednego rekordu”, a raz do „generowania dużej listy” (różne przypadki biznesowe pod tym samym interfejsem).
• Filtry opcjonalne: parametry czasem są „wąskie”, a czasem praktycznie nie filtrują, co radykalnie zmienia opłacalność strategii wykonania.
• Różne rozmiary partii: jedne wywołania przetwarzają mały zakres, inne duży (np. różne okna czasu).

Jak odróżnić to od innych problemów (na poziomie symptomów)

Bez wchodzenia w diagnostykę, można przyjąć praktyczną heurystykę:

• Jeśli zapytanie jest zawsze wolne — częściej winny jest brak indeksu, zła logika lub stały problem z IO/CPU.
• Jeśli zapytanie jest czasem bardzo szybkie, czasem bardzo wolne — rośnie prawdopodobieństwo problemu z doborem planu zależnym od parametrów.
• Jeśli incydenty pojawiają się falami i „znikają same” po pewnym czasie lub po operacjach utrzymaniowych — to typowy sygnał niestabilności planu.

Te symptomy nie są dowodem samym w sobie, ale stanowią mocne wskazówki, kiedy warto skupić dalsze działania na obserwacji planów, parametrów i ich wpływu na zachowanie zapytań.

3. Diagnostyka: szybka triada (Query Store, plan cache, DMVs) i jak interpretować wyniki

Gdy podejrzewasz parameter sniffing, najszybciej dojdziesz do konkretów, łącząc trzy źródła: Query Store (historia i regresje), plan cache (to, co jest aktualnie w pamięci) oraz DMVs (bieżąca telemetria: czekania, zużycie zasobów, charakter uruchomień). Każde z nich odpowiada na inne pytanie, a dopiero razem tworzą wiarygodny obraz.

3.1 Query Store: znajdź zapytania z wieloma planami i dużą zmiennością

W kontekście parameter sniffing Query Store jest najwygodniejszy, bo pozwala zobaczyć, czy to samo query_id miało w czasie różne plany oraz jak te plany przekładały się na metryki (czas, CPU, odczyty). Szukasz przede wszystkim:

• Wielu planów przypiętych do jednego zapytania (często sygnał niestabilności).
• Dużej rozpiętości czasów trwania / CPU / logical reads między planami.
• Regresji po zdarzeniu (wdrożenie, aktualizacja statystyk, przebudowa indeksów, restart) — skok metryk skorelowany ze zmianą planu.

Minimalny punkt zaczepienia: lista zapytań, które miały więcej niż jeden plan w obserwowanym okresie:

-- Query Store: zapytania z wieloma planami (punkt startowy)
SELECT TOP (50)
    qsq.query_id,
    COUNT(DISTINCT qsp.plan_id) AS plan_count
FROM sys.query_store_query AS qsq
JOIN sys.query_store_plan  AS qsp ON qsp.query_id = qsq.query_id
GROUP BY qsq.query_id
HAVING COUNT(DISTINCT qsp.plan_id) > 1
ORDER BY plan_count DESC;

Jak interpretować: samo „>1 plan” nie oznacza jeszcze problemu (kompilacje mogą się zmieniać z wielu powodów), ale jeśli do tego dochodzi wyraźnie gorszy profil zasobów jednego z planów lub duża zmienność w czasie, to masz dobry trop do dalszej weryfikacji.

3.2 Plan cache: sprawdź, co jest aktywne i czy występują warianty planu

Plan cache odpowiada na pytanie: co SQL Server uruchamia teraz i w jakiej formie to jest skeszowane? W diagnostyce parameter sniffing interesują Cię dwa wzorce:

• Ten sam tekst zapytania (lub ta sama procedura) ma różne wpisy w cache (np. z powodu różnych ustawień SET, różnych parametrów, różnych „kształtów” planu).
• Jeden plan jest w cache, ale metryki wykonania sugerują, że dla części wywołań jest on skrajnie nietrafiony (długi ogon czasów).

W praktyce plan cache jest najbardziej użyteczny, gdy:

• Problem jest aktywny teraz (chcesz szybko zobaczyć plan i kontekst).
• Query Store nie jest dostępny albo jest wyłączony dla bazy.

Jak interpretować: jeśli widzisz kilka planów dla „tego samego” zapytania, nie zakładaj automatycznie sniffingu. Upewnij się, że to naprawdę to samo zapytanie (ten sam obiekt/procedura) i że różnice nie wynikają wyłącznie z odmiennych ustawień sesji czy kompilacji wymuszonej. Plan cache traktuj jako dowód „co jest”, nie jako pełną historię „co było”.

3.3 DMVs: oceń wpływ i potwierdź, że to nie tylko ogólna presja zasobów

DMVs są przydatne do szybkiej odpowiedzi na pytanie: czy obserwowany problem to lokalna niestabilność konkretnego zapytania, czy raczej ogólny stan serwera (CPU/IO/locki)? Dla podejrzenia parameter sniffing zwróć uwagę na:

• Skoki CPU przypisane do pojedynczego zapytania/procedury (jeśli potrafisz je zidentyfikować po statystykach wykonania).
• Nadmierne odczyty logiczne (często efekt planu, który „przewiduje” inną selektywność niż rzeczywista).
• Czekania wskazujące na wtórny efekt złego planu (np. przeciążone IO, blokady przez długie skany) — sygnał, że zły plan promieniuje na resztę systemu.

Przykładowy szybki podgląd „najcięższych” zapytań z cache według CPU (orientacyjnie):

-- DMVs: zapytania o wysokim łącznym CPU (migawka)
SELECT TOP (20)
    qs.total_worker_time / 1000 AS total_cpu_ms,
    qs.execution_count,
    (qs.total_worker_time / NULLIF(qs.execution_count,0)) / 1000 AS avg_cpu_ms,
    (qs.total_logical_reads / NULLIF(qs.execution_count,0)) AS avg_logical_reads,
    SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
        ((CASE qs.statement_end_offset WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)+1) AS statement_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY qs.total_worker_time DESC;

Jak interpretować: DMVs powiedzą Ci, które zapytania są kosztowne i jakim zasobem „płacą”, ale nie pokażą same z siebie, dlaczego plan jest niestabilny. Jeśli jednak widzisz zapytanie z wysokim średnim CPU/reads i jednocześnie z Query Store/plan cache wynika, że ma alternatywne plany albo duże wahania metryk, masz spójny sygnał do głębszego porównania planów.

3.4 Szybka interpretacja wyników: kiedy podejrzenie jest mocne

• Mocny sygnał: Query Store pokazuje ten sam query_id z kilkoma planami, a jeden z nich ma wyraźnie gorsze CPU/IO/czas w tych samych warunkach obciążenia.
• Mocny sygnał: problem występuje „falami”, a w tych samych oknach czasowych rośnie koszt konkretnego zapytania (DMVs) i równolegle widać zmianę planu (Query Store / cache).
• Słabszy sygnał: tylko wysokie zużycie zasobów bez śladu zmienności planu — to może być po prostu ciężkie zapytanie albo presja środowiska, niekoniecznie sniffing.

Ta triada ma Cię doprowadzić do krótkiej listy kandydatów: konkretne zapytanie/procedura oraz konkretne plany, które korelują z „dobrym” i „złym” zachowaniem. Dopiero potem ma sens wchodzić w porównywanie planów, parametrów i kardynalności.

4. Potwierdzanie przyczyny: porównanie planów i parametrów, identyfikacja „dobrego” i „złego” planu, analiza statystyk i kardynalności

Po wstępnej diagnostyce kluczowe jest potwierdzenie, że to faktycznie parameter sniffing, a nie np. blokady, zmiana danych, utrata indeksu czy inny typ regresji. W praktyce chodzi o udowodnienie zależności: „ten sam tekst zapytania” + „inne wartości parametrów” → „inny (albo ten sam) plan wykonania, który działa diametralnie inaczej”. W czasie szkoleń Cognity ten temat bardzo często budzi ożywione dyskusje między uczestnikami, bo w realnych systemach łatwo pomylić sniffing z innymi źródłami regresji.

4.1. Porównanie planów: co zestawiać, żeby mieć twardy dowód

Weryfikacja zwykle polega na zestawieniu dwóch wykonań (albo dwóch planów): jednego, które jest szybkie („dobry plan”), oraz drugiego, które jest wolne („zły plan”). Porównuj zawsze te elementy, które bezpośrednio wskazują na wpływ parametrów na plan:

• Identyczność zapytania: ten sam tekst/ten sam query_id (jeśli korzystasz z Query Store) lub ten sam query_hash/plan_hash (jeśli pracujesz na cache/DMV).
• Różne wartości parametrów przy kompilacji: wartości „sniffowane”/parametry kompilacji vs wartości w czasie wykonania.
• Różnice w planie fizycznym: inne operatory (np. Seek vs Scan, Nested Loops vs Hash Join, inna kolejność joinów).
• Różnice w estymatach: rozjazd między Estimated Rows a Actual Rows na kluczowych operatorach (typowy ślad błędnej kardynalności).
• Różnice w kosztach dominujących: nagły wzrost odczytów (logical reads), CPU, spilli do tempdb, ostrzeżenia w planie.

4.2. Jak rozpoznać „dobry” i „zły” plan (bez fetyszyzowania jednego operatora)

„Dobry plan” to nie ten, który wygląda „ładnie”, tylko ten, który jest stabilnie dobry dla większości typowych parametrów albo przynajmniej dla parametrów krytycznych biznesowo. „Zły plan” to taki, który w praktyce generuje nieakceptowalne opóźnienia lub koszty zasobów.

Typowe pary planów, które wskazują na parameter sniffing:

Uwaga: parameter sniffing nie zawsze oznacza dwa różne plany. Czasem jest jeden plan w cache, ale jest on skompilowany pod „nietypowy” parametr i dla reszty świata działa źle. Wtedy potwierdzeniem jest zestawienie: ten sam plan + inne parametry wykonania → dramatycznie różne czasy/IO.

4.3. Parametry: co dokładnie sprawdzać, żeby nie pomylić przyczyny

Aby wiarygodnie potwierdzić sniffing, potrzebujesz wskazać wartości, które wpłynęły na kompilację. W zależności od narzędzia będą to:

• Parameter Compiled Value (wartość użyta podczas kompilacji planu).
• Parameter Runtime Value (wartość w danym wykonaniu).
• Zakres i rozkład wartości w danych (czy parametr trafia w „ogon” dystrybucji).

Najczęstszy mechanizm: zapytanie zostaje skompilowane dla parametru bardzo selektywnego (np. zwraca 1 wiersz), przez co powstaje plan „pod igłę”. Potem przychodzi parametr nie-selektywny (np. zwraca milion wierszy), a plan pozostaje ten sam — i wydajność się załamuje. Albo odwrotnie: plan pod szeroki zakres danych jest wolny dla małych wyników.

4.4. Kardynalność i statystyki: gdzie szukać dowodu „dlaczego plan się myli”

Parameter sniffing boli, bo optymalizator wybiera plan na podstawie estymat. Jeśli estymaty są sensowne dla jednego parametru, ale fatalne dla innego, różnice w planie są naturalną konsekwencją. W tej fazie potwierdzania skup się na trzech rzeczach:

• Rozjazd Estimated vs Actual na operatorach „ustawiających” dalszą część planu (pierwszy filtr, pierwszy join, operator wiodący).
• Dystrybucja danych w statystykach: czy wartości parametru są nierównomiernie rozłożone (skew), czy występują „hot values”.
• Świeżość i reprezentatywność statystyk: czy statystyki nie są przestarzałe, czy sampling nie zgubił ogonów rozkładu.

W praktyce „dowodem” bywa sytuacja, w której dla jednego parametru estymata jest trafna, a dla drugiego kompletnie nie — mimo że to to samo zapytanie. Jeśli dodatkowo widzisz, że problem dotyczy kolumn z nierówną dystrybucją, to jest to klasyczny kontekst dla sniffingu.

4.5. Minimalny przykład do porównania (jako wzorzec myślenia)

Poniższy szkic nie ma „rozwiązywać” problemu, tylko pokazać, jak zbiera się materiał do porównania: to samo zapytanie, różne wartości parametru, a potem analiza planów i estymat.

DECLARE @p int;

-- Wykonanie A: parametr selektywny
SET @p = 123;
EXEC dbo.TwojaProcedura @Param = @p;

-- Wykonanie B: parametr nieselektywny
SET @p = 999999;
EXEC dbo.TwojaProcedura @Param = @p;

-- Następnie porównujesz:
-- 1) plan (operatory i ich kolejność)
-- 2) Parameter Compiled Value vs Runtime Value
-- 3) Estimated Rows vs Actual Rows na kluczowych operatorach

4.6. Kiedy to jeszcze nie jest parameter sniffing

Na koniec tej fazy warto wykluczyć częste fałszywe tropy. Jeśli „zły plan” koreluje nie z parametrem, a z warunkami środowiska, przyczyna może leżeć gdzie indziej. Najczęstsze sygnały ostrzegawcze:

• Blokady/wait statistics dominują nad CPU/IO (czas rośnie, ale plan i IO są podobne).
• Równoległość i wahania DOP/degree of parallelism powodują skoki czasu mimo podobnych estymat.
• Zmiana danych/indeksów lub masowy load powoduje inną selektywność niż „wczoraj”, a problem dotyka wszystkich parametrów podobnie.

Jeżeli jednak masz: ten sam query, dwa zestawy parametrów, wyraźnie różne estymaty i/lub różne plany oraz powtarzalny wzorzec (konkretne wartości prawie zawsze „psują” wykonanie), to masz praktyczne potwierdzenie, że problemem jest parameter sniffing i możesz przejść do doboru sposobu stabilizacji.

5. Techniki stabilizacji bez przepisywania zapytań

Gdy źródłem problemu jest „parameter sniffing”, celem nie musi być idealny plan dla każdego zestawu parametrów, tylko stabilny i przewidywalny plan w produkcji. Poniżej zebrane są techniki, które zwykle da się wdrożyć bez zmiany logiki zapytania (często wystarcza zmiana sposobu kompilacji lub dobór planu), wraz z ich typowymi zastosowaniami i ryzykami.

OPTIMIZE FOR: „jeden plan, ale pod kontrolą”

OPTIMIZE FOR służy do ustabilizowania planu przez zasugerowanie optymalizatorowi, dla jakiej wartości parametru ma dobrać plan. To podejście jest użyteczne, gdy wiesz, że problem bierze się z nietypowego pierwszego uruchomienia (rzadki parametr), a większość obciążenia to „typowe” wartości.

• OPTIMIZE FOR (@p = ...) – gdy znasz reprezentatywną wartość.
• OPTIMIZE FOR UNKNOWN – gdy chcesz, by optymalizator oparł plan o uśrednione założenia (często zmniejsza wahania, ale rzadko daje plan „idealny”).

-- przykład (schematycznie)
SELECT ...
FROM ...
WHERE Col = @p
OPTION (OPTIMIZE FOR (@p = 42));

-- wariant bardziej „uśredniony”
SELECT ...
FROM ...
WHERE Col = @p
OPTION (OPTIMIZE FOR UNKNOWN);

To technika kompromisowa: zwykle zmniejsza rozrzut czasów, ale może zwiększyć koszt skrajnych przypadków.

RECOMPILE: plan dopasowany do parametrów na bieżąco

RECOMPILE rozwiązuje problem wprost: zamiast reuse planu skompilowanego „pod pierwszy parametr”, zapytanie/procedura jest kompilowana dla bieżących wartości. Najczęściej stosuje się, gdy różnice w selektywności parametrów są duże i nie da się znaleźć jednego stabilnego planu.

• OPTION (RECOMPILE) – dotyczy pojedynczego zapytania.
• WITH RECOMPILE na procedurze – dotyczy całej procedury.

SELECT ...
FROM ...
WHERE Col = @p
OPTION (RECOMPILE);

Najważniejszy trade-off to koszt kompilacji. Przy rzadkich/średnio częstych wywołaniach bywa świetny, przy bardzo wysokiej częstotliwości może podbić CPU.

Plan guides: gdy nie wolno ruszyć kodu

Plan guides pozwalają narzucić hinty dla zapytania z aplikacji bez zmiany jej kodu (lub gdy zmiana wymaga długiego procesu). To „narzędzie administracyjne” – przydatne, ale wymagające dyscypliny, bo jest wrażliwe na różnice w tekście zapytania i potrafi być zapomniane po czasie.

• Najczęstsze zastosowanie: dodanie OPTION(...) (np. OPTIMIZE FOR / RECOMPILE) do konkretnego statementu, którego nie możesz edytować.
• Warto traktować jako rozwiązanie tymczasowe lub „ostatniej szansy”, gdy inne ścieżki są zablokowane.

Hinty z umiarem: precyzyjne, ale łatwo „przyspawać” złą decyzję

Hinty mogą stabilizować zachowanie (np. wymuszenie indeksu lub rodzaju złączenia), ale są najbardziej ryzykowną formą „stabilizacji”, bo ingerują w decyzje optymalizatora, które mogą się zmieniać wraz z danymi, statystykami i wersją silnika.

• Stosuj, gdy masz jasny dowód, że konkretna decyzja optymalizatora jest systematycznie błędna w Twoich danych.
• Unikaj „hintowania na ślepo”. Stabilność planu nie może oznaczać utrwalenia planu, który jest stabilnie zły.
• Jeżeli już, preferuj hinty o mniejszym „promieniu rażenia” (np. kompilacyjne jak OPTIMIZE FOR) nad twardymi wymuszeniami operatorów.

Aktualizacja statystyk i prace na indeksach: usuń przyczynę błędnych estymacji

Choć parameter sniffing jest kojarzony z „pechem pierwszego parametru”, bardzo często problem pogłębiają nietrafione estymacje kardynalności. A te wynikają m.in. ze starych statystyk, nierównomiernych rozkładów danych, niedopasowanych indeksów albo zmian wolumenu danych.

• Statystyki: odświeżenie statystyk (automatyczne lub kontrolowane) może sprawić, że plan stanie się mniej wrażliwy na pierwszy parametr, bo estymacje będą bliższe prawdzie.
• Indeksy: dołożenie/brakujący indeks lub korekta istniejącego może zmienić „krajobraz planów” tak, że różne parametry przestaną wymuszać skrajnie różne strategie (np. zawsze sensowny seek zamiast skanów).
• Konserwacja: reorganizacja/odbudowa indeksów to osobny temat, ale w praktyce bywa częścią pakietu stabilizacyjnego, jeśli fragmentacja lub zmiana rozkładu danych pogarsza przewidywalność.

To podejście ma tę zaletę, że poprawia nie tylko jedno zapytanie, ale często całą klasę zapytań. Ma też koszt: maintenance w oknach serwisowych i ryzyko zmiany planów w innych miejscach.

Jak wybrać technikę (krótka ściąga)

• Chcesz zmniejszyć wahania i masz „typowy” przypadek → OPTIMIZE FOR (konkretna wartość) lub OPTIMIZE FOR UNKNOWN.
• Masz dużą zmienność selektywności i nie ma jednego dobrego planu → RECOMPILE (po ocenie kosztu kompilacji).
• Nie możesz zmienić zapytania/procedury w kodzie → rozważ plan guides jako warstwę administracyjną.
• Wiesz, że problem to złe decyzje optymalizatora w Twoich danych → hinty tylko punktowo i z testem regresji.
• Widzisz oznaki złych estymacji lub intensywnych zmian danych → zacznij od statystyk i indeksów (często to najzdrowsza stabilizacja).

6. Stabilizacja przez Query Store: wymuszenie planu, monitorowanie regresji, zasady rollbacku

Query Store to mechanizm w SQL Server, który utrwala historię zapytań, planów i metryk wykonania (czas, CPU, odczyty) w bazie danych. W kontekście „parameter sniffing” jest szczególnie użyteczny, bo pozwala zapanować nad niestabilnością planów bez modyfikowania tekstu zapytania: możesz wskazać, który plan ma być używany, a następnie kontrolować, czy sytuacja się poprawia i bezpiecznie wycofać zmianę.

Wymuszenie planu (Force Plan): kiedy ma sens i co daje

Wymuszenie planu w Query Store polega na tym, że dla konkretnego zapytania SQL Server ma preferować wskazany plan jako „docelowy”. To podejście bywa skuteczne, gdy problem wynika z tego, że ten sam kod raz kompiluje się do „dobrego”, a raz do „złego” planu (np. w zależności od pierwszych wąchanych parametrów), a Ty chcesz szybko przywrócić przewidywalność.

• Zastosowanie: szybka stabilizacja po regresji, „zamrożenie” sprawdzonego planu, ograniczenie losowości wynikającej z parameter sniffing.
• Korzyść: minimalna ingerencja (bez zmian w kodzie aplikacji i bez przepisywania zapytań).
• Ograniczenie: wymuszenie nie tworzy „idealnego” planu dla wszystkich możliwych parametrów — raczej wybierasz plan, który jest akceptowalny w większości przypadków (lub krytycznych ścieżkach).

Force Plan vs inne podejścia: krótkie porównanie

Jak bezpiecznie wybrać plan do wymuszenia (praktyka produkcyjna)

Najczęstszy błąd to wymuszenie planu „najlepszego” dla pojedynczego przypadku. W produkcji lepiej myśleć o planie jako o kompromisie:

• Wybieraj plan, który daje stabilne wyniki przy typowym obciążeniu, a nie rekordowy czas w jednym scenariuszu.
• Preferuj plan, który minimalizuje piki CPU/IO i ryzyko blokad, nawet jeśli średni czas jest nieco gorszy.
• Zwracaj uwagę na okno czasowe i wersję aplikacji: plan „dobry wczoraj” może być „zły dziś”, jeśli zmieniły się dane lub workload.

Minimalny przykład: wymuszenie i cofnięcie planu (T-SQL)

Wymuszenie planu wykonuje się przez procedury systemowe Query Store. Poniżej przykład „szkieletu” operacji (ID pobierzesz z Query Store):

-- Wymuszenie konkretnego planu dla zapytania
EXEC sys.sp_query_store_force_plan
    @query_id = 123,
    @plan_id  = 456;

-- Wycofanie wymuszenia planu
EXEC sys.sp_query_store_unforce_plan
    @query_id = 123,
    @plan_id  = 456;

Monitorowanie po wymuszeniu: co obserwować, żeby nie „naprawić” jednego kosztem reszty

Po wymuszeniu planu kluczowe jest monitorowanie, czy stabilizacja nie przeniosła problemu gdzie indziej. Query Store ułatwia to, bo pozwala porównać metryki przed i po oraz zobaczyć trendy.

• Czas trwania: spadek mediany i „ogona” (p95/p99), a nie tylko średniej.
• CPU i logical reads: czy nie pojawiły się nowe piki albo stały wzrost kosztu.
• Liczba wykonań: czy zmiana planu nie spowodowała, że zapytanie zaczęło „dławić” inne (np. przez dłuższe trzymanie zasobów).
• Regresje czasowe: czy problem nie wraca w konkretnych godzinach (inny rozkład parametrów w ciągu dnia).

W praktyce monitorowanie powinno obejmować co najmniej pełen cykl biznesowy (np. doba) albo okres, w którym wcześniej występowały skoki.

Zasady rollbacku: kiedy i jak wycofać wymuszenie

Force Plan to interwencja operacyjna — warto mieć jasne zasady wycofania, zanim ją wprowadzisz. Typowe reguły rollbacku:

• Natychmiastowy rollback, jeśli po wymuszeniu pojawiają się nowe blokady, timeouty lub skok zużycia CPU/IO w kluczowych oknach.
• Rollback warunkowy, gdy metryki poprawiają się dla jednego scenariusza, ale pogarszają dla innych (np. rośnie p95/p99).
• Rollback planowany, jeśli wymuszenie było „hotfixem” po wdrożeniu — po ustabilizowaniu sytuacji i wprowadzeniu docelowej korekty (np. statystyki/indeksy) usuń wymuszenie, żeby nie blokować naturalnej adaptacji optymalizatora.

Technicznie rollback jest prosty: unforce dla konkretnego planu. Operacyjnie ważniejsze jest, by rollback był odwracalny i szybki: trzymaj zapisane query_id/plan_id, notuj moment zmiany i obserwowane metryki.

Pułapki i dobre praktyki przy Force Plan

• Zmiany danych i statystyk: plan wymuszony może „zestarzeć się” szybciej. Po dużych zmianach wolumenu danych obserwuj ponownie metryki.
• Nie wymuszaj masowo: wiele wymuszonych planów utrudnia diagnostykę i utrzymanie. Traktuj to jako narzędzie punktowe.
• Weryfikuj, czy plan faktycznie jest wymuszany: w rzadkich przypadkach SQL Server może nie być w stanie użyć wymuszonego planu (np. z powodu zmian schematu). Wtedy Query Store zwykle pokaże informację o niepowodzeniu wymuszenia.
• Dokumentuj powód: dlaczego ten plan, jaki był symptom, jakie KPI mają się poprawić, kiedy przegląd sytuacji.

Query Store daje więc „dźwignię operacyjną”: możesz szybko ustabilizować plan dla problematycznego zapytania, monitorować wpływ na produkcję i równie szybko wycofać zmianę, gdyby kompromis okazał się nieakceptowalny.

7. Procedura krok po kroku (od wykrycia do wdrożenia poprawki i weryfikacji w produkcji)

Poniższa procedura prowadzi od pierwszego sygnału problemu do stabilizacji wykonania w produkcji tak, aby ograniczyć ryzyko, nie pogorszyć innych zapytań i mieć czytelny plan wycofania. Jest celowo pragmatyczna: najpierw potwierdzenie wpływu na biznes, potem identyfikacja wariantów planu i na końcu wybór najmniej inwazyjnej metody stabilizacji.

Krok 1: Złap incydent i zdefiniuj „złe zachowanie”

• Ustal, które metryki „bolą” najbardziej: czas odpowiedzi, timeouty, CPU, I/O, blokady, spadek przepustowości.
• Określ ramy czasowe: od kiedy występuje degradacja, czy jest cykliczna (np. po wdrożeniu, po aktualizacji statystyk, rano vs. wieczór).
• Zidentyfikuj krytyczne ścieżki biznesowe: które endpointy/procesy batch są dotknięte i jaki jest akceptowalny próg.

Krok 2: Wybierz 1–3 podejrzane zapytania do analizy

• Skup się na zapytaniach, które generują największą zmianę: nagłe skoki czasu/CPU/odczytów, nie na tych, które są „zawsze wolne”.
• Upewnij się, że to jest to samo zapytanie logiczne (ta sama treść lub ten sam wzorzec wywołania), a nie wiele podobnych wariantów.
• Zapisz kontekst uruchomienia: aplikacja/host, procedura, typ operacji, częstotliwość wywołań.

Krok 3: Potwierdź, że problem ma charakter planu (a nie środowiska)

• Odróżnij regresję planu od problemów infrastruktury: przeciążenie dysku, zmiana zasobów, równoległość, blokady i długie transakcje mogą maskować symptom.
• Sprawdź, czy pogorszenie jest „skokowe” (typowe dla zmiany planu), czy „narastające” (częściej dane, indeksy, fragmentacja, logika aplikacji).
• Jeśli widzisz duże różnice w zachowaniu przy podobnym obciążeniu, to jest silny sygnał do dalszej weryfikacji planu.

Krok 4: Zbierz dowody na niestabilność planu i powiąż ją z parametrami

• Ustal, czy występują co najmniej dwa różne warianty wykonania: „dobry” (szybki) i „zły” (wolny) dla tego samego zapytania.
• Powiąż wariant z okolicznościami: jakie parametry wejściowe były użyte, jaka była selektywność (mało vs. dużo wierszy), jaki był rozkład danych w danym okresie.
• Zidentyfikuj, czy plan „zły” pojawia się po konkretnym zdarzeniu: restart, czyszczenie cache, kompilacja po wdrożeniu, automatyczne odświeżenie statystyk.

Krok 5: Oceń ryzyko i wybierz strategię stabilizacji

Dobór metody zależy od tego, czy bardziej zależy Ci na stabilności czy na optymalności dla każdego zestawu parametrów, oraz jak duży jest koszt kompilacji.

• Jeśli priorytetem jest szybkie „zatrzymanie krwawienia” i masz pewność, który plan jest dobry: preferuj podejście polegające na ustabilizowaniu planu (np. wymuszenie planu), z jasnym planem rollbacku.
• Jeśli wartości parametrów są skrajnie różne i każdy wariant wymaga innego planu: rozważ podejście nastawione na rekalkulację (kosztem kompilacji) albo kierunkowe „uśrednienie” planu.
• Jeśli problem wynika z jakości statystyk/zmian w danych: rozważ działania operacyjne (statystyki/indeksy) jako element rozwiązania, ale nie traktuj ich jako magicznego fixu, jeśli dowody wskazują na plan zależny od parametrów.

Krok 6: Zaplanuj wdrożenie z kontrolą wpływu

• Wybierz okno i sposób wdrożenia: natychmiastowe w produkcji (tylko gdy incydent jest krytyczny) lub kontrolowane w oknie serwisowym.
• Ustal kryteria sukcesu i porażki: docelowe czasy, dopuszczalne CPU/IO, brak nowych timeoutów, brak istotnej degradacji innych zapytań.
• Zdefiniuj rollback: co dokładnie cofniesz, jak szybko, oraz kto podejmuje decyzję o wycofaniu.

Krok 7: Wdróż minimalnie inwazyjny fix i monitoruj „tu i teraz”

• Wprowadź wybraną stabilizację w najmniejszym zakresie (pojedyncze zapytanie/procedura), unikając zmian globalnych, jeśli nie są konieczne.
• Po wdrożeniu sprawdź zachowanie natychmiast: czy „zły” wariant zniknął, a „dobry” jest używany w warunkach produkcyjnych.
• Równolegle obserwuj skutki uboczne: wzrost kompilacji, wzrost zużycia pamięci, zmiany w równoległości, przesunięcia obciążenia na I/O.

Krok 8: Zweryfikuj stabilność w horyzoncie godzin i dni

• Sprawdź, czy poprawa utrzymuje się w różnych porach i dla różnych typów parametrów, nie tylko dla jednego „szczęśliwego” przypadku.
• Monitoruj regresje wtórne: czasem ustabilizowanie planu dla jednego zapytania uwidacznia wąskie gardło w innym miejscu.
• Zbierz porównywalne dane „przed vs. po”: mediany i percentyle czasów, CPU/IO na wykonanie, liczbę timeoutów oraz trend.

Krok 9: Zabezpiecz się na przyszłość (operacyjnie)

• Ustal mechanizm wczesnego ostrzegania: alerty na skoki czasu/CPU/IO dla kluczowych zapytań oraz na nagłe zmiany planów.
• Dodaj kontrolę po wdrożeniach: krótka lista krytycznych zapytań do obserwacji oraz progi, po których uruchamiasz procedurę diagnostyczną.
• Udokumentuj decyzję: jaki był symptom, jakie były dowody, jaka metoda stabilizacji została wybrana, jakie są kryteria rollbacku i kiedy należy ponownie ocenić rozwiązanie (np. po zmianie wolumenu danych).

Krok 10: Jeśli fix nie działa — iteruj metodycznie

• Jeżeli po stabilizacji nadal występują skoki: wróć do dowodów i sprawdź, czy to na pewno był problem parametru, czy np. zmienna selektywność danych, blokady lub inne zapytanie powodujące presję na zasoby.
• Jeśli zadziałało tylko częściowo: rozważ zmianę strategii na taką, która lepiej obsługuje różne rozkłady parametrów (kosztem kompilacji lub kosztem uśrednienia planu).
• Jeśli pojawiają się skutki uboczne: uruchom rollback i wybierz alternatywę o mniejszym wpływie globalnym.

Taka ścieżka pozwala prowadzić działania w produkcji w sposób kontrolowany: najpierw wiarygodne potwierdzenie, potem najmniej ryzykowna stabilizacja, a na końcu weryfikacja trwałości i gotowość do wycofania.

8. Zasady bezpieczeństwa zmian w produkcji

Stabilizacja planów zapytań w kontekście „parameter sniffing” to zmiana o wysokim wpływie: może natychmiast poprawić czasy odpowiedzi, ale równie szybko zaszkodzić innym przypadkom użycia. Dlatego podejście produkcyjne powinno opierać się na kontrolowanym wdrożeniu, dobrej obserwowalności i jasnych kryteriach wycofania. Poniżej zasady, które pozwalają minimalizować ryzyko, nawet jeśli sama technika stabilizacji jest relatywnie „mała”.

Testy A/B i wdrażanie stopniowe

• Oceniaj wpływ na różne klasy parametrów: celem jest uniknięcie sytuacji, w której poprawa dla jednego „krytycznego” parametru pogarsza inne, rzadziej widoczne ścieżki.
• Wdrażaj stopniowo: zaczynaj od najmniejszego zasięgu (pojedyncze zapytanie/procedura), a dopiero po potwierdzeniu efektu rozszerzaj zmianę.
• Oddzielaj test od produkcyjnej presji czasu: jeśli to możliwe, wykonuj kontrolowane porównanie w warunkach zbliżonych do produkcji (ten sam wolumen danych, podobny profil obciążenia), ale z ograniczonym ryzykiem dla użytkowników.

Okna serwisowe i kontrola współbieżności

• Planuj zmiany w czasie przewidywalnego obciążenia: nawet jeśli poprawka jest „bezpieczna”, jej efekty często ujawniają się dopiero pod typowym ruchem.
• Minimalizuj równoczesne zmiany: nie łącz stabilizacji planów z aktualizacjami indeksów, zmianami aplikacji i innymi modyfikacjami w tym samym oknie. Ułatwia to atrybucję przyczyny przy regresji.
• Ustal właściciela decyzji: ktoś musi mieć mandat do przerwania wdrożenia lub natychmiastowego rollbacku, gdy metryki przekroczą próg ryzyka.

Obserwowalność: co monitorować, zanim dotkniesz planu

Bez telemetrii łatwo „wygrać” na jednej metryce i przegrać na innej. Przed wdrożeniem upewnij się, że masz bazową obserwację stanu i możliwość szybkiego porównania „przed/po”.

• Metryki użytkowe: czasy odpowiedzi dla kluczowych endpointów, procent błędów, timeouty, kolejki zadań.
• Metryki SQL/hosta: CPU, oczekiwania, IO, pamięć, blokady oraz ogólny throughput. Nawet jeśli zapytanie przyspieszy, może zwiększyć presję na IO lub wywołać blokady.
• Metryki na poziomie zapytania: rozrzut czasów wykonania (stabilność), liczba wykonań, odchylenia w odczytach logicznych/fizycznych. Kluczowe jest wykrycie, czy zmiana zmniejsza „ogon” (p95/p99), a nie tylko średnią.

Dokumentacja decyzji i ślad audytowy

• Opisz problem w kategoriach objawów: co dokładnie było niestabilne, kiedy występowało i jaki miało wpływ biznesowy/operacyjny.
• Zapisz zakres i intencję: czego dotyczy zmiana (konkretne zapytanie/procedura), jaki jest oczekiwany efekt oraz jakie ryzyka są akceptowane.
• Zanotuj warunki brzegowe: dla jakich typów obciążeń lub parametrów spodziewasz się korzyści, a gdzie może pojawić się regresja.
• Ustal odpowiedzialność: kto wdraża, kto zatwierdza, kto monitoruje po wdrożeniu i jak długo trwa „okres obserwacji”.

Kryteria wycofania (rollback) i „bezpieczny przycisk stop”

Rollback musi być zaplanowany zanim wykonasz zmianę. Największe ryzyko w produkcji wynika nie z samej techniki, tylko z braku szybkiego i pewnego sposobu powrotu do poprzedniego stanu. W Cognity łączymy teorię z praktyką – dlatego podobne scenariusze rozwijamy także w formie ćwiczeń na szkoleniach.

• Progi alarmowe: z góry zdefiniuj, jakie pogorszenie jest nieakceptowalne (np. wzrost timeoutów, skok p95/p99, wzrost CPU lub IO ponad ustalony poziom, wzrost blokad).
• Czas reakcji: określ maksymalny czas, po którym podejmujesz decyzję o wycofaniu, jeśli sytuacja jest niejednoznaczna.
• Plan powrotu: przygotuj jednoznaczną procedurę cofnięcia zmiany i sprawdzenia, że system wrócił do stabilnych parametrów działania.
• Weryfikacja po rollbacku: cofnięcie to nie koniec — potwierdź, że kluczowe metryki wróciły do poziomów bazowych oraz że nie powstały efekty uboczne (np. trwałe kolejki, wyczerpane zasoby).

Higiena wdrożeń: ograniczanie ryzyka długoterminowego

• Jedna hipoteza na wdrożenie: mniejszy zestaw zmian daje większą pewność wniosków i łatwiejszą diagnostykę.
• Kontrola zmian w czasie: po wdrożeniu utrzymaj wzmożone monitorowanie przez ustalony okres, bo regresje często ujawniają się dopiero przy zmianie profilu danych lub obciążenia.
• Utrzymuj spójność operacyjną: jeżeli stabilizacja planu jest traktowana jako element „konfiguracji wydajności”, powinna podlegać tym samym procesom co inne zmiany produkcyjne (przegląd, akceptacja, rejestrowanie i przegląd po wdrożeniu).

Jak czytać wykresy pudełkowe (boxplot) w raportach HR i finansów: interpretacja bez błędów 01 kwietnia 2026

RStudio: 10 trików w tidyverse, które skracają analizę o połowę (z przykładami) 30 marca 2026