Index Tuning Week: Missed Missing Index Opportunities

Last Updated 5 years ago

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.

CREATE INDEX ix_whatever ON dbo.Posts(CreationDate, Score) INCLUDE(OwnerUserId);

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.

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;

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:

CREATE INDEX ix_whatever ON dbo.Posts(Score, CreationDate) INCLUDE(OwnerUserId);

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

Little Bobby Tables
October 12, 2018 9:19 am

Oh, how the tables have turntables…
Great post Brerik!

Reply
Isaac Koss
October 12, 2018 3:23 pm

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??

Reply
- Erik Darling
  October 13, 2018 6:18 am
  
  Isaac — partially yes about the sorting, but more because Score is the more selective predicate, even though both are inequalities.
  
  Reply
  - A. Best
    October 15, 2018 11:19 am
    
    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.
    
    Reply
    - Stephen Morris
      October 16, 2018 2:42 am
      
      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.
      
      Reply
      - Erik Darling
        October 16, 2018 7:32 am
        
        I’ve oft been accused of Dickensian tradition.
Simon
October 16, 2018 8:11 am

Imma need to see this Nick Cave onesie you mentioned

Reply
Stephen Morris
October 26, 2018 4:00 am

Cmon Erik – the suspense is killing me …

Reply
The Importance Of Index Column Order – Curated SQL
January 29, 2019 5:05 am

[…] 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. […]

Reply