When Query Plans Lie Part 1

Freaking Out Brent

Is a full time job. Some days I can get him to hit the desk drawer tequila, and other days I crash and burn.

This day was so monumental I almost hired someone to do an action movie narration of this blog post.

Let’s Start Easy

Over in Stack Overflow, we’re going to run a query and get the Actual Factual Plan.

With no indexes, this query only has one possible plan. It has to scan the clustered index, and filter out rows that don’t meet the Reputation criteria.

The query plan shows us that we got a Trivial Plan, and that Simple Parameterization was attempted (as shown by the 100000 literal turning into the @1 parameter.)

Simple Parameterization is only attempted in a Trivial Plan.

The key word here is attempted.

Validation

There are some interesting things that don’t happen when you get a trivial plan.

The first is that missing index requests won’t appear. Not that the requests themselves are all that good, but hey.

The second thing is that certain optimizations aren’t considered — for instance, column store prior to 2017 had some issues.

We can also check using a couple system views.

Unsafe parameterizations will tell us if Simple Parameterization was attempted and rejected, and we can see if a trivial plan was used with that second query.

These counters are cumulative, so don’t run them once and freak out.

If I check in on those around another run of my query, the results look about like this.

I see that both ticked up! I got a trivial plan, but the optimizer decided that parameterization wasn’t such a hot idea. (That directly contradicts what you saw in the plan, which implied that parameterization wasn’t just attempted, but that it was completed successfully.)

The query was simple enough, but cardinality estimation gives it a nudge — a good plan for this value may not be awesome for the next value.

This only happens with literals.

Lesson One: Mistrust

The query plan that SSMS showed us said the plan was Trivial, and Simple Parameterized.

The DMVs told us otherwise — Parameterization was attempted, but not used.

What does the plan in the plan cache imply?

It implies that the query was successfully parameterized.

What have we learned so far?

• Trivial Queries get Trivial Plans
• Simple Parameterization can only occur in a trivial plan
• Trivial Plans end optimization early and skip some phases
• Simple Parameterization can be rejected by the optimizer after query compilation
• Actual Plans are still Estimated Plans

In Part 2, we’ll look at where this can be even more confusing.

Thanks for reading!

Brent says: because of my standing desk setup, I don’t actually have desk tequila. But yes, as we worked through this one, I did indeed walk to the kitchen and pour myself a drink. Every time that I learn another way that execution plans lie to me, I say, “This is why I drink.”