INDEX HINT is used

SQL Prompt will raise this performance warning if it detects use of the INDEX() table hint. The SQL Server query optimizer usually selects the best index to use, so this hint should be used only as a last resort, by experienced developers and database administrators.

An INDEX() table hint, often referred to an index hint, forces the optimizer to choose a particular index, or set of indexes, when accessing the hinted table. It still allows the optimizer to choose between scanning or seeking those indexes. We can force the optimizer to use a single index, using WITH (INDEX (MyIndex)), or to use all indexes listed with the hint, in listed order, using WITH (INDEX (IndexName1,IndexName2,...)).

Only use this hint if you are certain of the consequences for performance and scalability. You should regularly review and test all hints in response to changes in the data distribution, on upgrade to a new SQL Server version, or application of a new service pack.

For more information on the dangers of using hints, see SQL Prompt Code Analysis: A Hint is Used (PE004-7).

Available in

 SQL Monitor

SQL Prompt

 SQL Code Guard