We recently ran into the tipping point issue and some of our report queries that used to complete execution within a couple of seconds are now taking more than 2 min because the query optimizer simply ignores the non clustered index on the search column. An example query below:

select top 100 *
from   [dbo].[t_Call]
where  ID > 0 
  and  throwtime between '3/20/2014 7:00:00 AM' and '3/24/2014 6:59:59 AM'
order by id

The ID column is clustered Index and Throwtime has nonclustered index. In this case, we noticed that ordering by throwtime instead of ID changes the query plan and nonclustered index is used.We are also planning to archive some of the old data (it currently has 20 mln rows!!). But making these changes in application is going to take some time and I need to find a way to make reports run reasonably fast, without making changes at the application level (oh well, such is life!).

Enter plan guide. I created the below plan guide with a nonclustered index query hint and for some reason, the nonclustered index is still not used. Am I missing something?

EXEC sp_create_plan_guide 
@name = N'[prod2reports_callthrowtime]', 
@stmt = N'select top 100 *
          from   [dbo] . [t_Call]
          where  ID > @0 and @1 < = ThrowTime and ThrowTime < = @2 order by ID',
@type = N'SQL', 
@module_or_batch = N'select top 100 *
                     from   [dbo] . [t_Call]
                     where  ID > @0 and @1 < = ThrowTime and ThrowTime < = @2 order by ID', 
@params = N'@0 int, @1 datetime, @2 datetime', 
@hints = N'OPTION (TABLE HINT( [dbo] . [t_Call],
  • How does the non clustered index look? The reason the server is ignoring it is because it doesn't think it'll do any good. Either you have a faulty plan or a faulty index May 6, 2014 at 6:01
  • 3
    As an aside, PLEASE PLEASE PLEASE change throwtime between '3/20/2014 7:00:00 AM' and '3/24/2014 6:59:59 AM' to throwtime >= '20140320 07:00' AND throwtime < '20140324 07:00'; - please see this blog post and also this one. May 6, 2014 at 11:42
  • @AaronBertrand Thanks a lot. Yes I suggested that change too and they will make it in next release.
    – RK Kuppala
    May 6, 2014 at 12:31

2 Answers 2


The query must match EXACTLY, including whitespace. I suggest you get the query from the cache, and create it from that rather than entering it any other way.



Writing a plan guide to force the use of your index is almost certainly not what you want, as it will force inefficient bookmark lookups. According to your article, the correct solution for most efficient performance of this query would be to alter your index to be a covering non-clustered index, in other words, either add all of the columns from your table to the index, or (preferably) just add the columns that you need for this query, and then alter the select to just pull those columns.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.