I'm try to get force Parameterization on a simple adhoc SQL query.As explained in this article https://www.simple-talk.com/sql/performance/fixing-cache-bloat-problems-with-guide-plans-and-forced-parameterization/
But even trying to do this with the simplest query I cant get it to work
CREATE TABLE fruit
(
id BIGINT PRIMARY KEY(id)
,title VARCHAR(150)
)
INSERT INTO fruit VALUES ( 1, 'Apple') , ( 2, 'Banana'), ( 3, 'Orange'), ( 4, 'Pear')
DECLARE @params nvarchar(max);
DECLARE @stmt nvarchar(max);
EXEC sp_get_query_template N'SELECT title FROM fruit WHERE id = 4',@stmt OUTPUT, @params OUTPUT;
--SELECT @params
EXEC sp_create_plan_guide
N'fruitGuide',
@stmt,
N'TEMPLATE',
NULL,
@params,
N'OPTION(PARAMETERIZATION FORCED)';
GO
SELECT title FROM fruit WHERE id = 1
Plan XML:
Shows a compile and doesn't use the plan guide, is there something i'm missing here? Where am i going wrong?
<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.5" Build="13.0.1601.5" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="TRIVIAL" CardinalityEstimationModelVersion="130" StatementSubTreeCost="0.0032831" StatementText="select title from fruit where id = @0" StatementType="SELECT" QueryHash="0xC02C3539A6CB7253" QueryPlanHash="0x257948D9E7B502E7" RetrievedFromCache="true" SecurityPolicyApplied="false">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="1" CachedPlanSize="16" CompileTime="1" CompileCPU="1" CompileMemory="104">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="256000" EstimatedPagesCached="64000" EstimatedAvailableDegreeOfParallelism="2" />
<RelOp AvgRowSize="86" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="0" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="4">
<OutputList>
<ColumnReference Database="[DV]" Schema="[dbo]" Table="[fruit]" Column="title" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1" ActualRowsRead="1" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="2" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[DV]" Schema="[dbo]" Table="[fruit]" Column="title" />
</DefinedValue>
</DefinedValues>
<Object Database="[DV]" Schema="[dbo]" Table="[fruit]" Index="[PK__fruit__3213E83F1B7D3288]" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[DV]" Schema="[dbo]" Table="[fruit]" Column="id" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="CONVERT_IMPLICIT(bigint,[@0],0)">
<Convert DataType="bigint" Style="0" Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@0" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
<ParameterList>
<ColumnReference Column="@0" ParameterCompiledValue="(1)" ParameterRuntimeValue="(1)" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>