How to make sure that the plan guide is applied? | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

How to make sure that the plan guide is applied?

1.create a plan guide sp_create_plan_guide
@name = N’AppendixPlanGuide’,
@stmt = N’SELECT p.Name AS ProductName, v.Name AS VendorName,
p.ProductLine
FROM Production.Product p, Purchasing.ProductVendor pv,
Purchasing.Vendor v
WHERE p.ProductID = pv.ProductID
AND pv.VendorID = v.VendorID
AND p.ProductLine = @P1
ORDER BY p.Name, v.Name’,
@type = N’SQL’,
@module_or_batch = NULL,
@params= N’@P1 nchar(2)’,
@hint = N’OPTION (OPTIMIZE FOR(@P1 = N”M”))’ 2. execute the following sql with value ‘S’ in the query editor and I followed the execution plan from SQL Server Profiler. SELECT p.Name AS ProductName, v.Name AS VendorName,
p.ProductLine
FROM Production.Product p, Purchasing.ProductVendor pv,
Purchasing.Vendor v
WHERE p.ProductID = pv.ProductID
AND pv.VendorID = v.VendorID
AND p.ProductLine = N’S’
ORDER BY p.Name,v.Name 3.execute the following sql with value ‘M’ in the query editor SELECT p.Name AS ProductName, v.Name AS VendorName,
p.ProductLine
FROM Production.Product p, Purchasing.ProductVendor pv,
Purchasing.Vendor v
WHERE p.ProductID = pv.ProductID
AND pv.VendorID = v.VendorID
AND p.ProductLine = N’M’
ORDER BY p.Name,v.Name 4.I try to flush the plan cache
dbcc freesystemcache(‘all’) 5.execute the sql with value ‘T’
SELECT p.Name AS ProductName, v.Name AS VendorName,
p.ProductLine
FROM Production.Product p, Purchasing.ProductVendor pv,
Purchasing.Vendor v
WHERE p.ProductID = pv.ProductID
AND pv.VendorID = v.VendorID
AND p.ProductLine = N’T’
ORDER BY p.Name,v.Name 6.execute the sql with value ‘M’ again
SELECT p.Name AS ProductName, v.Name AS VendorName,
p.ProductLine
FROM Production.Product p, Purchasing.ProductVendor pv,
Purchasing.Vendor v
WHERE p.ProductID = pv.ProductID
AND pv.VendorID = v.VendorID
AND p.ProductLine = N’M’
ORDER BY p.Name,v.Name [?]Questions:
1.why the execution plan still use the cached plan and it can not use the plan specified by the plan guide?
2.How to mark sure that the specified execution plan(the value is ‘M’) is applied?

1) IMO you are not using parameterized queries
2) I think to optimize your meanth queries, you should use template planguides (with changes to the PARAMETERIZATION database option)
check BOL "Specifying Query Parameterization Behavior by Using Plan Guides"
I guess in your case that would result in …
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
N’SELECT p.Name AS ProductName, v.Name AS VendorName,
p.ProductLine
FROM Production.Product p, Purchasing.ProductVendor pv,
Purchasing.Vendor v
WHERE p.ProductID = pv.ProductID
AND pv.VendorID = v.VendorID
AND p.ProductLine = N”S”
ORDER BY p.Name,v.Name
‘,
@stmt OUTPUT,
@params OUTPUT; –Select @stmt, @params
EXEC sp_create_plan_guide
N’TemplateGuide1′,
@stmt,
N’TEMPLATE’,
NULL,
@params,
@hint = N’OPTION (OPTIMIZE FOR(@P1 = N”M”, PARAMETERIZATION FORCED))’
;

quote:Originally posted by alzdba 1) IMO you are not using parameterized queries
2) I think to optimize your meanth queries, you should use template planguides (with changes to the PARAMETERIZATION database option)
check BOL "Specifying Query Parameterization Behavior by Using Plan Guides"

My the PARAMETERIZATION database option is Forced and I just want to use OPTIMIZE FOR query hint. I think template planguide just parameterize the specified query and it can not optimize for the specified value, so it can not resolve this problem. thanks
quote:Originally posted by alzdba I guess in your case that would result in …
DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
N’SELECT p.Name AS ProductName, v.Name AS VendorName,
p.ProductLine
FROM Production.Product p, Purchasing.ProductVendor pv,
Purchasing.Vendor v
WHERE p.ProductID = pv.ProductID
AND pv.VendorID = v.VendorID
AND p.ProductLine = N”S”
ORDER BY p.Name,v.Name
‘,
@stmt OUTPUT,
@params OUTPUT; –Select @stmt, @params
EXEC sp_create_plan_guide
N’TemplateGuide1′,
@stmt,
N’TEMPLATE’,
NULL,
@params,
@hint = N’OPTION (OPTIMIZE FOR(@P1 = N”M”, PARAMETERIZATION FORCED))’
;

Hi alzdba, Thanks for your help. I have tried it before but I always get the following error message:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ‘PARAMETERIZATION’.
Msg 10504, Level 16, State 1, Procedure sp_create_plan_guide, Line 1
Cannot create plan guide ‘TemplateGuide111′ because parameter @hints is incorrect. Use N’OPTION ( <query_hint> [ ,…n ] )’. It seems to me that it can not support both these two query hint at the same time.
I try to use the following script to create it and I get the message below. DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
N’SELECT p.Name AS ProductName, v.Name AS VendorName,
p.ProductLine
FROM Production.Product p, Purchasing.ProductVendor pv,
Purchasing.Vendor v
WHERE p.ProductID = pv.ProductID
AND pv.VendorID = v.VendorID
AND p.ProductLine = N”S”
ORDER BY p.Name,v.Name
‘,
@stmt OUTPUT,
@params OUTPUT; –Select @stmt, @params
EXEC sp_create_plan_guide
N’TemplateGuide1′,
@stmt,
N’TEMPLATE’,
NULL,
@params,
@hint = N’OPTION (PARAMETERIZATION FORCED,OPTIMIZE FOR(@0 = N”M”))’
error message: Msg 10522, Level 16, State 1, Procedure sp_create_plan_guide, Line 1
Cannot create plan guide ‘TemplateGuide111’ because @hints has illegal value. @hints must be OPTION(PARAMETERIZATION FORCED) or OPTION(PARAMETERIZATION SIMPLE) if @type is ‘template’.
I think you can remove the oparameterization froced parameter because , as you have stated, your db already has parameterization forced on.
quote:Originally posted by alzdba I think you can remove the oparameterization froced parameter because , as you have stated, your db already has parameterization forced on.

yes, but it is not applied and still use the first cached plan. thanks
So the TemplateGuide (like below) didn’t work … [B)] IMO your first planguide wouldn’t work because it does not match the queries in an exact way (you provide a parameterized query), but using a template it should.
What if you compare the plan it selected to the plan you templated [?]
Is it better or worse ? DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
N’SELECT p.Name AS ProductName, v.Name AS VendorName,
p.ProductLine
FROM Production.Product p, Purchasing.ProductVendor pv,
Purchasing.Vendor v
WHERE p.ProductID = pv.ProductID
AND pv.VendorID = v.VendorID
AND p.ProductLine = N”S”
ORDER BY p.Name,v.Name
‘,
@stmt OUTPUT,
@params OUTPUT; –Select @stmt, @params
EXEC sp_create_plan_guide
N’TemplateGuide1′,
@stmt,
N’TEMPLATE’,
NULL,
@params,
@hint = N’OPTION (OPTIMIZE FOR(@0 = N”M”))’

<blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by alzdba</i><br /><br />So the TemplateGuide (like below) didn’t work … [B)]<br /><br />IMO your first planguide wouldn’t work because it does not match the queries in an exact way (you provide a parameterized query), but using a template it should.<br />What if you compare the plan it selected to the plan you templated [?]<br />Is it better or worse ?<br /><br /><i>DECLARE @stmt nvarchar(max);<br />DECLARE @params nvarchar(max);<br />EXEC sp_get_query_template <br />N’SELECT p.Name AS ProductName, v.Name AS VendorName, <br />p.ProductLine <br />FROM Production.Product p, Purchasing.ProductVendor pv, <br />Purchasing.Vendor v <br />WHERE p.ProductID = pv.ProductID <br />AND pv.VendorID = v.VendorID <br />AND p.ProductLine = N”S”<br />ORDER BY p.Name,v.Name<br />’,<br />@stmt OUTPUT, <br />@params OUTPUT;<br /><br />–Select @stmt, @params<br />EXEC sp_create_plan_guide <br />N’TemplateGuide1′, <br />@stmt, <br />N’TEMPLATE’, <br />NULL, <br />@params, <br /><b>@hint = N’OPTION (DECLARE @stmt nvarchar(max);<br />DECLARE @params nvarchar(max);<br />EXEC sp_get_query_template <br />N’SELECT p.Name AS ProductName, v.Name AS VendorName, <br />p.ProductLine <br />FROM Production.Product p, Purchasing.ProductVendor pv, <br />Purchasing.Vendor v <br />WHERE p.ProductID = pv.ProductID <br />AND pv.VendorID = v.VendorID <br />AND p.ProductLine = N”S”<br />ORDER BY p.Name,v.Name<br />’,<br />@stmt OUTPUT, <br />@params OUTPUT;<br /><br />–Select @stmt, @params<br />EXEC sp_create_plan_guide <br />N’TemplateGuide1′, <br />@stmt, <br />N’TEMPLATE’, <br />NULL, <br />@params, <br />@hint = N’OPTION (OPTIMIZE FOR(@0 = N”M”))'(@0 = N”M”))'</b></i><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />According to my testing, it seems that MS can’t support both OPTIMIZE FOR and PARAMETERIZATION FORCED query hint,[<img src=’/community/emoticons/emotion-1.gif’ alt=’:)‘ />]maybe it will be supported in next release. so I was using SQL type with OPTIMIZE FOR query hint and hope that the database option can parameterize it.<br /><br />thanks
Hi alzdba, I get the solution and can see the plan guide is applied. Thank you for your help! DECLARE @stmt nvarchar(max);
DECLARE @params nvarchar(max);
EXEC sp_get_query_template
N’SELECT p.Name AS ProductName, v.Name AS VendorName,
p.ProductLine
FROM Production.Product p, Purchasing.ProductVendor pv,
Purchasing.Vendor v
WHERE p.ProductID = pv.ProductID
AND pv.VendorID = v.VendorID
AND p.ProductLine = N”S”
ORDER BY p.Name,v.Name
‘,
@stmt OUTPUT,
@params OUTPUT; –Select @stmt, @params
EXEC sp_create_plan_guide
N’OptimizeForSQLGuide1′,
@stmt,
N’SQL’,
NULL,
@params,
@hint = N’OPTION (OPTIMIZE FOR(@0 = N”M”))’ Xml plan:
<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.0" Build="9.00.1399.06">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementText="(@0 nvarchar(4000))select p . Name as ProductName , v . Name as VendorName , p . ProductLine from Production . Product p , Purchasing . ProductVendor pv , Purchasing . Vendor v where p . ProductID = pv . ProductID and pv . VendorID = v . VendorID and p . ProductLine = @0 order by p . Name , v . Name" StatementId="1" StatementCompId="1" StatementType="SELECT" PlanGuideDB="AdventureWorks" PlanGuideName="OptimizeForSQLGuide1" StatementSubTreeCost="0.0826491" StatementEstRows="121.521" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound">
<QueryPlan CachedPlanSize="48">
<RelOp NodeId="0" PhysicalOp="Sort" LogicalOp="Sort" EstimateRows="121.521" EstimateIO="0.0112613" EstimateCPU="0.00141292" AvgRowSize="117" EstimatedTotalSubtreeCost="0.0826491" Parallel="0" EstimateRebinds="0" EstimateRewinds="0">
<OutputList>
……………..
thanks for the feedback.
Glad it finaly worked.
]]>