Erik Darling continues a series on Parameter Sensitive Plan optimization. First up is a post on indexing:
Anyway, let’s use the example that I had started with here, to illustrate that the PSP optimization does work with a computed column, but… like any other column, indexes make all the difference.
I’m using the same example query over and over again, because a lot of the other great examples of parameter sensitivity that I have demo queries written for don’t seem to trigger it.
And sometimes there’s just nothing to do:
After seeing places where the Parameter Sensitive Plan (PSP) optimization quite stubbornly refuses to kick in, it’s somewhat amusing to see it kick in where it can’t possibly have any positive impact.
Even though some parameters are responsible for filtering on columns with highly skewed data, certain other factors may be present that don’t allow for the type of plan quality issues you might run into under normal parameter sensitivity scenarios:
This continues to be a very interesting look into one of the most-anticipated features in SQL Server 2022, as well as a necessary wet blanket for the hype.