Getting Weirder
In Part 1, we looked at how query plans can tell us little lies.
Now we’re going to look at how those little lies can turn into bigger lies.
Adding An Index
Right now, this is our query:
|
1 2 3 |
SELECT u.UpVotes, u.DownVotes FROM dbo.Users AS u WHERE u.Reputation >= 100000; |
If we add this filtered index, our query will have a grand ol’ time using it;
|
1 2 |
CREATE INDEX ix_pall_mall ON dbo.Users (UpVotes, DownVotes) WHERE Reputation >= 100000; |
Leaving aside my own advice about filtered indexes, what happens to the query plan now?
More lies! We can see in the query plan that our filtered index was used.
But… we have a warning that it wasn’t. Why?
Because filtered indexes don’t get along with variables.
But this plan is still lying to us.
This time we have no trivial plan, and we have another Unsafe Auto-Parametization.
What a headache.
Now what does the plan cache tell us?
No. Really?
The plan is still showing us a warning, even though we see a literal in the cache.
This is obviously wrong. And very confusing.
Is This Documented Anywhere?
In the year of our SMOD, 2011, it was written:
SQL Server attempts to auto-parameterize queries and sends the auto-parameterized query to the query processor. Now the query processor decides if it can generate a potentially better plan given the constant values instead of the auto-parameterized query. If the query processor cannot find a better plan with the constant value plugged in, it means that the parameterized plan can be used across different parameter values. This parameterization is considered ‘safe’. In this case we cache both the parameterized and the shell query. However if the query processor can generate a better plan with the literal values, then this parameterization is considered ‘unsafe’. The query is parameterized and the shell query is cached. The parameterized query is not cached and is not visible through any of the plan cache DMV’s. The shell query points to the parameterized query and this is the only way to get to the parameterized query. In other words, unless the exact same query (and parameter value) is re-executed there is no chance of re-using this ‘unsafe’ parameterized query. Queries that already have explicitly defined parameters are not auto-parameterized.
Got that? There are shells. They’re invisible. But trust me, they’re there.
You know it when you step on them.
Thanks for reading!
