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?