It’s time to finish the triptych on bad parameter sniffing, and how Microsoft tries (and fails) to fix this for you.
After first talking about bad parameter sniffing in general, I used my last video to explain Parameter Sensitive Plan Optimization, the feature that Microsoft released in SQL Server 2022 as an attempt to fix one of the three root causes for bad parameter sniffing. If you want to see why I consider that a failed attempt, go watch that video.
In SQL Server 2025, Microsoft then added Optional Parameter Plan Optimization (OPPO). Their attempt to fix the second of those three root causes. Put where PSPO in most cases simply doesn’t do anything, or does do something, but not enough, OPPO does too much. It fixes bad parameter sniffing due to optional parameters in demos that are carefully curated to showcase the feature, but it does more bad than good in more realistic scenarios.
OPPO, making the bad even worse
The video starts with an explanation of how the multiplan infrastructure, that was introduced for PSPO, has been extended in SQL Server 2025 to support different plans based on the runtime value of optional parameters. I then show this in a demo, on a carefully curated example where all goes as we like it to, and you can see how the feature helps you if all stars align.
But I then continue the demo with a different (and more realistic example). This will show the limitations of the feature, as well as its innate instability, resulting in a totally new kind of bad sniffing problem, that might make erratic performance issues even harder to troubleshoot.
The demo then continues to show what happens in queries where PSPO and OPPO can both be applied, to show how an upgrade to SQL Server 2025 might cause regression if you don’t disable OPPO. After that, I look at a few very common ways to handle optional parameters in T-SQL code that OPPO does not even recognize!
After the demo, I wrap up what I have shown, resulting in my advice to disable OPPO unless testing shows that it does actually help you in your specific situation.
My apologies for the sound quality. I had my good microphone in front of me, but apparently, I had selected one of my other microphones to be recorded! Stupid mistake. Plus, for some reason, the last bit of the recording was cut off, so I had to re-record that part. This time, I did select the good microphone. So if you watch until the end of the video, you will not only have learned why I recommend disabling the OPPO feature, but also heard how much difference in sound quality a microphone can make!
More of me?
Was this useful to you? Do you want to learn more from me?
You can click here to see an overview of my scheduled conference talks. If you attend one of those, you get a lot of other presenters and sessions thrown in as well!
To learn really everything about SQL Server execution plans, check out my video training. Over 20 hours of super high quality video content (and more will be added when ready).
Or you can hire me to fix the performance problems on your server, or for in house training.
6 Comments. Leave new
Test 2
Apologies for these test comments. @Hugo; I also sent you a message through your regular contact form.
These comments did go through. I also received your comment from the contact form, with the screenshot of the error you received at first. I don’t know why you got that error. I see no reason for it to happen.
I checked the comment moderation queue, but your comment also did not make it there. So, no idea what happened.
I hope that your future comments will get through, like the two above.
The OPPO parameter breaking the previously working three-parameter PSPO setup is indeed a real risk when migrating to 2025, I haven’t thought about it before. Thanks a lot for the great demos Hugo!
I work primarly with a DWH and many procedures have an optional parameter for @file_id (WHERE @file_id IS NULL OR tbl.file_id = @file_id).
For those scenarios OPPO is excellent (and made). Not for your report grid, where the user could specify any of 20 filter paramters that are all optional.
I worked with those too, but in that case the application who launched the query removed / commented out all empty parameters that where not specified by the user befor launching it.
7 optional parameters could lead to 7^6 = 117,649 combinations and would float the query store when they would allow it per design. When your app or stored procedure doesn’t support some sort of dynamic query composition – feel free to still use the RECOMPILE option.
Yes. For simple cases like yours, with just a single optional parameter, the OPPO feature does indeed work. That is a good addition.
Your math is a bit off for the number of possible combinations. Every parameter can be NULL or NOT NULL. With 7 parameters, that works out to 2^7 combinations – 128 total. Still a big number. But in practice, not all 128 combinations will be used. OPPO will only create plans for combinations that are used. So if OPPO were changed to support more parameters, it would in most cases create far less than 128 plans.
With the current state of OPPO, you will indeed have to use dynamic SQL instead. (Which, incidentally, results in the same number of plans in the plan cache – just as individual dynamic queries, and not easily recognized as variants of effectively the same query).