One feature better to avoid in SQL Server while tuning the performance

There is a feature called “Index Hints” which is sometime used by developers. Basically if you use this feature, you are asking SQL Server to use the specific index in the execution plan instead of the index chosen by SQL Server.

I often found it as a short term solution where someone created an index and then forced SQL Server to use it for immediate performance improvement. While this can give you some immediate benefits, this comes with following costs:


• If a better index is added in future, SQL server still will not pick it. So basically the code with forced index will be out of scanner of SQL Server for planning a better execution plan.
•If the index which is forced gets dropped, your code will be in deep trouble. Moreover if you really do not remember the code where you forced index hint and try to take a generic approach of performance troubleshooting of SQL Server (like updating statistics etc), you will not be benefited.
• Every time Stored procedure/application needs to be recompiled (assuming the code is in a Stored procedure/Application) for any change to take effect related to performance tuning. So DBA will have a serious limitation to assist developers.
•When SQL Server is upgraded, it may be able to build a better query plan for the same code, but if you use a hint, it will never do so.



So considering the downsides of using “Index Hints”, I recommend not using it. Also as a DBA, if you find this practice in any of the code while troubleshooting the performance, it is better to remove such dependency.


However, if you are still wondering on how to use "index hint" in a TSQL statement, please use below syntax:


SELECT ….

FROM WITH (INDEX ())
WHERE ...