Parameter Sensitive Plan optimization (PSPO) na SQL Server 2025

Źródła: Parameter Sensitive Plan optimization, Intelligent query processing.

Problem i idea

Sparametryzowane zapytanie kompiluje się raz, a plan zależy od wartości parametru z pierwszego wykonania (parameter sniffing). Przy nierównomiernym rozkładzie danych ten plan bywa zły dla innych wartości. PSPO (od SQL Server 2022, CL 160) pozwala utrzymywać kilka aktywnych planów dla jednej instrukcji i dobierać plan do wartości parametru w czasie wykonania.

Czym jest dispatcher plan

Najkrócej. Dispatcher plan to plan, który nie zwraca danych sam z siebie, tylko wskazuje, który z kilku planów wykonać. Można go porównać do rozdzielni: sprawdza wartość parametru i kieruje wykonanie do właściwego planu, a właściwą pracę wykonuje wybrany plan (query variant). Microsoft używa terminów dispatcher plan, dispatcher expression i query variant, więc w tym dokumencie zostają w oryginalnym brzmieniu.

Co dispatcher plan robi przy każdym wykonaniu (według dokumentacji):

1. Oblicza dispatcher expression dla podanych wartości parametrów i wyznacza zakres kardynalności.
2. Przypisuje zakres do konkretnego wariantu zapytania.
3. Kompiluje i wykonuje wybrany wariant. Dzięki wielu wariantom jedno zapytanie ma wiele planów.

Dlaczego to w ogóle potrzebne. Bez PSPO silnik zapisuje jeden plan, zbudowany dla wartości parametru z pierwszego wykonania. Jeśli pierwsze wywołanie dotyczy wartości „rzadkiej” (mało wierszy), plan z seekiem i key lookup trafia potem do wartości „częstej” (miliony wierszy) i wykonuje miliony lookupów. Odwrotnie: plan ze scanem zbudowany dla wartości częstej jest zbyt drogi dla rzadkiej.

Przykład 1: PSPO, jeden predykat

Dane ilustracyjne. Tabela dbo.Property z 5 000 000 wierszy i indeksem nieklastrowanym po AgentId. Zapytanie:

SELECT PropertyId
             , AgentId
             , ListingPrice
FROM dbo.Property
WHERE AgentId = @AgentId
ORDER BY ListingPrice DESC;

W dokumentacji granice zakresów to 100 i 1 000 000 wierszy. Przy takich granicach dispatcher plan kieruje wykonanie tak:

Kształty planów w ostatniej kolumnie to ilustracja. Rzeczywisty plan każdego wariantu wybiera optymalizator.

Jak to wygląda w planie (przykład z dokumentacji). W Showplan XML dispatcher plan zawiera element <Dispatcher>:

<Dispatcher>
  <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1000000">
    <StatisticsInfo Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]"
                    Statistics="[NCI_Property_AgentId]" ModificationCount="0" SamplingPercent="100" />
    <Predicate> ... [dbo].[Property].[AgentId]=[@AgentId] ... </Predicate>
  </ParameterSensitivePredicate>
</Dispatcher>

Tekst wariantu ma dopisany hint z identyfikatorem wariantu i granicami:

... ORDER BY ListingPrice DESC
option (PLAN PER VALUE(ObjectID = 613577224, QueryVariantID = 1,
        predicate_range([PropertyMLS].[dbo].[Property].[AgentId] = @AgentId, 100.0, 1000000.0)))

Przykład 2: PSPO, dwa predykaty

Każdy wybrany predykat ma trzy zakresy, a wariant odpowiada kombinacji zakresów wszystkich predykatów. Dla dwóch predykatów daje to do dziewięciu wariantów, numerowanych od 1 do 9. W przykładzie z dokumentacji zapytanie z UNION i dwoma predykatami AgentId dostało wariant QueryVariantID = 9 z dwoma predicate_range w hincie.

Przykład 3: OPPO

Dla zapytania z parametrem opcjonalnym dispatcher plan zawiera predykat @bedrooms IS NULL (element OptionalParameterPredicate), a warianty odpowiadają stanom parametru:

-- zapytanie nadrzędne
SELECT * FROM Properties WHERE bedrooms = @bedrooms OR @bedrooms IS NULL;

-- wariant 1: @bedrooms IS NULL (predykat zredukowany do stałej, plan oparty o scan)
SELECT * FROM Properties PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 1, optional_predicate(@bedrooms is NULL))

-- wariant 2: @bedrooms IS NOT NULL (plan oparty o seek po indeksie)
SELECT * FROM Properties WHERE bedrooms = @bedrooms PLAN PER VALUE(ObjectID = 1234, QueryVariantID = 2, optional_predicate(@bedrooms is NULL))

Ten zapis pochodzi z przykładu w dokumentacji OPPO. Dispatcher expression sprawdza wartość @bedrooms i kieruje wykonanie do wariantu 1 albo 2.

Gdzie dispatcher plan widać

• W planie wykonania jako element <Dispatcher> oraz w tekście wariantów jako hint PLAN PER VALUE.
• W cache planów: dispatcher plan trafia do CACHESTORE_SQLCP, a warianty do CACHESTORE_OBJCP.
• W Query Store: sys.query_store_plan rozróżnia plan zwykły, dispatcher plan i plan wariantu, a sys.query_store_query_variant opisuje relację zapytanie nadrzędne – dispatcher plan – warianty.
• W zdarzeniach XE: pola is_query_variant i query_variant_id (wartość 0 w query_variant_id oznacza dispatcher plan, czyli rodzica).

Czego dispatcher plan nie jest

• Nie jest planem wykonywanym na danych. Wybiera wariant i go uruchamia.
• Nie powstaje dla każdego zapytania. Wymaga zapytania sparametryzowanego z kwalifikującym się predykatem (PSPO: równościowy o nierównym rozkładzie, OPPO: parametr opcjonalny z testem NULL). Nie powstaje przy OPTION (RECOMPILE) i wyłączonym parameter sniffingu.
• Nie jest przebudowywany przy każdym wykonaniu. Dispatcher plan jest przebudowywany po istotnej zmianie rozkładu danych, a warianty rekompilują się niezależnie, według zwykłych zdarzeń rekompilacji.

Przebieg kompilacji i utrzymania

• Wybór predykatów. Przy pierwszej kompilacji histogramy statystyk wskazują nierównomierne rozkłady. Oceniane są najbardziej „ryzykowne” predykaty z parametrami, maksymalnie trzy na zapytanie, żeby nie rozdymać cache i Query Store.
• Zakresy. Dla każdego wybranego predykatu dispatcher plan zapisuje granice i statystyki, na których je oparto. Wartość parametru w czasie wykonania trafia do jednego z trzech zakresów.
• Hint PLAN PER VALUE jest tylko znacznikiem systemowym. Wpisany ręcznie nie jest parsowany.

Ograniczenia PSPO (dla każdego CL)

• działa tylko dla predykatów równościowych,
• jest wyłączone, gdy wyłączony jest parameter sniffing (trace flag 4136, PARAMETER_SNIFFING, hint DISABLE_PARAMETER_SNIFFING),
• nie działa dla zapytań z OPTION (RECOMPILE) (po dodaniu RECOMPILE do zapytania nadrzędnego powstają plany bez PSPO, gdy stare warianty wypadną z cache),
• Query Store nie jest wymagany, ale jest zalecany. Od SQL Server 2022 jest domyślnie włączony w nowych bazach.

Co zmienia CL 170 (cztery usprawnienia z dokumentacji)

1. Instrukcje DML. PSPO obejmuje DELETE, INSERT, MERGE i UPDATE. Na CL 160 działało wyłącznie dla SELECT. Ponieważ PSPO ocenia tylko predykaty równościowe z parametrami, w praktyce dotyczy to tych instrukcji DML, które takie predykaty zawierają (np. UPDATE ... WHERE kolumna = @p, DELETE ... WHERE ..., MERGE ... ON ..., INSERT ... SELECT ... WHERE ...). Informacja nie została zweryfikowana (brak odniesień do dokumentacji Microsoft). Skutek uboczny: procedury z intensywnym DML mogą po podniesieniu CL tworzyć warianty, czego na CL 160 nie robiły.

2. Rozszerzona obsługa tempdb. Dokumentacja podaje tylko ten ogólny opis. Które scenariusze z tempdb (np. tabele tymczasowe) zostały dodatkowo objęte, nie zostało zweryfikowane (brak odniesień do dokumentacji Microsoft).

3. Wiele predykatów na jednej tabeli. Gdy kilka kwalifikujących się predykatów dotyczy tej samej tabeli, PSPO wybiera ten o największej skośności według histogramu. Przykład z dokumentacji: WHERE column1 = @p1 AND column2 = @p2 na jednej tabeli ocenia tylko najbardziej skośny predykat. Przy UNION lub self-joinie z aliasami system traktuje gałęzie jak różne tabele i może wybrać więcej niż jeden predykat (w przykładzie z UNION co najwyżej dwa).

4. Zmiana zdarzenia XE query_with_parameter_sensitivity. Pola: interesting_predicate_count, interesting_predicate_details (JSON z listą predykatów i ich skośnością), psp_optimization_supported, query_type. Pole max_skewness zostało zastąpione przez interesting_predicate_details.

Konsekwencje dla narzędzi i monitoringu

• Warianty są kompilowane jako nowe prepared statements, więc tracą powiązanie z object_id procedury w DMV planu. object_id jest dostępny w Query Store (widok sys.query_store_query_variant, który opisuje relację zapytanie nadrzędne – dispatcher plan – warianty; sys.query_store_plan odróżnia plan zwykły, dispatcher plan i plan wariantu) oraz w atrybucie ObjectID hintu w planie. Skrypty łączące cache planów z sys.objects przez object_id mogą nie widzieć wariantów.
• Dla wariantów tego samego dispatcher plan: query_hash jest wspólny (można zsumować zużycie zapytań różniących się tylko wartościami parametrów), a query_plan_hash, plan_handle, sql_handle i query_id w Query Store są unikalne.
• Warianty trafiają do CACHESTORE_OBJCP, a dispatcher plans do CACHESTORE_SQLCP. Przy wysokiej eliminacji planów lub bardzo dużych magazynach dokumentacja sugeruje trace flag 174.
• Limit Query Store max_plans_per_query (domyślnie 200 planów na zapytanie, włącznie z wariantami). Dokumentacja podaje przykład, w którym zapytanie z dwoma dispatcher plans po 20 wariantów, kilkoma planami nieprzypisanymi do PSPO i kilkoma planami na wariant przekracza ten limit, i radzi rozważyć jego zwiększenie.
• SQL Server Audit rejestruje zarówno instrukcję dispatcher plan, jak i jej warianty. Kolumna additional_information zawiera stos T-SQL (tsql_stack) także dla wariantów, więc przybywa wpisów audytu.

Query Store: plany wymuszone i hinty

• Wymuszenie planu wariantu nie wymusza dispatcher plan. Wymuszenie dispatcher plan ogranicza użycie do wariantów tego dispatcher plan. Poprzednio wymuszone warianty innych dispatcher plans stają się nieaktywne, ale zachowują status „forced” do ponownego wymuszenia ich dispatcher plan.
• Hint Query Store dodany do wariantu działa jak dla zwykłego zapytania i ma wyższy priorytet niż hint zapytania nadrzędnego. Gdy hint dodano tylko do zapytania nadrzędnego, warianty go dziedziczą. Usunięcie hintu z nadrzędnego usuwa go z wariantów.
• Hint RECOMPILE dodany do zapytania nadrzędnego powoduje generowanie planów bez PSPO po usunięciu istniejących wariantów z cache.

Wpływ estymatora

Progi skośności można modyfikować przez trace flagi CE (np. 9481) i opcje obniżające model CE (LEGACY_CARDINALITY_ESTIMATION). Brak statystyk wielokolumnowych utrudnia ocenę kwalifikacji predykatów. Dokumentacja nie zaleca legacy CE dla większości obciążeń.

Wyłączanie i diagnostyka

-- stan opcji bazy
SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name IN (N'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION', N'OPTIONAL_PARAMETER_OPTIMIZATION',
               N'DOP_FEEDBACK', N'CE_FEEDBACK', N'LEGACY_CARDINALITY_ESTIMATION');

-- wyłączenie PSPO dla bazy
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

-- powody, dla których PSPO mogło zostać pominięte (zdarzenie XE parameter_sensitive_plan_optimization_skipped_reason)
SELECT map_value FROM sys.dm_xe_map_values WHERE [name] = N'psp_skipped_reason_enum' ORDER BY map_key;

Wyłączenie dla jednego zapytania: hint DISABLE_PARAMETER_SENSITIVE_PLAN. Zdarzenia XE: parameter_sensitive_plan_optimization (kanał debug; pola m.in. is_query_variant, predicate_count, query_variant_id), parameter_sensitive_plan_optimization_skipped_reason, query_with_parameter_sensitivity.

Znany problem, już naprawiony

Access violation na readable secondary, gdy PSPO działało razem z Query Store dla secondary. Wykryty we wrześniu 2025, naprawiony w CU1 (styczeń 2026) (tabela Known Issues na stronie PSP). Zalecenie „wyłącz PSPO”, które opisywał Brent Ozar w grudniu 2025, dotyczy buildów bez tej poprawki (Brent Ozar — Known Issues).

Checklista przed podniesieniem CL do 170

1. Query Store w trybie READ_WRITE, z odpowiednio dużym MAX_STORAGE_SIZE_MB i max_plans_per_query.
2. Baseline czasów i liczby planów dla procedur z DML.
3. Przegląd skryptów monitorujących, które łączą plany z sys.objects przez object_id.
4. Obserwacja CACHESTORE_OBJCP i CACHESTORE_SQLCP po zmianie.
5. Gotowość do wyłączenia PSPO na poziomie bazy lub zapytania, jeśli pojawi się regresja.

Brent Ozar zauważa, że SQL Server 2022 i 2025 znacząco zwiększyły liczbę planów, jakie może mieć jedno zapytanie lub procedura, i dodał do kursu osobny moduł na ten temat (nowości w kursach).