Michael J. Swart

October 12, 2022

You Can Specify Two Indexes In Table Hint?

Filed under: SQLServerPedia Syndication — Michael J. Swart @ 12:00 pm

Yes, It turns out that you can specify two indexes in a table hint:

SELECT Id, Reputation
FROM dbo.Users WITH (INDEX (IX_Reputation, PK_Users_Id))
WHERE Reputation > 1000

And SQL Server obeys. It uses both indexes even though the nonclustered index IX_Reputation is covering:
Two Indexes

But Why?

I think this is a solution looking for a problem.

Resolving Deadlocks?
My team wondered if this could be used as to help with a concurrency problem. We recently considered using it to resolve a particular deadlock but we had little success.

It’s useful to think that SQL Server takes locks on index rows instead of table rows. And so the idea we had was that perhaps taking key locks on multiple indexes can help control the order that locks are taken. But after some effort, it didn’t work at avoiding deadlocks. For me, I’ve had better luck using the simpler sp_getapplock.

Forcing Index Intersection?
Brent Ozar wrote about index intersection a while ago. Index intersection is a rare thing to find in a query plan. Brent can “count on one hand the number of times [he’s] seen this in the wild”.

In theory, I could force index intersection (despite the filter values):

FROM dbo.Users WITH (INDEX (IX_UpVotes, IX_Reputation))
WHERE Reputation > 500000
AND UpVotes > 500000

But I wouldn’t. SQL Server choosing index intersection is already so rare. And so I think the need to force that behavior will be even rarer. This is not a tool I would use for tuning queries. I’d leave this technique alone.

Have You Used More Than One Index Hint?

I’d love to hear about whether specifying more than one index in a table hint has ever helped solve a real world problem. Let me know in the comments.


  1. If only you searched a little further back, you would have found a post by a much better looking BOU employee: https://www.brentozar.com/archive/2017/05/bad-idea-jeans-multiple-index-hints/

    Comment by Erik Darling — October 12, 2022 @ 2:52 pm

  2. @Erik,

    That is impressive.

    I feel comforted that we came up with the advice “I’d leave this technique alone” independently.

    Comment by Michael J. Swart — October 12, 2022 @ 4:48 pm

  3. […] Michael J. Swart is not satisfied with just one index: […]

    Pingback by Specifying Multiple Indexes in a Table Hint – Curated SQL — October 14, 2022 @ 8:00 am

RSS feed for comments on this post. TrackBack URL

Leave a comment

Powered by WordPress