As we know that SQL Server Optimizer always finds out the “best” plan possible within the given time frame.
There could be some use cases when you need to force an index to be used by SQL Server, I have done this many a times to study the actual reason why some specific Index is not being used by SQL Server. Sometime a normal WITH INDEX hint doesn’t work as SQL Server finds it unsuitable for getting an optimal plan.
If you want to know the reason behind why your Index is not being used force the query to use it, and compare it’s execution plan with the one SQL Server chooses as the best fit for your query. By analyzing the difference you might be able to figure out the reasoning of SQL Server in skipping your Index.
To force an Index use the below FORCESEEK, see the example below for it’s usage.
SELECT tb1.clm FROM tb1 INNER JOIN tb2 WITH (FORCESEEK, Index(indexname), NOEXPAND) On tb1.clm2 = tb2.clm2
Note: If you are forcing an Index and this Index cannot create the end results than your query will fail.