Index Hints

While modifying a function one day. I noticed that a SELECT statement was running slow.
Looking at the execution plan, I found that 90% of the performance was consumed by one Key Lookup on RPT_ACTION_TEST.
After reviewing the function I saw this.
FROM [Database].[dbo].[RPT_ACTION_TEST] O WITH (INDEX(PK_IDX_ACTION_3))

I reviewed the index that is used and it does not include the STATUS_DATE or ACTUAL_DATET. That is why you should not use the WITH statement to force an index. I had already created an index that would work great with this but it won’t get used. IDX_RECORD_STAGE includes everything the query would need to be freaky fast.

By removing the WITH hint, now it is able to use the IDX_RECORD_STAGE index.
This change increased the speed and it no longer needs the Key Lookup.
Execution time went from 20 seconds to 3 seconds.

Before using Index scan and Key lookup:

Before

After using Index Seek:

After

Please read this article for more information.

https://www.brentozar.com/archive/2013/10/index-hints-helpful-or-harmful/

Leave a Reply

Your email address will not be published. Required fields are marked *