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
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