Case study of SQL Server plan guide with SQL and Procedure

A application run a query which sometimes slow on one literal but fast on the other, this is the case study of SQL Server plan guide feature:

Slow query: use 255 as literal, run duration is ~ 1 minute

SELECT top 1 mh.intnum as intnum,rh.remark as remark,reportid FROM msghead mh inner join msgdetail md on md.intnum = mh.msgintnum left outer join rephistory rh on md.functionnum = 45000 AND md.functionref = rh.intnum WHERE readon < '1900/01/02' AND((isnull(rh.userid,0) = 255 AND md.functionnum = 45000 and rh.remark like 'http%'))

Fast query: use 167 or 66 as literal, complete in less than 1 second

SELECT top 1 mh.intnum as intnum,rh.remark as remark,reportid FROM msghead mh inner join msgdetail md on md.intnum = mh.msgintnum left outer join rephistory rh on md.functionnum = 45000 AND md.functionref = rh.intnum WHERE readon < '1900/01/02' AND((isnull(rh.userid,0) = 167 AND md.functionnum = 45000 and rh.remark like 'http%'))

Thanks to this book: outlined the steps to implement

https://books.google.ca/books?id=wK1CAwAAQBAJ&pg=PT1200&lpg=PT1200&dq=sql+server+plan+guide+template+literal+SHOWPLAN_XML&source=bl&ots=iuIR6SzIE5&sig=ACfU3U1bTbUwofgeFoYGFC0TX2CEzeA2CQ&hl=en&sa=X&ved=2ahUKEwi1qvXN0bbzAhX3IjQIHcc_D1wQ6AF6BAgVEAM#v=onepage&q=sql%20server%20plan%20guide%20template%20literal%20SHOWPLAN_XML&f=false

Here are the steps:

DECLARE @my_templatetext nvarchar(max)

DECLARE @my_parameters nvarchar(max)

EXEC sp_get_query_template

N'SELECT top 1 mh.intnum as intnum,rh.remark as remark,reportid FROM msghead mh

inner join msgdetail md on md.intnum = mh.msgintnum

left outer join rephistory rh on md.functionnum = 45000 AND md.functionref = rh.intnum WHERE readon < ''1900/01/02''

AND((isnull(rh.userid,0) = 167 AND md.functionnum = 45000 and rh.remark like ''http%''))',

@my_templatetext OUTPUT,

@my_parameters OUTPUT;

SELECT @my_templatetext;

SELECT @my_parameters;

exec sp_create_plan_guide

@name=N'plan_guide_template_plan_for_select_top_1_blabla',

@stmt = @my_templatetext,

@type = N'TEMPLATE',

@module_or_batch=null,

@params=@my_parameters,

@hints=N'OPTION (PARAMETERIZATION FORCED)';

exec sp_create_plan_guide

@name=N'plan_guide_for_select_top_1_blabla',

@stmt = @my_templatetext,

@type = N'SQL',

@module_or_batch=null,

@params=@my_parameters,

@hints=N'OPTION (OPTIMIZE FOR (@3=167))';

Comparing the plan, the plan guide is being used.

The query is parameterized, and both template and SQL plan guides are applied.

(@0 int,@1 varchar(8000),@2 int,@3 int,@4 int)select top 1 mh . intnum as intnum , rh . remark as remark , reportid from msghead mh inner join msgdetail md on md . intnum = mh . msgintnum left outer join rephistory rh on md . functionnum = @0 and md . functionref = rh . intnum where readon < @1 and ( ( isnull ( rh . userid , @2 ) = @3 and md . functionnum = @4 and rh . remark like 'http%' ) )

But the performance is still bad! so this plan is not helping literal 255, let me try optimize the plan for another literal 66.

EXEC sp_control_plan_guide N'DROP', N'plan_guide_for_select_top_1_blabla'

exec sp_create_plan_guide

@name=N'plan_guide_for_select_top_1_blabla',

@stmt = @my_templatetext,

@type = N'SQL',

@module_or_batch=null,

@params=@my_parameters,

@hints=N'OPTION (OPTIMIZE FOR (@3=66))';

This one works! all literal complete in less than 1 second by using the plan guide.

Tested performance for literal tried : 255, 261,271,167,66, they all completed in 1 second.

SELECT plan_guide_id, msgnum, severity, state, message

FROM sys.plan_guides

CROSS APPLY fn_validate_plan_guide(plan_guide_id);

select * from sys.plan_guides

Now, what about if a query is run in a procedure which use parameter/variable? can we still guide the plan?

Thanks to this link below, let's give it a try:

https://dbtut.com/index.php/2019/05/09/increase-query-performance-in-sql-server-using-sp_create_plan_guide-system-stored-procedure/

The two cases below indicates either all literal or combination of literal and parameter both work:

For the all parameters:

CREATE PROCEDURE dbo.jsun( @userid INT,@readondate date,@remark varchar(99),@num int)

select @remark='http%'

select @num=45000

SELECT top 1 mh.intnum as intnum,rh.remark as remark,reportid

FROM msghead mh inner

join msgdetail md on md.intnum = mh.msgintnum

left outer join rephistory rh on md.functionnum = @num AND md.functionref = rh.intnum

WHERE readon < @readondate AND((isnull(rh.userid,0) = @userid AND md.functionnum = @num and rh.remark like @remark))

EXEC sp_control_plan_guide N'DROP', N'plan_guide_jsun'

exec sp_create_plan_guide

@name=N'plan_guide_jsun',

@stmt = N'SELECT top 1 mh.intnum as intnum,rh.remark as remark,reportid

FROM msghead mh inner

join msgdetail md on md.intnum = mh.msgintnum

left outer join rephistory rh on md.functionnum = @num AND md.functionref = rh.intnum

WHERE readon < @readondate AND((isnull(rh.userid,0) = @userid AND md.functionnum = @num and rh.remark like @remark))',

@type = N'OBJECT',

@module_or_batch=N'dbo.jsun',

@params=NULL,

@hints=N'OPTION (OPTIMIZE FOR (@userid=66))';

for Combination of literal (45000) and parameter (@userid):

drop procedure dbo.jsun

CREATE PROCEDURE dbo.jsun( @userid INT)

SELECT top 1 mh.intnum as intnum,rh.remark as remark,reportid

FROM msghead mh inner join msgdetail md on md.intnum = mh.msgintnum

left outer join rephistory rh on md.functionnum = 45000 AND md.functionref = rh.intnum

WHERE readon < '1900/01/02' AND((isnull(rh.userid,0) = @userid AND md.functionnum = 45000 and rh.remark like 'http%'))

exec sp_create_plan_guide

@name=N'plan_guide_jsun',

@stmt = N'

SELECT top 1 mh.intnum as intnum,rh.remark as remark,reportid

FROM msghead mh inner join msgdetail md on md.intnum = mh.msgintnum

left outer join rephistory rh on md.functionnum = 45000 AND md.functionref = rh.intnum

WHERE readon < ''1900/01/02'' AND((isnull(rh.userid,0) = @userid AND md.functionnum = 45000 and rh.remark like ''http%''))',

@type = N'OBJECT',

@module_or_batch=N'dbo.jsun',

@params=NULL,

@hints=N'OPTION (OPTIMIZE FOR (@userid=66))';

The @stmt has to match the proc sql, for example, if you change 1900 to 1901, then, you will get error like:

Msg 10507, Level 16, State 1, Procedure jsun, Line 1 [Batch Start Line 54]

Cannot create plan guide 'plan_guide_jsun' because the statement specified by @stmt and @module_or_batch, or by @plan_handle and @statement_start_offset, does not match any statement in the specified module or batch. Modify the values to match a statement in the module or batch.

Compare the plan and it's working.

Now, one more question: Can I guide the slow query (literal 255) with a plan generated by faster query(literal 66)?

Yes, I can use option (use plan N '<ShowPlanXML...' )

The XML plan come from the fast query literal, it's like this:

OPTION (

USE PLAN N'

<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.2" Build="11.0.5388.0">

<BatchSequence>

<Batch>

<Statements>

<StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false" />

<QueryPlan CachedPlanSize="72" CompileTime="10" CompileCPU="10" CompileMemory="856">

<MemoryGrantInfo SerialRequiredMemory="1024" SerialDesiredMemory="20992" />

<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="419407" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="2" />

<OutputList>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msghead]" Alias="[mh]" Column="intnum" />

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[rephistory]" Alias="[rh]" Column="reportid" />

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[rephistory]" Alias="[rh]" Column="remark" />

</OutputList>

<Top RowCount="0" IsPercent="0" WithTies="0">

<TopExpression>

<ScalarOperator ScalarString="(1)">

<Const ConstValue="(1)" />

</ScalarOperator>

</TopExpression>

<OutputList>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msghead]" Alias="[mh]" Column="intnum" />

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[rephistory]" Alias="[rh]" Column="reportid" />

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[rephistory]" Alias="[rh]" Column="remark" />

</OutputList>

<NestedLoops Optimized="0" WithUnorderedPrefetch="1">

<OuterReferences>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msghead]" Alias="[mh]" Column="intnum" />

<ColumnReference Column="Expr1012" />

</OuterReferences>

<OutputList>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msghead]" Alias="[mh]" Column="intnum" />

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[rephistory]" Alias="[rh]" Column="reportid" />

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[rephistory]" Alias="[rh]" Column="remark" />

</OutputList>

<NestedLoops Optimized="0" WithUnorderedPrefetch="1">

<OuterReferences>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msgdetail]" Alias="[md]" Column="intnum" />

<ColumnReference Column="Expr1011" />

</OuterReferences>

<OutputList>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msgdetail]" Alias="[md]" Column="intnum" />

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[rephistory]" Alias="[rh]" Column="reportid" />

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[rephistory]" Alias="[rh]" Column="remark" />

</OutputList>

<NestedLoops Optimized="1" WithUnorderedPrefetch="1">

<OuterReferences>

<ColumnReference Column="Bmk1004" />

<ColumnReference Column="Expr1010" />

</OuterReferences>

<OutputList>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msgdetail]" Alias="[md]" Column="intnum" />

<ColumnReference Column="Bmk1004" />

<ColumnReference Column="Expr1009" />

</OutputList>

<ComputeScalar>

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Expr1009" />

<ScalarOperator ScalarString="BmkToPage([Bmk1004])">

<Intrinsic FunctionName="BmkToPage">

<ScalarOperator>

<Identifier>

<ColumnReference Column="Bmk1004" />

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</DefinedValue>

</DefinedValues>

<OutputList>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msgdetail]" Alias="[md]" Column="intnum" />

<ColumnReference Column="Bmk1004" />

</OutputList>

<NestedLoops Optimized="1" WithUnorderedPrefetch="1">

<OuterReferences>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msgdetail]" Alias="[md]" Column="functionref" />

<ColumnReference Column="Expr1008" />

</OuterReferences>

<OutputList>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msgdetail]" Alias="[md]" Column="intnum" />

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msgdetail]" Alias="[md]" Column="functionref" />

</OutputList>

<IndexScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0">

<DefinedValues>

<DefinedValue>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msgdetail]" Alias="[md]" Column="intnum" />

</DefinedValue>

<DefinedValue>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msgdetail]" Alias="[md]" Column="functionref" />

</DefinedValue>

</DefinedValues>

<Object Database="[EatecX]" Schema="[dbo]" Table="[msgdetail]" Index="[PK_msgdetail]" Alias="[md]" IndexKind="Clustered" />

<Predicate>

<ScalarOperator ScalarString="[EatecX].[dbo].[msgdetail].[functionnum] as [md].[functionnum]=[@0] AND [EatecX].[dbo].[msgdetail].[functionnum] as [md].[functionnum]=[@4]">

<Logical Operation="AND">

<ScalarOperator>

<Compare CompareOp="EQ">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msgdetail]" Alias="[md]" Column="functionnum" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Identifier>

<ColumnReference Column="@0" />

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

<ScalarOperator>

<Compare CompareOp="EQ">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msgdetail]" Alias="[md]" Column="functionnum" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Identifier>

<ColumnReference Column="@4" />

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

<OutputList>

<ColumnReference Column="Bmk1004" />

</OutputList>

<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">

<DefinedValues>

<DefinedValue>

<ColumnReference Column="Bmk1004" />

</DefinedValue>

</DefinedValues>

<Object Database="[EatecX]" Schema="[dbo]" Table="[rephistory]" Index="[reph_pk]" Alias="[rh]" IndexKind="NonClustered" />

<SeekPredicates>

<SeekPredicateNew>

<SeekKeys>

<Prefix ScanType="EQ">

<RangeColumns>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[rephistory]" Alias="[rh]" Column="intnum" />

</RangeColumns>

<RangeExpressions>

<ScalarOperator ScalarString="[EatecX].[dbo].[msgdetail].[functionref] as [md].[functionref]">

<Identifier>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msgdetail]" Alias="[md]" Column="functionref" />

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</ComputeScalar>

</RelOp>

<OutputList>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[rephistory]" Alias="[rh]" Column="reportid" />

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[rephistory]" Alias="[rh]" Column="remark" />

</OutputList>

<IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">

<DefinedValues>

<DefinedValue>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[rephistory]" Alias="[rh]" Column="reportid" />

</DefinedValue>

<DefinedValue>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[rephistory]" Alias="[rh]" Column="remark" />

</DefinedValue>

</DefinedValues>

<Object Database="[EatecX]" Schema="[dbo]" Table="[rephistory]" Alias="[rh]" TableReferenceId="-1" IndexKind="Heap" />

<SeekPredicates>

<SeekPredicateNew>

<SeekKeys>

<Prefix ScanType="EQ">

<RangeColumns>

<ColumnReference Column="Bmk1004" />

</RangeColumns>

<RangeExpressions>

<ScalarOperator ScalarString="[Bmk1004]">

<Identifier>

<ColumnReference Column="Bmk1004" />

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

<Predicate>

<ScalarOperator ScalarString="isnull([EatecX].[dbo].[rephistory].[userid] as [rh].[userid],[@2])=[@3] AND [EatecX].[dbo].[rephistory].[remark] as [rh].[remark] like N''http%''">

<Logical Operation="AND">

<ScalarOperator>

<Compare CompareOp="EQ">

<ScalarOperator>

<Intrinsic FunctionName="isnull">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[rephistory]" Alias="[rh]" Column="userid" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Identifier>

<ColumnReference Column="@2" />

</Identifier>

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

<ScalarOperator>

<Identifier>

<ColumnReference Column="@3" />

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

<ScalarOperator>

<Intrinsic FunctionName="like">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[rephistory]" Alias="[rh]" Column="remark" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Const ConstValue="N''http%''" />

</ScalarOperator>

</Intrinsic>

</ScalarOperator>

</Logical>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

<OutputList>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msghead]" Alias="[mh]" Column="intnum" />

</OutputList>

<IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">

<DefinedValues>

<DefinedValue>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msghead]" Alias="[mh]" Column="intnum" />

</DefinedValue>

</DefinedValues>

<Object Database="[EatecX]" Schema="[dbo]" Table="[msghead]" Index="[msghead_indx1]" Alias="[mh]" IndexKind="NonClustered" />

<SeekPredicates>

<SeekPredicateNew>

<SeekKeys>

<Prefix ScanType="EQ">

<RangeColumns>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msghead]" Alias="[mh]" Column="msgintnum" />

</RangeColumns>

<RangeExpressions>

<ScalarOperator ScalarString="[EatecX].[dbo].[msgdetail].[intnum] as [md].[intnum]">

<Identifier>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msgdetail]" Alias="[md]" Column="intnum" />

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

<OutputList />

<IndexScan Lookup="1" Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore">

<DefinedValues />

<Object Database="[EatecX]" Schema="[dbo]" Table="[msghead]" Index="[PK_msghead]" Alias="[mh]" TableReferenceId="-1" IndexKind="Clustered" />

<SeekPredicates>

<SeekPredicateNew>

<SeekKeys>

<Prefix ScanType="EQ">

<RangeColumns>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msghead]" Alias="[mh]" Column="intnum" />

</RangeColumns>

<RangeExpressions>

<ScalarOperator ScalarString="[EatecX].[dbo].[msghead].[intnum] as [mh].[intnum]">

<Identifier>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msghead]" Alias="[mh]" Column="intnum" />

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

<Predicate>

<ScalarOperator ScalarString="[EatecX].[dbo].[msghead].[readon] as [mh].[readon]<CONVERT_IMPLICIT(datetime,[@1],0)">

<Compare CompareOp="LT">

<ScalarOperator>

<Identifier>

<ColumnReference Database="[EatecX]" Schema="[dbo]" Table="[msghead]" Alias="[mh]" Column="readon" />

</Identifier>

</ScalarOperator>

<ScalarOperator>

<Identifier>

<ColumnReference Column="ConstExpr1007">

<ScalarOperator>

<Convert DataType="datetime" Style="0" Implicit="1">

<ScalarOperator>

<Identifier>

<ColumnReference Column="@1" />

</Identifier>

</ScalarOperator>

</Convert>

</ScalarOperator>

</ColumnReference>

</Identifier>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</Top>

</RelOp>

<ParameterList>

<ColumnReference Column="@4" ParameterCompiledValue="(45000)" />

<ColumnReference Column="@3" ParameterCompiledValue="(66)" />

<ColumnReference Column="@2" ParameterCompiledValue="(0)" />

<ColumnReference Column="@1" ParameterCompiledValue="''1900/01/02''" />

<ColumnReference Column="@0" ParameterCompiledValue="(45000)" />

</ParameterList>

</QueryPlan>

</StmtSimple>

</Statements>

</Batch>

</BatchSequence>

</ShowPlanXML>

Remember, if the xml plan has single quote around literal, you need to replace single quote ' to two single quotes '', for example, replace '1900/01/02' to ''1900/01/02''.

I tested run it and compared it to the faster plan, they matches and query complete fast as expected.

In other post, I have case study of query store, which stablize the query plan, as what Oracle SPM does.

https://walkthrough-dba.blogspot.com/2021/10/sql-server-query-store-as-oracle-plan.html

DBA

Search This Blog

Case study of SQL Server plan guide with SQL and Procedure

Comments

Post a Comment

Popular posts from this blog

non-existent process lock port on windows server

Opatch apply/lsinventory error: oneoff is corrupted or does not exist

shell script to clean up oracle dumpfile