Optional Parameter Plan Optimization (OPPO) na SQL Server 2025

Źródła: OPPO, Intelligent query processing.

Problem

Wzorzec „kitchen-sink search”:

SELECT * FROM Properties
WHERE bedrooms = @bedrooms OR @bedrooms IS NULL;

Gdy @bedrooms ma wartość, najlepszy jest seek po indeksie. Gdy jest NULL, predykat jest zawsze prawdziwy i lepszy bywa scan. Seek nie jest poprawnym planem dla NULL, więc pojedynczy plan wielokrotnego użytku musi być zachowawczy (scan), także dla wykonań selektywnych. Nawet gdy parameter sniffing „podejrzy” wartość 10, optymalizator nie wygeneruje seeka, bo ten plan byłby błędny dla NULL. OPTIMIZE FOR nie pomaga, ponieważ plan musi pozostać poprawny w obu stanach.

Dotychczasowe obejścia to rozbicie na IF @bedrooms IS NULL ... ELSE ..., dynamiczny SQL albo OPTION (RECOMPILE). Dokumentacja pokazuje zapis IF/ELSE jako obraz tego, co OPPO robi automatycznie.

Mechanizm

• OPPO działa na tej samej infrastrukturze Multiplan co PSPO. Tworzy dispatcher plan i warianty zapytania, po jednym na stan parametru (NULL / nie-NULL).
• Po wyborze wariantu silnik upraszcza predykaty na podstawie rzeczywistej wartości. Wyrażenie @p1 IS NULL jest redukowane do stałej, więc powstają plany nieważne w pojedynczym planie wielokrotnego użytku (wariant NULL może mieć plan ze scanem, wariant nie-NULL plan z seekiem).
• Silnik wkleja do metadanych planu systemowy hint PLAN PER VALUE (... optional_predicate(@bedrooms IS NULL)). Nie jest przeznaczony do ręcznego użycia.
• Wiele opcjonalnych predykatów. Przykład z dokumentacji: zapytanie z czterema parametrami (@AgentId, @ZipCode, @MinPrice, @HasDescription) wygenerowało wariant QueryVariantID = 7 z trzema hintami optional_predicate. Dokumentacja nie podaje wzoru na liczbę wariantów ani limitu. Wariant opisuje kombinację stanów wielu predykatów, więc liczba wariantów rośnie z liczbą opcjonalnych parametrów (informacja nie została zweryfikowana: brak odniesień do dokumentacji Microsoft).
• W planie przykładowym atrybut CardinalityEstimationModelVersion="170", czyli zapytanie kompilowane jest pod CE 170.

OPPO i PSPO

• PSPO wybiera plany po szacowanej kardynalności (predykaty równościowe i zakresy), OPPO po tym, czy parametr jest NULL.
• Jedno zapytanie może używać obu funkcji, jednej lub żadnej. Ponieważ może generować warianty obu typów, przy diagnozie sprawdza się oba zdarzenia „skipped reason” (PSPO: parameter_sensitive_plan_optimization_skipped_reason, OPPO: optional_parameter_optimization_skipped_reason).

Warunki włączenia

• baza na CL 170,
• opcja OPTIONAL_PARAMETER_OPTIMIZATION (database scoped configuration) włączona. Jest domyślnie ON, więc baza na CL 170 używa OPPO od razu.
• Opcja zapisana jako OFF nie włączy się sama po podniesieniu CL. Jawnie: ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = ON (Adam the Automator).

ALTER DATABASE [Baza] SET COMPATIBILITY_LEVEL = 170;
ALTER DATABASE SCOPED CONFIGURATION SET OPTIONAL_PARAMETER_OPTIMIZATION = ON;   -- wyłączenie: OFF
-- wyłączenie dla jednego zapytania, działa w każdym CL i ma pierwszeństwo przed opcją bazy:
-- OPTION (USE HINT ('DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION'))
SELECT map_value FROM sys.dm_xe_map_values WHERE [name] = N'opo_skipped_reason_enum' ORDER BY map_key;

Hint DISABLE_OPTIONAL_PARAMETER_OPTIMIZATION można podać w zapytaniu albo przez Query Store hints, bez zmiany kodu. W Azure SQL MI funkcja wymaga polityki aktualizacji SQL Server 2025 lub Always-up-to-date (nie działa w polityce SQL Server 2022).

Kiedy OPPO się nie stosuje

Funkcja działa tylko dla zapytań kwalifikujących się do optymalizacji Multiplan. Nie jest stosowana dla:

• zapytań używających zmiennych lokalnych zamiast parametrów,
• zapytań z OPTION (RECOMPILE),
• wykonań z SET ANSI_NULLS OFF,
• instrukcji parametryzowanych automatycznie.

Konsekwencja praktyczna. Procedury „kitchen-sink”, które kopiują parametry do zmiennych lokalnych albo już mają OPTION (RECOMPILE) jako obejście, nie skorzystają z OPPO, dopóki ten wzorzec nie zostanie usunięty. Z drugiej strony ich dotychczasowe obejście pozostaje skuteczne.

Diagnostyka

• optional_parameter_optimization_skipped_reason (powód odrzucenia, mapa opo_skipped_reason_enum),
• query_with_optional_parameter_predicate z polami optional_parameter_optimization_supported, optional_parameter_predicate_count, predicate_details (JSON ze skośnością) i query_type.

Zdarzenia nie są wymagane do działania funkcji. W planie wariantu szukaj PLAN PER VALUE i optional_predicate.

Procedura testowa (zalecenia branżowe)

SQLServerCentral proponuje: najpierw zebrać punkt odniesienia z wyłączonym OPPO na CL 170, włączyć i odpowiednio dobrać Query Store przed zmianą CL, zebrać najczęstsze i najdroższe kombinacje parametrów, testować na danych w skali produkcyjnej, potwierdzić PLAN PER VALUE i optional_predicate w planie oraz mierzyć każdy wariant osobno. Dokumentacja Microsoft nie stawia Query Store jako wymogu działania OPPO. Wpływ braku Query Store na działanie funkcji nie został zweryfikowany.

Ryzyka

• więcej planów w cache i Query Store, szczególnie przy wielu opcjonalnych parametrach,
• możliwa regresja dla konkretnego stanu parametrów (mierz warianty osobno),
• zmiana zachowania dotyczy całej bazy po podniesieniu CL, nie tylko wybranych procedur (dla pojedynczych zapytań użyj hintu wyłączającego).