A developer made a small change to a sql statement  - and assumed that this would not have a significant impact on performance . The change  caused the optimizer to use a Clustered Index Scan – with over 10 million records – as opposed to a NonClustered Index Seek, which was using an efficient index. The change was compounded  by an involved UPDATE statement.

 As a rapid tuning exercise  – I tried – full STATISTICS update , that didn’t work – I then tried ALTER NDEX…  , on the indexes . Note , that this was on a Production server – with pressure from users to extract the relevant data .

 All of these measures still didn’t allow the index to be chosen. As this is a third party managed database system – I needed to be careful  , as there are support agreements in  place , but on the other hand the users needed the data set .

 The quick workaround was to introduce a TABLE HINT. Generally the optimizer chooses the correct index – but on occasions measures need to be taken which allow the correct results. As described this was a Production situation- so I just needed to get the data available – and go back and complete some index analysis.

 The TABLE HINT directs the optimizer towards a specific scan,index or lock. I added the HINT –  and the performace pressure was alleviated. 

 The syntax for a TABLE HINT is :

 SELECT col1,col2… FROM mytable WITH (INDEX (myindex) [, ...])

 Using a table hint is a last measure , and shouldn’t replace an index analysis process

Source:Jack Vamvas (http://www.sqlserver-dba.com)