Tread Carefully With Index Hints

There may come a time when you find SQL Server not coming up with the most efficient plan for executing a query. What happens when you have an index that is getting left out even though it would be an improvement over what is chosen by SQL Server? For these situations, you can use index hints.

Everything is Fine, Until it’s Not

We’ll use the StackOverflow2013 database for our example. Let’s say someone wanted to see who has more than 10000 UpVotes in the Users table. We can run this:

SELECT DisplayName, UpVotes FROM Users
WHERE UpVotes > 10000
ORDER BY UpVotes DESC;
GO

Looking at the execution plan, we see the PK_Users_ID clustered index is scanned to get our results. Someone thought they could do better, so they created an index to try improving the query:

CREATE NONCLUSTERED INDEX [Users_UpVotes]
ON [dbo].[Users] ([UpVotes]);

If we run the same query again, we see the execution plan used the new index:

Hypothetically, it may be determined that the Users_UpVotes index is preferred so much so that it should always be used for this query regardless of what SQL Server thinks is best. SQL Server can be forced to use this index by modifying the query:

SELECT DisplayName, UpVotes FROM Users
WITH (INDEX(Users_UpVotes))
WHERE UpVotes > 10000
ORDER BY UpVotes DESC;
GO

The Best You’ve Got?

This might be good enough…for now. The potential issues with index hints can be more about the future than the present. You might come along later on and think “why not use an index to cover the whole query?” We can add the index:

CREATE NONCLUSTERED INDEX [Users_UpVotes_DisplayName]
ON [dbo].[Users] ([UpVotes])
INCLUDE ([DisplayName]);

But if our query is still written to include the index hint (in a stored procedure for example) the new index is not going to matter. The old index is still forced to be used. Even if something better comes along, you’re going to need to modify the query in addition to adding the better index. If an index was added for a completely separate query but would also be an improvement for the query in question, it’s also not going to get by the index hint.

Another concern to watch for with index hints is making sure that the referenced index doesn’t get disabled or dropped. If we still have our index hint in place but we disable the Users_UpVotes index, look what happens when the query runs:

Msg 315, Level 16, State 1, Line 15
Index “Users_UpVotes” on table “Users” (specified in the FROM clause) is disabled or resides in a filegroup which is not online.

Or if we drop the index completely:

Msg 308, Level 16, State 1, Line 11
Index ‘Users_UpVotes’ on table ‘Users’ (specified in the FROM clause) does not exist.

Our query is looking for the Users_UpVotes index and an error is getting thrown if that index can’t be used. If an index is referenced in a hint, it must be available. Index hints can be useful but should not be considered “set it and forget it” fixes.

Index Hints for Testing

If you’re creating multiple indexes trying to find what is most efficient, index hints can be helpful to quickly compare and contrast. If we set statistics IO on and stick with our previous example, we can run the following which will force the use of each index:

SELECT DisplayName, UpVotes FROM Users
WITH (INDEX(PK_Users_Id))
WHERE UpVotes > 10000
ORDER BY UpVotes DESC;
GO

SELECT DisplayName, UpVotes FROM Users
WITH (INDEX(Users_UpVotes))
WHERE UpVotes > 10000
ORDER BY UpVotes DESC;
GO

SELECT DisplayName, UpVotes FROM Users
WITH (INDEX(Users_UpVotes_DisplayName))
WHERE UpVotes > 10000
ORDER BY UpVotes DESC;
GO

Observing execution plan differences:

Observing IO differences:

Thanks for reading!

One thought on “Tread Carefully With Index Hints”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s