Parameter Sensitive Plan optimization
Applies to: SQL Server (Starting with SQL Server 2022 (16.x))
Parameter Sensitive Plan (PSP) optimization is part of the Intelligent query processing family of features. It addresses the scenario where a single cached plan for a parameterized query is not optimal for all possible incoming parameter values. This is the case with non-uniform data distributions. For more information, see Parameter Sensitivity and Parameters and Execution Plan Reuse.
For more information on existing workarounds for this problem scenario, see:
- Investigate and resolve parameter-sensitive issues
- Parameters and Execution Plan Reuse
- Queries that have parameter sensitive plan (PSP) problems.
PSP optimization automatically enables multiple, active cached plans for a single parameterized statement. Cached execution plans will accommodate different data sizes based on the customer-provided runtime parameter value(s).
In the SQL Server Database Engine, using parameters or parameter markers in Transact-SQL statements increases the ability of the relational engine to match new Transact-SQL statements with existing, previously compiled execution plans and promote plan reutilization. For more information, see Simple Parameterization.
You can also override the default simple parameterization behavior of SQL Server by specifying that all
DELETE statements in a database be parameterized subject to certain limitations. For more information, see Forced Parameterization.
PSP optimization implementation
During the initial compilation, column statistics histograms identify non-uniform distributions and evaluate the most "at-risk" parameterized predicates, up to three out of all available predicates.
For eligible plans, the initial compilation produces a dispatcher plan that contains the PSP optimization logic called a dispatcher expression. A dispatcher plan maps to query variants based on the cardinality range boundary values predicates.
For each predicate that is chosen, the Query Processor bucketizes them into predicate cardinality ranges based on the runtime cardinality, as seen in the following picture:
Dispatcher plans are automatically rebuilt if there are significant data distribution changes. Query variant plans will independently recompile as needed, same as any other query plan type, and subject to default recompilation events. For more information about recompilation, see Recompiling Execution Plans.
For each query variant mapping to a given dispatcher:
query_plan_hashis unique. This column is available in
sys.dm_exec_query_stats, and other Dynamic Management Views and catalog tables.
plan_handleis unique. This column is available in
sys.dm_exec_cached_plans, and in other Dynamic Management Views and Functions, and catalog tables.
query_hashis common to other variants mapping to the same dispatcher, so it's possible to determine aggregate resource usage for queries that differ only by input parameter values. This column is available in
sys.query_store_query, and other Dynamic Management Views and catalog tables.
sql_handleis unique due to special PSP optimization identifiers being added to the query text during compilation. This column is available in
sys.dm_exec_cached_plans, and in other Dynamic Management Views and Functions, and catalog tables. The same handle information is available in the Query Store as the
last_compile_batch_sql_handlecolumn in the
query_idis unique in the Query Store. This column is available in
sys.query_store_query, and other Query Store catalog tables.
To enable PSP optimization, enable database compatibility level 160 for the database you are connected to when executing the query.
For additional insights into the PSP optimization feature, we recommend that Query Store integration also be enabled by ensuring that the Query Store is turned on. The following example will turn on the Query Store for a database called
ALTER DATABASE my_psp_database SET QUERY_STORE (QUERY_CAPTURE_MODE = AUTO);
To disable PSP optimization at the database level, use the
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF database scoped configuration.
Dispatcherelement shows details of the predicate boundaries as derived from a statistics histogram.
<Dispatcher> <ParameterSensitivePredicate LowBoundary="100" HighBoundary="1000000"> <StatisticsInfo Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Statistics="[NCI_Property_AgentId]" ModificationCount="0" SamplingPercent="100" LastUpdate="2019-09-07T15:32:16.89" /> <Predicate> <ScalarOperator ScalarString="[PropertyMLS].[dbo].[Property].[AgentId]=[@AgentId]"> <Compare CompareOp="EQ"> <ScalarOperator> <Identifier> <ColumnReference Database="[PropertyMLS]" Schema="[dbo]" Table="[Property]" Column="AgentId" /> </Identifier> </ScalarOperator> <ScalarOperator> <Identifier> <ColumnReference Column="@AgentId" /> </Identifier> </ScalarOperator> </Compare> </ScalarOperator> </Predicate> </ParameterSensitivePredicate> </Dispatcher>
QueryVariantIDattribute on QueryPlan element for query variant identification.
<QueryPlan DegreeOfParallelism="1" MemoryGrant="1024" CachedPlanSize="40" CompileTime="2" CompileCPU="2" CompileMemory="224" QueryVariantID="1">
To disable PSP optimization at the query level, use the
DISABLE_PARAMETER_SENSITIVE_PLAN_OPTIMIZATION query hint.
If parameter sniffing is disabled by trace flag 4136,
PARAMETER_SNIFFING database scoped configuration, or the
USE HINT('DISABLE_PARAMETER_SNIFFING') query hint, PSP optimization will be disabled for the associated workloads and execution contexts. For more information, see Hints (Transact-SQL) - Query and ALTER DATABASE SCOPED CONFIGURATION (Transact-SQL).
The number of unique plan variants per dispatcher stored in the plan cache is limited to avoid cache bloating. The internal threshold isn't documented.
The number of unique plan variants stored for a query in the Query Store store is limited by the
max_plans_per_query configuration option.
The following XEs are available for the feature:
parameter_sensitive_plan_optimization_skipped_reason: Occurs when the parameter sensitive plan feature is skipped. Use this event to monitor the reason why parameter sensitive plan optimization is skipped.
parameter_sensitive_plan_optimization: Occurs when a query uses Parameter Sensitive Plan (PSP) Optimization feature. Debug channel only.
parameter_sensitive_plan_testing: Occurs when parameter-sensitive plan is tested. Debug channel only.
Plan forcing in Query Store
If a variant is forced, the parent dispatcher is not forced. If a dispatcher is forced, only variants from that dispatcher are considered eligible for use:
- Previously forced variants from other dispatchers will become inactive but retain the forced status until their dispatcher is forced again
- Previously forced variants in the same dispatcher that had become inactive are forced again
At this time, the following are known issue(s) that will be addressed in upcoming CTP releases:
- When you have a stored procedure created in the non-dbo schema, and you have queries inside the stored procedure that reference tables without schema, error 208 can occur.
-- This fails CREATE OR ALTER PROCEDURE my_schema.my_proc @var1 int AS SELECT c2 FROM my_table WHERE c1 = @var1; -- This works CREATE OR ALTER PROCEDURE my_schema.my_proc @var1 int AS SELECT c2 FROM my_schema.my_table WHERE c1 = @var1;
Submit and view feedback for