Bad Idea Jeans: Multiple Index Hints

I hate that you can do this

Here’s the thing: I’m mostly writing this because I didn’t know you could do it.

But it’s cool, because it’ll reinforce some other concepts, and I’ll show you why you shouldn’t do it.

I’m talking, of course, about index hints. To be more specific, hinting multiple indexes on the same table.

Setup, setup

If I run this query and force this index, I end up with kind of a gross key lookup plan.

Aight blah

This, of course, happens because we need to show users c3, but it’s not part of the index we forced.

Ootpoot

Notice that it’s only for c3! Hooray knowledge reinforcement. We know that clustered index key columns are part of every nonclustered index.

Here’s the part I hate

I can force SQL to use BOTH NONCLUSTERED INDEXES.

See? See that?

Hey optimizer, you gotta use both these things.

Why?

Because I said so.

Oh good, we avoided a key lookup. Sort of.

This is called index intersection. It’s come up on the blog before. It’s normally where the optimizer chooses (keyword here is “chooses”, not “is forced to”) to use two nonclustered indexes in a key lookup type scenario.

Again, it uses the clustered index key column present in both nonclustered indexes to ‘join’ them together.

Hashbox 20

This will, of course, fall all to pieces if we add the fourth column into the mix.

Whyyyyy? Stop.

Now we’re on a two nonclustered index intersection plus a key lookup back to the clustered index plan, which is arguably worse than just a single key lookup plan if we were just forcing the optimizer to use one index.

The key lookup here is of course to get c4, which is not part of either nonclustered index.

But only c4!

Very special

See that seek predicate on c1? That’s our clustered index join again.

What about HEAPs

If we start over, and create our demo table with a nonclustered primary key this time (side note: you wouldn’t believe how many emails we get from people saying that sp_BlitzIndex is wrong about their table being a HEAP because it has a primary key on it).

All of the same mechanics are possible, but now every plan has a RID lookup in it (that’s a row identifier, and that’s how SQL identifies rows in HEAPs).

Since c1 isn’t automagically part of every nonclustered index now that it’s not the clustered index, we need to go back to the HEAP to fetch that column.

No thanks.

Drilling into the RID lookup of the plan where I force index intersection, it shows us this, once again confirming our super awesome knowledge about clustered indexes.

You are garbage.

Fields

Like I said, this was mostly a vehicle for the multiple index hint thing, but I hope you learned something valuable along the way.

Thanks for reading!

, , ,
Previous Post
Building a Faux PaaS, Part 3: What the Ideal Engineering Team Looks Like
Next Post
[Video] Office Hours 2017/05/24 (With Transcriptions)

5 Comments. Leave new

  • Paul Holmes
    May 26, 2017 1:29 pm

    Ah…. There’s one use I can think of – forcing index intersections, just for research purposes. Nice! In a never-in-real-code style 😉

    Reply
  • Hi.
    I have to say that I hate to involves on sql engine coises.
    But sometimes it’s help ,specially in high oltp environment when one of the most uses procedure in the system changing its execution plan and cause to CPU over load.

    Reply
  • I copied the script from this post and executed step by step. Am unable to find the Parallelism in my execution plan.

    Can you please confirm me is execution plan is difference based on the editions. because am used the Express edition.

    Reply
  • I tried his years and years ago on SQL 2005 and it would not do it. Adding multiple index hints would just raise syntax errors. I was really annoyed. I had this treMENdous table that if I could get it to hash-join these two indexes it would have to go out to the table to fetch only 1 row that had the single value I needed.

    We eventually ended up having to tear down the entire structure of that table and rebuild it completely differently for other reasons.

    Reply

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":""}