When Query Plans Lie Part 1

Last Updated 6 years ago

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.

	SELECT u.UpVotes, u.DownVotes
	FROM dbo.Users AS u
	WHERE u.Reputation >= 100000;

1

2

3

SELECT u.UpVotes, u.DownVotes

FROM dbo.Users AS u

WHERE u.Reputation >= 100000;

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.

	SELECT *
	FROM sys.dm_os_performance_counters AS dopc
	WHERE dopc.counter_name LIKE '%Unsafe Auto-Params/sec%';
	GO 

	SELECT * 
	FROM sys.dm_exec_query_optimizer_info AS deqoi 
	WHERE deqoi.counter = 'trivial plan';
	GO

1

2

3

4

5

6

7

8

9

SELECT *

FROM sys.dm_os_performance_counters AS dopc

WHERE dopc.counter_name LIKE '%Unsafe Auto-Params/sec%';

GO

SELECT *

FROM sys.dm_exec_query_optimizer_info AS deqoi

WHERE deqoi.counter = 'trivial plan';

GO

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.”

3 Comments. Leave new

David Forck
May 7, 2018 2:20 pm

the ozar.me link is broken

Reply
- Erik Darling
  May 7, 2018 2:29 pm
  
  It’s working for me.
  
  Reply
Simple Parameterization Isn’t – Curated SQL
May 8, 2018 7:00 am

[…] Erik Darling ran into an interesting case with simple parameterization: […]

Reply