Parameter Sensitive Plan Optimization, Why?

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

One question that we hear quite often is why we chose to capitalize the first three letters when abbreviating this feature sometimes, and at other times, we chose not to. For example, why only abbreviate it like PSP optimization, and not just PSPO?

Well, I have some breaking news to share, PSPO keeps getting better. 

As one of the new members of the growing Intelligent Query Processing (IQP) family, PSPO has had a few growing pains. But PSP (see, there I go with the short abbreviation inconsistency again) is continuing to take steps towards addressing one of the most common performance challenges in SQL Server, bad parameter sniffing. Not all parameter sniffing is bad, and PSPO, while in its introduction to the database world, has taken a more conservative approach when it comes to dealing with bad parameter sniffing. But this blog post is not

so much about parameter sniffing, or PSP optimization fundamentals. For that, please feel free to refer to a high-level introduction to the feature - Intelligent Query Processing: feature family additions - Microsoft SQL Server Blog, afterwards head over to our official documentation page for a slightly deeper dive. Ok, if this post is not about what PSPO is, or what PSP is not, or what are the future plans for PSPO (i.e. when will it expand its current scope in order to address other types of workloads); then why does this blog post even exist? Great questions, please continue to ask them and do not forget to even post them and more to our Ideas site (https://aka.ms/sqlfeedback).

This blog post is here to share how PSPO keeps getting better with your feedback and why we hope that you will agree.  

Several sentences back, there were some references to our official documentation page and the Azure Ideas site. One of the things that we have added to the documentation page for PSP is a Known Issues section. This was added to ensure that the SQL community was aware of major issues that we have been tracking and fixing for the current version of PSPO. One issue that we have been working on has also been tracked since April of 2023 on the Azure Ideas site - sql data store is causing errors on sql server 2022 · Community (azure.com). 

Ok, why is the relevant to this blog post and what does this have to do with PSP optimization getting better? Well, starting with the release of Cumulative Update 4 for SQL Server 2022, we have released several fixes for some of the errors that some of you in the SQL community may have encountered while using PSPO. The optimal word is some, however, we are continually improving the product and are continuing to address all the issues that we are aware of, post Cumulative Update 4. 

Why are there bugs in the product at all? I’ll save my response to that question for another blog post. 

Why were these fixes not mentioned in the Knowledge Base article for Cumulative Update 4 for SQL Server 2022? Another great question. The short answer is that we were focusing on delivering additional improvements. I’ll add a few words in the same format as our Cumulative Update notes use about the fixes that have been included as part of Cumulative Update 4 for SQL Server 2022 below: 

As we continue to make the Parameter Sensitive Plan Optimization feature more reliable and more robust, I hope you find this blog helpful and that you will continue this journey along with us in alleviating the negative performance effects of bad parameter sniffing. Oh, and before I forget, we still strongly recommend that you apply the latest Cumulative Updates for SQL Server as we ceaselessly improve the supportability, reliability, and manageability of the product.