Bad Idea Jeans: Multiple Index Hints

Last Updated 6 years ago

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

USE tempdb;

DROP TABLE IF EXISTS dbo.ForceFed; --2016+ only

CREATE TABLE dbo.ForceFed
(
  c1 INT PRIMARY KEY CLUSTERED,
  c2 INT,
  c3 INT,
  c4 INT
);

INSERT dbo.ForceFed WITH (TABLOCK)
     (c1, c2, c3, c4)
SELECT rn, rn % 2, rn % 3, rn % 4
FROM (
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
FROM sys.messages m1
CROSS JOIN sys.messages m2
) x;

CREATE NONCLUSTERED INDEX ix_ff_c2 ON dbo.ForceFed (c2);
CREATE NONCLUSTERED INDEX ix_ff_c3 ON dbo.ForceFed (c3);

RETURN;

USE tempdb;

DROP TABLE IF EXISTS dbo.ForceFed; --2016+ only

CREATE TABLE dbo.ForceFed

(

c1 INT PRIMARY KEY CLUSTERED,

c2 INT,

c3 INT,

c4 INT

);

INSERT dbo.ForceFed WITH (TABLOCK)

(c1, c2, c3, c4)

SELECT rn, rn % 2, rn % 3, rn % 4

FROM (

SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn

FROM sys.messages m1

CROSS JOIN sys.messages m2

) x;

CREATE NONCLUSTERED INDEX ix_ff_c2 ON dbo.ForceFed (c2);

CREATE NONCLUSTERED INDEX ix_ff_c3 ON dbo.ForceFed (c3);

RETURN;

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

SELECT ff.c1, ff.c2, ff.c3
FROM dbo.ForceFed AS ff WITH (INDEX(ix_ff_c2));

1 2	SELECT ff.c1, ff.c2, ff.c3 FROM dbo.ForceFed AS ff WITH (INDEX(ix_ff_c2));

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

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.

SELECT ff.c1, ff.c2, ff.c3
FROM dbo.ForceFed AS ff WITH (INDEX(ix_ff_c2, ix_ff_c3));

1 2	SELECT ff.c1, ff.c2, ff.c3 FROM dbo.ForceFed AS ff WITH (INDEX(ix_ff_c2, ix_ff_c3));

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.

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

SELECT ff.c1, ff.c2, ff.c3, ff.c4
FROM dbo.ForceFed AS ff WITH (INDEX(ix_ff_c2, ix_ff_c3));

1 2	SELECT ff.c1, ff.c2, ff.c3, ff.c4 FROM dbo.ForceFed AS ff WITH (INDEX(ix_ff_c2, ix_ff_c3));

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!

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

CREATE TABLE dbo.ForceFed
(
  c1 INT PRIMARY KEY NONCLUSTERED,
  c2 INT,
  c3 INT,
  c4 INT
);

CREATE TABLE dbo.ForceFed

(

c1 INT PRIMARY KEY NONCLUSTERED,

c2 INT,

c3 INT,

c4 INT

);

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.

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.

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!

Building a Faux PaaS, Part 3: What the Ideal Engineering Team Looks Like

[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
Itzik
May 27, 2017 2:12 am

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
Baazi
May 30, 2017 4:39 am

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
- Brent Ozar
  May 30, 2017 5:29 am
  
  Baazi – Express is single-core.
  
  Reply
Joshua
October 2, 2017 11:24 pm

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