Sql Server 2005 Plan Guide

Sql Server 2005 Plan Guide

Here is how i managed to add plan guide to an sql executed by sp_executesql statement.
My sql statement is similar to this::

exec sp_executesql N'SELECT ... FROM ...WHERE col1 = @var1 AND col2 = @var2 ...',N'@var1 uniqueidentifier,@var2 int',@var1='...',@var2=...

I extracted xml of the correct execution plan and created following plan guide for this sql statement (setting static execution plan)

EXEC sp_create_plan_guide @name = N'[myplan-20100817-171848]', @stmt = N'SELECT ... FROM ...WHERE col1 = @var1 AND col2 = @var2 ...', @type = N'SQL', @module_or_batch = NULL, @params = N'@var1 uniqueidentifier,@var2 int', @hints = N'OPTION (USE PLAN ''<ShowPlanXML ... > ... </ShowPlanXML>'')'

In order to make sure that plan guide is working fine, extract actual execution plan of the query and look for following lines:

...
<StmtSimple StatementText="SELECT ... FROM ...WHERE col1 = @var1 AND col2 = @var2 ..." StatementId="1" StatementCompId="2" StatementType="SELECT" PlanGuideDB="mydb" PlanGuideName="myplan-20100817-171848" StatementSubTreeCost="0.00328312" StatementEstRows="1" StatementOptmLevel="FULL">
...
<QueryPlan DegreeOfParallelism="0" CachedPlanSize="20" CompileTime="3" CompileCPU="3" CompileMemory="376" UsePlan="1">
...