Overview of PSP Optimization

PSPO (Parameter Sensitive Plan Optimization) is a SQL Server feature that improves query performance by accepting varied data sizes based on the runtime parameter value(s) specified by the customer. It deals with the situation in which a single cached plan for a parameterized query isn't the best option for all potential incoming parameter values. Non-uniform data distributions exhibit this phenomenon. When using PSPO, SQL Server keeps several execution plans for a single query, each one customized for a particular parameter value. With the help of this feature, numerous execution plans for a parameterized query are generated, each of which is tailored for a certain range of parameter values.

In order to avoid establishing a single execution plan for all parameter values, which can produce a suboptimal plan for some parameter values, it is a method of customizing query execution.

How the Feature Works

The execution plan for a query is constructed and saved in the plan cache when the query is initially run. For the parameter values used in the initial execution, the execution plan has been optimised. SQL Server will use the cached execution plan if the same query is run again with different parameter values. However, if the new parameter values differ greatly from the old ones, the cached execution plan might not be the best option. PSPO can help in this situation in the following ways.

Step 1: Parameter sniffing

A plan for the current parameter values is created by SQL Server when a stored procedure or query is run for the first time. An initial plan is a plan that is created based on the values of the parameters.

Step 2: Optimization

SQL Server compares the new parameter values with the old ones if the same query is run again with different parameter values. When the parameter values change, SQL Server optimises the query and generates a new execution plan. Such a plan is known as a customised plan, and it is tailored to the particular parameter values.

Step 3: Execution

The query is then carried out by SQL Server using the customised plan. As a result, queries run more quickly and perform better overall.

An Example

Here's an example to help you understand. I have a copy of the 2013 StackOverflow database. I have a table called users in that database with all user information. I have created a stored procedure called [userdetails] that will provide user information.  I will Run this with various parameters to see how it performs.

USE [StackOverflow2013]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[userdetails] @upvote nvarchar(30)
AS
select * from users where
 UpVotes = @upvote
GO

First I would like to view user information for users who have upvotes 1998.

EXECUTE [dbo].[userdetails] 
   @upvote = 1998

You may see here The query was carried out by SQL using execution plan Query variant 1.

Now that there are many people who have 2 upvotes, I want to view user information for those individuals. So I will run

EXECUTE  [dbo].[userdetails] 
   @upvote = 2

In this situation, the parameter has changed, thus SQL optimised the execution for the new parameter, made a new execution plan, and ran the query. The altered execution plan is also visible. SQL has chosen query variant 2 as execution plan.

Multiple Plans

When using PSPO, SQL Server keeps several execution plans for a single query, each one customised for a particular parameter value. Based on the precise parameter values supplied at runtime, SQL Server decides the best execution plan to use when executing a query. This enables the database engine, based on the precise parameter values supplied at runtime, to dynamically choose the most optimized execution plan.

Benefits for Query Performance

There are a number of benefits when using this feature.

1. Reduced parameter sniffing: Performance suffers when SQL Server creates an execution plan based on the first parameter value it encounters and then applies it to subsequent parameter values. When using PSPO, SQL Server keeps several execution plans for a single query, each one customized for a particular parameter value. This enables the database engine, based on the precise parameter values supplied at runtime, to dynamically choose the most optimized execution plan.
2. Better query performance: PSPO boosts query performance by dynamically choosing the best execution plan depending on the precise parameter values supplied at runtime.
3. Reduced CPU utilization for query: PSPO lessens CPU usage by cutting down on parameter sniffing and enhancing query performance.
4. Reduced memory utilization for query: PSPO lessens memory usage by keeping numerous execution plans for a single query, each adapted to distinct parameter values.

Limitations and Considerations

It is important to remember that parameter-sensitive plan optimization is not a panacea for all query performance problems. It functions well for queries with complicated execution plans and a lot of parameter values. It's possible that the performance difference won't matter much for easy and simple queries. After the functionality is enabled, it's crucial to keep an eye on the server's performance because it can need additional memory and CPU resources.

The limitations of PSPO include:

• The PSP optimization feature currently only works with equality predicates.
• Increased memory usage for SQL server : PSPO increases server memory usage by keeping numerous execution plans for a single query, each adapted to different parameter values.
• Increased plan cache size: PSPO expands the size of the plan cache by storing numerous execution plans for a single query, each customised to distinct parameter values.
• Increased CPU usage: While PSPO improves query performance and decreases parameter sniffing, it can also raise CPU consumption by storing numerous execution plans for a single query.
• Increased I/O usage: PSPO can increase I/O usage by storing numerous execution plans for a single query, each adapted to different parameter values.

Considerations

• To enable PSP optimization, enable database compatibility level 160 for the database you're connected to when executing the query.
• For additional insights into the PSP optimization feature, Query Store integration is enabled, by turning on the Query Store.

How to Enable Parameter Sensitive Plan Optimization

SQL Server 2022 by default has PSPO enabled. However, it may be turned off at the query or database level. This T-SQL command can be used to disable PSPO at the database level:

ALTER DATABASE [database_name]
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = OFF;

Using the following command, you can make it active if it's disabled:

ALTER DATABASE [database_name]
SET PARAMETER_SENSITIVE_PLAN_OPTIMIZATION = ON;

To enable the PSP optimization at query level, Add the query hint OPTION (USE HINT('ENABLE_PARAMETER_SENSITIVITY_OPTIMIZATION')) just before the semicolon at the end of the query or stored procedure. Run the stored procedure or query. This stored procedure or query now has the feature enabled.

Query without parameter sensitive plan optimization:

SELECT * FROM records  WHERE record_id  BETWEEN '201' AND '571';

Query with parameter sensitive plan optimization:

SELECT * FROM records  WHERE record_id  BETWEEN  @start AND @end
 OPTION(USE HINT('ENABLE_PARAMETER_SENSITIVITY_OPTIMIZATION'));

Conclusion

In SQL Server 2022, a potent feature called parameter sensitive plan optimisation is expected to improve query performance and speed up execution. The efficiency of queries with a lot of parameter values and complex execution plans can be greatly enhanced by optimizing the execution plan for specific parameter values. For everyone who works with SQL Server, it's absolutely worth exploring, even though it's not a cure-all for all query speed issues.