Forcing an Index for a query

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.

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