Database Reference
In-Depth Information
Figure 17-22. The plan guide forces a new execution plan on the same query
One other option exists when you have a plan in the cache that you think performs the way you want. You can
capture that plan into a plan guide to ensure that the next time the query is run, the same plan is executed. You
accomplish this by running sp_create_plan_ guide_from_handle .
To test it, first clear the procedure cache so you can control exactly which query plan is used.
DBCC FREEPROCCACHE();
With the procedure cache clear and the existing plan guide, MyGoodSOQLGuide , in place, rerun the query. It will
use the plan guide to arrive at the execution plan displayed in Figure 17-20 . To see whether this plan can be kept, first
drop the plan guide that is forcing the Index Seek operation.
EXECUTE sp_control_plan_guide
@operation = 'Drop',
@name = N'MyGoodSQLGuide' ;
If you were to rerun the query now, it would revert to its original plan. However, right now in the plan cache, you
have the plan displayed in Figure 17-20 . To keep it, run the following script:
DECLARE @plan_handle VARBINARY(64),
@start_offset INT ;
SELECT @plan_handle = deqs.plan_handle,
@start_offset = deqs.statement_start_offset
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(sql_handle)
CROSS APPLY sys.dm_exec_text_query_plan(deqs.plan_handle,
deqs.statement_start_offset,
deqs.statement_end_offset) AS qp
WHERE text LIKE N'SELECT soh.SalesOrderNumber%'
EXECUTE sp_create_plan_guide_from_handle
@name = N'ForcedPlanGuide',
@plan_handle = @plan_handle,
@statement_start_offset = @start_offset ;
GO
 
Search WWH ::




Custom Search