Behind Every Trivial Plan Is A Good Demo

Last Updated 6 years ago

A While Back

I started this Q&A on Stack Exchange about ISNULL. It’s one of the most common client issues I see, and putting stuff like that out in a different community to get more opinions on it is valuable.

Also, I’m saving up points for a Stack Overflow Swiss Army Knife. That’s what you do with points, right? Like Marlboro Miles?

Hiding Out

It wasn’t until I was looking at something else entirely, that I realized my starting query was a Trivial plan.

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Age < 18
OR     u.Age IS NULL;

1

2

3

4

SELECT COUNT(*) AS records

FROM dbo.Users AS u

WHERE u.Age < 18

OR u.Age IS NULL;

Now… Looking at this plan, you don’t really expect it to be Trivial. There’s kind of a lot going on there.

Via Paul White:

The details of which types of query can benefit from Trivial Plan change frequently, but things like joins, subqueries, and inequality predicates generally prevent this optimization.

Now, I’m not sure if the details have changed, or if this query is just much more Trivial than the syntax or plan make it appear.

But let’s go one step further.

Laying Low

Let’s run this query with a known Trivial plan killer.

SELECT COUNT(*) AS records
FROM   dbo.Users AS u
WHERE  u.Age < 18
OR     u.Age IS NULL
AND    1 = ( SELECT 1 );

1

2

3

4

5

SELECT COUNT(*) AS records

FROM dbo.Users AS u

WHERE u.Age < 18

OR u.Age IS NULL

AND 1 = ( SELECT 1 );

The plan changes quite a bit, and uses a far less common strategy called Index Union.

This is a far more interesting plan, and I was a bit surprised to see it.

If I tried for a week to write a query that got this plan on its own, I’d fail utterly.

Plan Collector

I wish I had this plan in time for this post. It is indeed a sort we did not ask for.

The optimizer chose something between index intersection and sort-merge and a weird kinda-but-not-really Key Lookup.

It performs two ordered seeks into the same index, sorts the side with the inequality predicate on Id, and merges the two inputs together.

For those wondering, the seek without the sort doesn’t need one, because our index is just on Age, and is non-unique.

That means the clustered index key column (Id) is in the key of the index, and since IS NULL is an equality, the Id column is ordered by it next.

After that are Stream Aggregates to do some grouping and counting.

And to think, all this interesting stuff was hiding just a couple search phases beyond a Trivial plan.

Thanks for reading!

Brent says: when people see this, their hands immediately shoot up in the air to ask, “Should I add WHERE (1 = SELECT 1) to all my queries?” I lean towards no on that one because most of your trivial queries just don’t need full optimization, and that further compilation takes longer. I think I can count on one hand the number of times in the last couple years where an under-optimized trivial plan was the main cause of a client’s problems, and I wasn’t even lucky enough to hit that myself. Tara hit it, and the rest of us were all excited to see it, hahaha.