A Reminder
With a new baby in the house, I often find myself comparing her to various things.
Barnacles. Ham. Pugs.
No, I swear, I love her.
SQL Server Has A Baby, Too
That baby is the optimizer, and when it starts crying, it uses missing index requests to tell you something is wrong.
Usually.
See, when you hear a baby crying, it’s your job to go figure out what’s wrong. It could be anything.
Hungry. Tired. Dirty diaper.
You don’t know why, you just know that it’s crying and you need to stop it so you can maybe get another couple hours of sleep before dear god how is it already 5am?
It’s good to pay attention to a crying baby, but it’s not good to just do everything that might make it stop crying.
Sometimes Babies Are Quiet
Sneaky babies!
See, if things are just okay, they might not make a peep.
This is your proudest parental moment.
Quiet baby.
Too quiet.
Let’s check that diaper.
Dads, Moms, and SQL
Let’s say we’ve got an index. This index is keeping our baby quiet.
1 |
CREATE INDEX ix_whatever ON dbo.Posts(CreationDate, Score) INCLUDE(OwnerUserId); |
Let’s also say we’ve got some queries that run that…
You know, I’m past trying to come up with a good reason for these queries.
They look good. That’s what counts.
Quiet blogger.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
SELECT p.OwnerUserId, p.Score FROM dbo.Posts AS p WHERE p.CreationDate >= '20070101' AND p.CreationDate < '20181231' AND p.Score >= 5000 AND 1 = (SELECT 1) ORDER BY p.Score DESC; SELECT p.OwnerUserId, p.Score FROM dbo.Posts AS p WHERE p.CreationDate >= '20070101' AND p.CreationDate < '20181231' AND p.Score >= 10000 AND 1 = (SELECT 1) ORDER BY p.Score DESC; SELECT p.OwnerUserId, p.Score FROM dbo.Posts AS p WHERE p.CreationDate >= '20070101' AND p.CreationDate < '20181231' AND p.Score >= 25000 AND 1 = (SELECT 1) ORDER BY p.Score DESC; |
Most people looking at these query plans would be happy.

Our biggest problem is that we got uh… 2MB memory grants, and used 80KB.
Yep.
Happy, happy little Seeks. Even those Sorts cost nothing.
Too quiet.

This Seek really had to Scan.
See, you’d think that with an ever-increasing predicate (from 5000 to 25000), we’d be able to do less reads as we restricted more rows.
That didn’t happen.
All three queries do this: Table 'Posts'. Scan count 5, logical reads 136856
In other words:

Go To Sleepy Little Baby
What happened, here? Things were okay enough. The baby was not crying.
Even though the baby had spit up all over its Nick Cave onesie.
Silly baby.
Not all predicates are created equal, even inequality predicates, which are usually treated unequally.
Despite still needing to read a bunch of data, and being expensive enough (116 Query Bucks) to go parallel, the optimizer sees no opportunity to do better.
Really, the list of “despites” could go on a lot longer, but real life crying babies call.
If we shuffle our index slightly, we get interesting changes. The original index was on CreationDate, Score, but instead let’s flip the field order:
1 |
CREATE INDEX ix_whatever ON dbo.Posts(Score, CreationDate) INCLUDE(OwnerUserId); |
Here are the new query plans:

Stuff that happened:
- Query cost dropped way down
- Didn’t need to go parallel
- Didn’t need to Sort data
- Only did 5 reads:
Table 'Posts'. Scan count 1, logical reads 5
The Seek predicate is even fairly interesting!

The estimate is a touch off, but that’s fine. We only read one row.
We’re also able to start the beginning of our seek on CreationDate after finding our starting Score.
In other words:

Will be the title of my next blog post.
Thanks for reading!
9 Comments. Leave new
Oh, how the tables have turntables…
Great post Brerik!
I’m curious. Does moving the Score field to the first part of the Index key work better because it is only referenced once in the predicate, or is it because Score is used in the sorting??
Isaac — partially yes about the sorting, but more because Score is the more selective predicate, even though both are inequalities.
I think this comment is important enough to be included somewhere in the article itself. I had similar confusion about the reasoning behind the ordering of the index columns.
A Best – in the great tradition of Charles Dickens it’s a serial and you will have to wait for the next installment to get the solution to the cliffhanger :-
“Gee Bill ! – how come your seek lets you eat two predicates”
Will be the title of my next blog post.
I’ve oft been accused of Dickensian tradition.
Imma need to see this Nick Cave onesie you mentioned
Cmon Erik – the suspense is killing me …
[…] A while back I promised I’d write about what allows SQL Server to perform two seeks rather than a seek with a residual predicate. […]