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.