DaveWentzel.com            All Things Data

Plan Guides


You need to tweak the query plan to execute how you see fit.  Then generate the XML query plan.  You can use Profiler, SET STATISTICS XML ON, use a DMV query (below), etc.
SELECT           query_plan

FROM sys.dm_exec_cached_plans a

      CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) AS b

WHERE objtype = 'prepared'
sp_create_plan_guide:  creates the plan guide
sp_control_plan_guide:  enables and disables the plan guide
Good when you need to use OPTIMIZE FOR or MAXDOP clauses.