October 3

0 comments

SQL Server 2022 – Parameter Sensitive Plan Optimisation

By Ryan Freeman

October 3, 2022


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 don’t blame us)! 

When will we have SQL Server 2022? 

For those of you 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 which Microsoft has 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 re-use 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 will 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 parameters values. 

Hopefully that is a good 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 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, then please feel free to get in touch and we will be very happy to assist. 

Digital Samurai are a team of data and SQL experts that have that have experienced every version through the years – yes, all the way back to SQL Server 6.0! We also have a wealth of experience in upgrades and migrations and are always on hand to help. 

Ryan Freeman

About the author

You might also like

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