SQLBits 2023

Parameter Sensitive Plan Optimization in SQL 2022 ... As Cool as it Sounds?

We'll provide a balance perspective of the benefits and limitations of the new Parameter Sensitive Plan Optimization feature in SQL 2022 to help you decide if it's right for your environment.
Since SQL Server 2017, Microsoft has added improvements in query processing in every new version. Improvements designed to make your queries simply run faster, in many cases with no code change needed.

One of the query processing features added in SQL Server 2022 is Parameter Sensitive Plan Optimization, or PSPO for short. PSPO is designed to automatically improve the performance of queries that in earlier versions of SQL Server would suffer from bad parameter sniffing.

But does it work?

Find out in this session! Erin Stellato (SQL Experiences PM at Microsoft) will explain how the feature works and demonstrate how it can address bad parameter sniffing without code changes or DBA action, while Hugo Kornelis (MVP) is the louse in the pelt who will point out potential issues and existing shortcomings of the feature.

Our goal is to have you leave the session with a balanced knowledge of both the benefits and the drawbacks of PSPO so that when you upgrade to SQL Server 2022 (or when it arrives in Azure SQL DB), you will know how to get the maximum benefit while also working around its limitations.