Parameter Sensitive Plan Optimization

In this article, I will explain what the PSP (Parameter Sensitive Plan Optimization) feature is.

I would like to point out that this feature will provide a huge performance boost and is one of the most important features of SQL Server 2022.

I predict that this feature will also solve the Parameter Sniffing problem we experienced in previous versions.

Of course, the Query Store feature, which comes actively with the SQL Server 2022 installation, will be a feature that we can actively use to improve query performances.

What is PSP?

The first time a query is run, the execution plan is created and stored in the plan cache.

When we run the query again, it improves the execution time of our query by reducing the compilation time since the execution plan exists.

However, it can cause performance issues when data is not evenly distributed.

This problem is called “Parameter Sniffing”.

Multiple execution plans are stored in the plan cache for single parameter queries thanks to PSP optimization.

Each execution plan will be optimized and contain different data sizes depending on the values assumed by the parameters,

so when you run the query again, your query will run on different execution plans stored specifically for your query and your query performance will be improved.

Of course, in parallel with this process, there is the fact that it can cause the plan cache to swell.

This should not be overlooked either.

With SQL Server 2022 currently in public preview, I’ll be talking about these as more details come in.