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