SQL Server 2022 – Parameter Sensitive Plan Optimisation

By Ryan Freeman

October 3, 2022

SQL, SQL 2022, sql server

Welcome to the first of our feature-specific posts for SQL Server 2022.

This week we are looking at Parameter Sensitive Plan optimisation, or PSP optimisation for short (yes, it’s not PSPO, but we don’t think you should blame us for this)! 

When will we have SQL Server 2022?

For those who don’t know, SQL Server 2022 is coming and should be released later in 2022.

At the time of writing, you can now download SQL Server 2022 preview to try the new features in the latest release.

‘SQL Server 2022 is the most Azure-enabled release of SQL Server yet, with continued innovation across performance, security, and availability.’ – Microsoft. 

What will PSP Optimisation help solve?

PSP optimisation is a new feature that Microsoft introduced to try and fix parameter sniffing.

When SQL Server first executes a piece of code, it uses (sniffs) any parameter values to generate an execution plan which is then cached.

Creating an execution plan can be expensive, so SQL Server tries to reuse them whenever possible. 

How does PSP Optimisation work?

PSP optimisation will allow you to keep multiple active execution plans in the plan cache for a single parameterised query; each execution plan will be optimised and host different data sizes depending on the values assumed by the parameters.

Whenever SQL Server detects the need to use an execution plan other than the one saved in the plan cache for a parameterised query, it will calculate the optimal execution plan for the current parameter values. 

Hopefully, that is an excellent introduction to this new feature; next week, we’ll be looking at Query Store enhancements, so stay tuned! 

Are you excited by the new version of SQL 2022? At Digital Samurai, we certainly are! We would love to hear your thoughts. 

It may also benefit you to know that on the 27th of July 2022, extended support ended for SQL Server version 2012. Is this a version you are using, or any prior? 


If you have any questions on SQL Server 2022 or upgrading to the later versions of SQL Server, please feel free to get in touch; we will be delighted to assist. 

Digital Samurai are a team of data and SQL experts that have experienced every version through the years – yes, back to SQL Server 6.0!

We also have a wealth of experience in upgrades and data migrations and are always available to help. 

Ryan Freeman

About the author

You might also like

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}