The Microsoft SQL Server query optimizer is a cost based optimizer. This means it will determine the best method to execute your SQL command based on information that it has about the database objects. At times, the optimizer does not know best and you need to give a hint to nudge it in the right direction to give you the most optimal performance. The index hint is one of the easiest hints to use and can yield the best results.
For a recent client, I was working on optimizing a complex query. I had eliminated table scans, cluster index scans and non-clustered index scans. The query plan looked great, but it was still taking too long to execute. When analyzing the IO statistics, I still had a table producing over 1 million scan counts and close to 2 million logical reads.
Table 'PTC_TableXYZ'. Scan count 1004904, logical reads 1997567, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0
I knew that I had created an index to cover the data. When I looked at the query plan step for that table, I noticed the optimizer was not picking up my new index. SQL Server’s optimizer continued to pick another index because it had a slightly lower cost. I decided to try and force the query to use the new index using the “WITH (INDEX(..))” hint. Now when looking at the IO statistics, scan counts and logical reads were dramatically reduced by 99%.
Table 'PTC_TableXYZ’. Scan count 102, logical reads 301, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Given that this particular query was executed tens of thousands of times, the over impact of this change improved internal SQL Server memory performance and reduced the query duration by 10% for each execution.