SQL SERVER 2022 – Parameter Sensitive Plan Optimization (PSPO)

SQL Server 2022 introduces an exciting new feature called Parameter Sensitive Plan Optimization (PSPO), which aims to improve the performance of parameterized queries. This feature generates multiple execution plans for a parameterized query, each optimized for a different range of values for the parameters. This allows SQL Server to choose the best execution plan for the specific values of the parameters used when the query is executed.

How Parameter Sensitive Plan Optimization (PSPO) Works

Parameter Sensitive Plan Optimization (PSPO) is a feature of SQL Server that allows storing multiple execution plans for a single parameterized query. When a query is executed for the first time, its execution plan is created and stored in the plan cache. However, if the same query is executed again with a different parameter value, the cached plan may not be optimal for that value, leading to suboptimal performance. PSPO addresses this issue by enabling 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).

PSPO works by caching up to three execution plans per query – low, medium, and high cardinality ranges – and choosing between them at runtime based on the customer-provided runtime parameter value(s). This allows SQL Server to determine the optimal execution plan based on the parameter value(s), improving query performance. Additionally, PSPO can reduce parameter sniffing, a situation where SQL Server generates an execution plan based on the first parameter value it encounters and then reuses that plan for all subsequent parameter values. This can lead to suboptimal performance for some parameter values. PSPO can also improve scalability by accommodating different data sizes based on the customer-provided runtime parameter value(s).

When to Use PSPO

In SQL Server 2022, PSPO is automatically enabled by default. Parameter Sensitive Plan Optimization (PSPO) is a feature in SQL Server designed to improve query performance by accommodating different data sizes based on the customer-provided runtime parameter value(s). PSPO is beneficial in scenarios where a single cached plan for a parameterized query isn’t optimal for all possible incoming parameter values, such as non-uniform data distributions. PSPO can significantly improve query performance and scalability for applications with non-uniform data distributions by automatically enabling multiple, active cached plans for a single parameterized statement.

Enabling and Disabling PSPO in SQL Server 2022

PSPO is enabled by default in SQL Server 2022. However, it can be disabled at the database or query level. To disable PSPO at the database level, you can use the following T-SQL statement:

ALTER DATABASE [database_name] 
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

If you have accidentally disabled the PSPO, you can also enable it by running the following command:

ALTER DATABASE [database_name] 
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;

Benefits of Using PSPO

By generating multiple active cached plans for a single parameterized statement, PSPO offers several benefits, including:

1. Improved query performance: PSPO enables SQL Server to choose the optimal execution plan based on the customer-provided runtime parameter value(s), leading to enhanced query performance.
2. Reduced parameter sniffing: PSPO can reduce parameter sniffing, which can cause suboptimal performance for some parameter values. By caching up to three execution plans per query, PSPO can ensure that the optimal plan is chosen based on the parameter value(s).
3. Better scalability: PSPO can accommodate different data sizes based on the customer-provided runtime parameter value(s), making it particularly useful for applications with non-uniform data distributions.

Limitations of PSPO

There are a few limitations to PSPO, including:

• While PSPO offers benefits, it also has a few limitations to consider, including:

1. 1. Increased memory usage: PSPO enables multiple active cached plans for a single parameterized statement, which can improve memory usage, making it a concern for applications with limited memory resources.
   2. Increased plan cache size: PSPO stores multiple execution plans for a single parameterized statement, which can increase the plan cache size, making it a concern for applications with a large number of stored procedures or queries.
   3. Increased parameter sniffing: While PSPO can help reduce parameter sniffing, it can also contribute to the problem in some scenarios. PSPO caches up to three execution plans per query, potentially leading to more parameter sniffing. However, in most cases, the benefits of PSPO outweigh this limitation. PSPO generates plans for most of the cardinality ranges less prone to extreme performance issues, resulting in an overall improvement in query performance.

Video Demonstration

Here is the video demonstration:

Conclusion

In summary, PSPO is a valuable feature in SQL Server 2022 that can significantly improve parameterized query performance. However, it is essential to understand the limitations of PSPO and when it may not be suitable for all queries. Careful consideration of the scenarios where PSPO can be helpful and when to disable it is necessary to ensure optimal performance. If you have any questions, you can always ask me on Twitter.

Reference: Pinal Dave (http://blog.SQLAuthority.com)