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

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

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