How does including a SQL index hint affect query performance?

Question

Say I have a table in a SQL 2005 database with 2,000,000+ records and a few indexes. What advantage is there to using index hints in my queries? Are there ever disadvantages to using index hints in queries?

Answer 1

First, try using SQL Profiler to generate a .trc file of activity in your database for a normal workload over a few hours. And then use the "Database Engine Tuning Advisor" on the SQL Server Management Studio Tools menu to see if it suggests any additional indexes, composite indexes, or covering indexes that may be beneficial.

I never use query hints and mostly work with multi-million row databases. They sometimes can affect performance negatively.

Answer 2

The index hint will only come into play where your query involves joining tables, and where the columns being used to join to the other table matches more than one index. In that case the database engine may choose to use one index to make the join, and from investigation you may know that if it uses another index the query will perform better. In that case you provide the index hint telling the database engine which index to use.

Answer 3

My experience is that sometimes you know more about your dataset then SQL Server does. In that case you should use query hints. In other words: You help the optimizer decide.

I once build a datawarehouse where SQL Server did not use the optimal index on a complex query. By giving an index hint in my query I managed to make a query go about 100 times faster.

Use them only after you analysed the query plan. If you think your query can run faster when using another index or by using them in a different order, give the server a hint.

Answer 4

The key point that I believe everyone here is pointing to is that with VERY careful consideration the usage of index hints can improve the performance of your queries, IF AND ONLY IF, multiple indexes exist that could be used to retreive the data, AND if SQL Server is not using the correct one.

In my experience I have found that it is NOT very common to need Index hints, I believe I maybe have 2-3 queries that are in use today that have used them.... Proper index creation and database optimization should get you most of the way there to the performing database.