SQL Server 2022 and the Parameter Sensitive Plan Optimization (PSP) with example

Hi guys,

Today we will start talking about the features that the new SQL Server 2022 bring us.
We have already talked about it here.

In particular, today I want to talk to you about the new feature called Parameter Sensitive Plan Optimization or PSP.

This feature is part of the family of features known as Intelligent Query Processing and aims to improve the performance of existing workloads.

I want to remark that this is a big performance improvement and one of the main highlights of this new version of SQL Server.
 

This improvement comes at no cost and without any changes to the application code.

Parameter Sensitive Plan (PSP) Optimization

 

Starting from the idea that creating an execution plan (determine which is the best way to solve a Query) require resources and time.

Microsoft has developed into SQL Server 2022 a mechanism to save and reuse created execution plans.

Briefly, the first time a query is executed its execution plan is created and stored in the plan cache.

When the same Query will be executed a second time the execution plan is retrieved.

 

This mechanism improves query execution time by reducing compile time, but its behavior could lead to problems when data is not evenly distributed.

The presence of uneven data can cause performance degradation, a phenomenon known as Parameter Sniffing.

This is because an execution plan for parameterized queries is not optimal for the possible values ​​that these parameters can take.

I'll try to be clearer in the example below when i will show how the PSP feature works.

This is the scenario that the PSP feature wants to address.

Parameter Sensitive Plan (PSP) Optimization allows you to keep multiple active execution plans in the plan cache for a single parameterized query. 

Each execution plan will be optimized and will host different data sizes depending on the values ​​assumed by the parameters.

Whenever it detects the need to use an execution plan other than the one present in the cache for a parametric query, SQL Server will calculate the optimal execution plan for the current values ​​assumed by the parameters.

 

Let's see how this feature works with an example

Using the PSP, the Example

For this example I will use the table ORDTES we already used many times in many previous posts.

This table represents a list of orders. For simplicity, it has only the necessary fields useful for the example: year and the name of the customer.

 

CREATE TABLE ORDTES (YEAR Int, CUSTOMER_NAME Varchar(80) );

 

For this example, let’s populate the table with some non-uniform distributed data

For example the customer "Johnny Deep" has 2 orders while another one, Amber Heard, has a lot of orders. About 500.000 rows!

INSERT INTO ORDTES ([YEAR],CUSTOMER_NAME) VALUES (2022,'Johnny Deep')

INSERT INTO ORDTES ([YEAR],CUSTOMER_NAME) VALUES (2021,'Amber Heard')
GO 500000

Define the following two indexes:

CREATE INDEX IDX_ORDTES_YEAR ON ORDTES(YEAR)

CREATE INDEX IDX_ORDTES_CUTOMERNAME ON ORDTES(CUSTOMER_NAME)

Finally create this procedure.

CREATE PROCEDURE GET_DATA (@YEAR INT, @NAME VARCHAR(80) ) AS
BEGIN
  SELECT * FROM ORDTES WHERE [YEAR] = @YEAR AND CUSTOMER_NAME = @NAME
END

 

Ready for the test?

Execute the procedure just created with the following parameters:

EXEC GET_DATA @YEAR=2022, @NAME = 'Johnny Deep'
 

Johnny Deep has placed only one order and so we expect only a row as a result. 

Let's go and see inside ours plan cache with the following DMV:

SELECT
  usecounts
  ,plan_handle
  ,objtype
  ,text
FROM
  sys.dm_exec_cached_plans 
CROSS APPLY
  sys.dm_exec_sql_text (plan_handle)
WHERE
  text LIKE '%ORDTES%'
AND
  objtype = 'Prepared'

We currently have one execution plan:

If the new PSP feature is active (you need the CL equal to 160) you will see at the begin of the execution plan the label "PLAN PER VALUE":

This is the execution plan.

 

Ok, now let's look for another customer name!

This is the moment of "Amber Heard" and so execute the following SP

EXEC GET_DATA @YEAR=2021, @NAME = 'Amber heard'
 

She has 500k orders done in 2021 and the procedure will return 500.000 rows.

Note that we have a sniffing problem reusing the same execution plan 

The exection plan created uses an index seek; this time instead it would not be the best way.

 

Hey but we have the new SQL Server 2022!

So execute the SELECT and take a look to the execution plan.

This time the exection plan is not reused and the scan of the table is done! Great!

Taking another look at the plan cache, we can see that we have multiple execution plans for a single query!

 

The Parameter Sensitive Plan Optimization is running fine.

That's all for today but Stay tuned! 

 

Another post will follow to explain the details more in depth and to show some benchmarks.

But man, hey this is a great achievement!

 

 

Stay tuned and click on follow me on linkedin please! Luke!

 

If you find an advertisement that genuinely interests you, simply click it from on my blog. 

You will help me keep writing more and more interesting content. ~Luke

 

 

Previous post: SQL Server, Extract the row values of which another field has maximum value