SQL Server Performance

How to make sure that the plan guide is applied?

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by levin, Feb 8, 2007.

  1. levin New Member

    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?
  2. alzdba Member

    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"
  3. alzdba Member

    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))'
    ;
  4. levin New Member

    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
  5. levin New Member

    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.
  6. levin New Member

    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'.
  7. alzdba Member

    I think you can remove the oparameterization froced parameter because , as you have stated, your db already has parameterization forced on.
  8. levin New Member

    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
  9. alzdba Member

    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''))'
  10. levin New Member

    <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
  11. levin New Member

    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>
    .................
  12. alzdba Member

    thanks for the feedback.
    Glad it finaly worked.

Share This Page