Sql-server – Why is the plan guide not being used

execution-planperformanceplan-guidesquery-performancesql server

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

Best Answer

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.

Related Question