Recently I was able to harness the power of Plan Guides in a critical SQL Server environment that needed some urgent attention.
A particular query was being fired thousands of times every minute on the database hosted on SQL Server 2016 Enterprise Edition running 128 cores and a couple of terabytes RAM.
Every now and then, the database server would reel under CPU pressure and after some time the CPU utilization would drop back to normal utilization.
During the investigation it was observed that this particular query, when would get recompiled with a certain parameter value would produce an execution plan that would take around 300 milliseconds of CPU while usually with the other plan it would get executed within 15 milliseconds.
A query that executes so frequently, and suddenly starts consuming almost 20 times CPU, is certainly going to hit the server hard. And that’s exactly what was happening here.
While investigating this issue, I had saved both the execution plans and I was now looking forward to putting a fix in place to alleviate the issue.
This was an ORM generated query and changing it would take at least a few sprints.
The query store had been disabled earlier due to performance issues encountered in the past.
I explored the options and then recalled that a plan guide might help and it did!
In the scripts below I have used the AdventureWorks database to demonstrate the steps that I took to put the plan guide in action.
/*
--------------
Target Query
--------------
*/
exec sp_executesql N'SELECT e.businessentityid,
p.title,
p.firstname,
p.middlename,
p.lastname,
e.jobtitle,
pp.phonenumber
FROM HumanResources.Employee e
INNER JOIN Person.Person p
on p.BusinessEntityID = e.BusinessEntityID
INNER JOIN person.PersonPhone pp
ON p.BusinessEntityID = pp.BusinessEntityID'
/*
-----------------------
Create Plan Guide
-----------------------
To create the plan guide, copy the execution plan xml for the good plan and paste that in the @xml_showplan variable below.
*/
DECLARE @xml_showplan nvarchar(max);
DECLARE @sql nvarchar(max);
SET @xml_showplan = '<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.539" Build="15.0.2095.3" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="2" StatementEstRows="269.695" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" CardinalityEstimationModelVersion="150" StatementSubTreeCost="0.265423" StatementText="SELECT e.businessentityid, 
 p.title,
 p.firstname,
 p.middlename,
 p.lastname,
 e.jobtitle,
 pp.phonenumber
FROM HumanResources.Employee e
 INNER JOIN Person.Person p
 on p.BusinessEntityID = e.BusinessEntityID
 INNER JOIN person.PersonPhone pp 
 ON p.BusinessEntityID = pp.BusinessEntityID" StatementType="SELECT" QueryHash="0x287A7F26FA4BABF7" QueryPlanHash="0xA4D13284A2EA1A49" 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="48" CompileTime="139" CompileCPU="137" CompileMemory="424">
<MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" GrantedMemory="0" MaxUsedMemory="0" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="26214" EstimatedPagesCached="19660" EstimatedAvailableDegreeOfParallelism="6" MaxCompileMemory="2149296" />
<OptimizerStatsUsage>
<StatisticsInfo Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[PersonPhone]" Statistics="[IX_PersonPhone_PhoneNumber]" ModificationCount="0" SamplingPercent="100" LastUpdate="2009-08-15T11:42:44.41" />
<StatisticsInfo Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[PersonPhone]" Statistics="[PK_PersonPhone_BusinessEntityID_PhoneNumber_PhoneNumberTypeID]" ModificationCount="0" SamplingPercent="100" LastUpdate="2009-08-15T11:42:37.81" />
<StatisticsInfo Database="[AdventureWorks2008Test]" Schema="[HumanResources]" Table="[Employee]" Statistics="[PK_Employee_BusinessEntityID]" ModificationCount="0" SamplingPercent="100" LastUpdate="2009-08-15T11:42:36.24" />
<StatisticsInfo Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Statistics="[PK_Person_BusinessEntityID]" ModificationCount="0" SamplingPercent="100" LastUpdate="2009-08-15T11:42:37.65" />
</OptimizerStatsUsage>
<WaitStats>
<Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="194" WaitCount="2" />
</WaitStats>
<QueryTimeStats CpuTime="1" ElapsedTime="196" />
<RelOp AvgRowSize="258" EstimateCPU="0.00112733" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="269.695" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.265423">
<OutputList>
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="BusinessEntityID" />
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="JobTitle" />
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="Title" />
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="FirstName" />
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="MiddleName" />
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="LastName" />
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[PersonPhone]" Alias="[pp]" Column="PhoneNumber" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="290" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="1" ActualCPUms="1" />
</RunTimeInformation>
<NestedLoops Optimized="false" WithUnorderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="BusinessEntityID" />
<ColumnReference Column="Expr1007" />
</OuterReferences>
<RelOp AvgRowSize="235" EstimateCPU="0.0012122" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="269.695" LogicalOp="Inner Join" NodeId="2" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.215208">
<OutputList>
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="BusinessEntityID" />
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="JobTitle" />
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="BusinessEntityID" />
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="Title" />
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="FirstName" />
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="MiddleName" />
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="LastName" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="290" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" />
</RunTimeInformation>
<NestedLoops Optimized="false" WithUnorderedPrefetch="true">
<OuterReferences>
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="BusinessEntityID" />
<ColumnReference Column="Expr1006" />
</OuterReferences>
<RelOp AvgRowSize="65" EstimateCPU="0.000476" EstimateIO="0.00756944" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="290" EstimatedRowsRead="290" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.00804544" TableCardinality="290">
<OutputList>
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="BusinessEntityID" />
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="JobTitle" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="290" ActualRowsRead="290" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="9" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="BusinessEntityID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="JobTitle" />
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks2008Test]" Schema="[HumanResources]" Table="[Employee]" Index="[PK_Employee_BusinessEntityID]" Alias="[e]" IndexKind="Clustered" Storage="RowStore" />
</IndexScan>
</RelOp>
<RelOp AvgRowSize="179" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="289" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="5" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.20595" TableCardinality="19972">
<OutputList>
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="BusinessEntityID" />
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="Title" />
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="FirstName" />
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="MiddleName" />
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="LastName" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="290" ActualRowsRead="290" Batches="0" ActualEndOfScans="0" ActualExecutions="290" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="0" ActualLogicalReads="816" 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="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="BusinessEntityID" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="Title" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="FirstName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="MiddleName" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="LastName" />
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Index="[PK_Person_BusinessEntityID]" Alias="[p]" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="BusinessEntityID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[AdventureWorks2008Test].[HumanResources].[Employee].[BusinessEntityID] as [e].[BusinessEntityID]">
<Identifier>
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[HumanResources]" Table="[Employee]" Alias="[e]" Column="BusinessEntityID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
<RelOp AvgRowSize="36" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="268.695" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" EstimatedRowsRead="1" LogicalOp="Clustered Index Seek" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0490877" TableCardinality="19972">
<OutputList>
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[PersonPhone]" Alias="[pp]" Column="PhoneNumber" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="290" ActualRowsRead="290" Batches="0" ActualEndOfScans="290" ActualExecutions="290" ActualExecutionMode="Row" ActualElapsedms="0" ActualCPUms="0" ActualScans="290" ActualLogicalReads="582" 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="[AdventureWorks2008Test]" Schema="[Person]" Table="[PersonPhone]" Alias="[pp]" Column="PhoneNumber" />
</DefinedValue>
</DefinedValues>
<Object Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[PersonPhone]" Index="[PK_PersonPhone_BusinessEntityID_PhoneNumber_PhoneNumberTypeID]" Alias="[pp]" IndexKind="Clustered" Storage="RowStore" />
<SeekPredicates>
<SeekPredicateNew>
<SeekKeys>
<Prefix ScanType="EQ">
<RangeColumns>
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[PersonPhone]" Alias="[pp]" Column="BusinessEntityID" />
</RangeColumns>
<RangeExpressions>
<ScalarOperator ScalarString="[AdventureWorks2008Test].[Person].[Person].[BusinessEntityID] as [p].[BusinessEntityID]">
<Identifier>
<ColumnReference Database="[AdventureWorks2008Test]" Schema="[Person]" Table="[Person]" Alias="[p]" Column="BusinessEntityID" />
</Identifier>
</ScalarOperator>
</RangeExpressions>
</Prefix>
</SeekKeys>
</SeekPredicateNew>
</SeekPredicates>
</IndexScan>
</RelOp>
</NestedLoops>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>'
SET @sql = 'SELECT e.businessentityid,
p.title,
p.firstname,
p.middlename,
p.lastname,
e.jobtitle,
pp.phonenumber
FROM HumanResources.Employee e
INNER JOIN Person.Person p
on p.BusinessEntityID = e.BusinessEntityID
INNER JOIN person.PersonPhone pp
ON p.BusinessEntityID = pp.BusinessEntityID'
EXEC sp_create_plan_guide
@name = N'PlanGuide_Query1',
@stmt = @sql,
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = @xml_showplan;
GO
To verify if the plan guide is being used, check the “PlanGuideName” field in the raw xml for the execution plan or have a look at the properties window for the left-most operator in the execution plan.
