Index Tuning Week: Missed Missing Index Opportunities

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.

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.

Most people looking at these query plans would be happy.

Happy little seeks.

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.

Butters.

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:

Rubberneckin’

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:

Here are the new query plans:

NQPZ

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!

Sigmund, get my pipe

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:

Because.

Will be the title of my next blog post.

Thanks for reading!

Previous Post
Index Tuning Week: Getting Blocking? Play “Hot or Not.”
Next Post
[Video] Office Hours 2018/10/10 (With Transcriptions)

9 Comments. Leave new

  • Little Bobby Tables
    October 12, 2018 9:19 am

    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.

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

  • Imma need to see this Nick Cave onesie you mentioned

  • Stephen Morris
    October 26, 2018 4:00 am

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

Menu
{"cart_token":"","hash":"","cart_data":""}