We have a table with 20 million+ rows. It has/had 1 clustered and 5 non-clustered indexes.
Today morning users complained about slowness in the queries and this is what I found:
1. sp_help & sp_helpindex shows 5 non clustered indexes
2. select * from sysindexes for that tables shows 1 clustered and 5 non clustered indexes.
3. No query is using the clustered index, which they are supposed to. All of them are going for table scan.
You cannot force the query optimiser to forcefully use an index , so I guess there is a problem with the indexes being not updates.
Try to update the statistics for the index or consider dropping and recreatin the index.
Take out the output of sp_showplan and find out whether optimizer is using the index or not. If it is not using try to update the statistics for the index or consider dropping and recreating the index.
If still the problem persist and u want the index to be forcefully used, do the following: