SQL Server creates Parameter Sensitive Plan (PSP) variants only when an eligible parameterized query has enough estimated data skew to justify them. A selective value and a common value producing different ideal plans does not, by itself, guarantee that the optimizer will build a dispatcher.
Last updated: October 1, 2026.
SELECT name, compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();
SELECT name, value, value_for_secondary
FROM sys.database_scoped_configurations
WHERE name IN (
'PARAMETER_SENSITIVE_PLAN_OPTIMIZATION',
'PARAMETER_SNIFFING'
);
SELECT map_key, map_value
FROM sys.dm_xe_map_values
WHERE name = 'psp_skipped_reason_enum'
ORDER BY map_key;For SQL Server 2022, the database needs compatibility level 160, PSP enabled, and parameter sniffing enabled. The final query lists the values reported by the parameter_sensitive_plan_optimization_skipped_reason Extended Event.
Check query eligibility before changing the plan
PSP currently considers equality predicates, such as WHERE CustomerId = @CustomerId. It does not promise variants for every skewed column, and an OPTION (RECOMPILE) hint prevents PSP because the statement is compiled for each execution. Microsoft’s PSP documentation also explains that statistics histograms drive predicate selection.
Confirm that the test calls a stored procedure, sp_executesql, or another genuinely parameterized statement. Separate ad hoc statements containing different literal values do not demonstrate reuse of one parameterized plan.
Inspect evidence instead of forcing a demonstration
Refresh stale statistics on the filtered column when the histogram no longer represents the data, then rerun representative values. Enable Query Store and inspect sys.query_store_query_variant for dispatcher-to-variant relationships. If no dispatcher appears, capture the PSP skip event and compare its numeric reason with the map above.
Do not clear the entire plan cache on a production server merely to test PSP. Use a controlled test database or target a specific plan. For related administration work, see choosing indexes for filter patterns, SQL Server procedure parameters, and cross-database collation conflicts.
