Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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],
                   INDEX(IDX_NC_t_call_ThrowtimeProblemCodes)))'
GO
share|improve this question
    
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 –  Allan S. Hansen May 6 at 6:01
2  
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. –  Aaron Bertrand May 6 at 11:42
    
@AaronBertrand Thanks a lot. Yes I suggested that change too and they will make it in next release. –  yogirk May 6 at 12:31

2 Answers 2

up vote 6 down vote accepted

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.

share|improve this answer
1  
Thanks, @Rob - that helped. –  yogirk May 9 at 7:16

READ YOUR LINKED ARTICLE AGAIN

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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