-- Allows SQL Server query optimizer
SELECT COUNT(*) FROM dbo.tbTable01 WHERE Id BETWEEN 1 AND 4000000

-- Force the use of a specific index
SELECT COUNT(*) FROM dbo.tbTable01 WITH (INDEX(XPK_tbtbTable01)) WHERE Id BETWEEN 1 AND 4000000

An index hint forces the  query optimizer to use the index specified in the hint to retrieve the data .

Index hints can be nice to use in the short-term for investigating, testing, and debugging.  However, they are almost never the correct long-term solution for fixing query performance.

It’s better to look for the root-cause of a poor performing query: maybe you need to rebuild stats on an index or determine if the cardinality estimator being used is not ideal.  You might also benefit from rewriting a terribly written query.

Any of these options will likely help you create a better, long-term, flexible solutions rather than forcing SQL Server to use the same hard-coded, potentially sub-optimal index forever.


Last modified: December 20, 2019



Write a Reply or Comment