Using Plan Guides and Plan Freezing

Today one of my customers came to me with a question. The question was “We have some 3rd party applications which heavily use ad-hoc queries with Index Force. Because of changing query optimizer behavior these queries do tons of logical reads. We would like to optimize these queries by removing index forces however we don’t have the application code. What can we do to resolve this problem?”

 

The solution of this issue is simple if we had the application code but lacking that we need another solution to optimize these queries without touching the source code.

 

The best approach to do that is to use the “plan freezing” and “plan guides” features which are shipped with SQL Server 2005.

 

Let’s make an example.

 

Assume that, we have an ad-hoc query (see below) and would like to remove the index force statement, however we don’t have the access to change the application code. So we will try to use plan guide and use plan options.

 

select *

from Person.Address WITH (INDEX=PK_Address_AddressID)

where city='Bothell'

 

The query plan of the above query is;

 

 

To be able to use the use plan option, we need a query plan which does not use an index force statement. To be able to get this query plan I run the below query and get the xml format of the query plan. (To be able to reach the xml format of the query plan, right click the query plan and then click “Show Execution Plan XML”)

 

select *

from Person.Address

where city='Bothell'

 

 

Now we need to create a plan guide like below;

 

EXEC sp_create_plan_guide

       @name = N'myPlanGuide',

    @stmt = N'select *

from Person.Address WITH (INDEX=PK_Address_AddressID)

where city=''Bothell''',

    @type = N'SQL',

    @hints= N'OPTION (USE PLAN

N''

<ShowPlanXML xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="https://www.w3.org/2001/XMLSchema" Version="1.2" Build="11.0.2218.0" xmlns="https://schemas.microsoft.com/sqlserver/2004/07/showplan">

  <BatchSequence>

    <Batch>

      <Statements>

        <StmtSimple StatementCompId="1" StatementEstRows="9.33333" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.217108" StatementText="SELECT * FROM [Person].[Address] WHERE [city]=@1" StatementType="SELECT" QueryHash="0x867F29E0E5F954BA" QueryPlanHash="0x741D9C41BD0C9E95" RetrievedFromCache="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="32" CompileTime="1" CompileCPU="1" CompileMemory="208">

            <MissingIndexes>

              <MissingIndexGroup Impact="91.0545">

                <MissingIndex Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]">

           <ColumnGroup Usage="EQUALITY">

                    <Column Name="[City]" ColumnId="4" />

                  </ColumnGroup>

                </MissingIndex>

              </MissingIndexGroup>

            </MissingIndexes>

            <MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0" />

            <OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="208220" EstimatedPagesCached="104110" EstimatedAvailableDegreeOfParallelism="4" />

            <RelOp AvgRowSize="4228" EstimateCPU="3.90133E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="9.33333" LogicalOp="Inner Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.217108">

            <OutputList>

                <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="AddressID" />

                <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="AddressLine1" />

                <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="AddressLine2" />

                <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="City" />

                <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="StateProvinceID" />

                <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="PostalCode" />

   <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="SpatialLocation" />

                <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="rowguid" />

                <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="ModifiedDate" />

              </OutputList>

              <RunTimeInformation>

                <RunTimeCountersPerThread Thread="0" ActualRows="26" ActualEndOfScans="1" ActualExecutions="1" />

              </RunTimeInformation>

              <NestedLoops Optimized="false">

                <OuterReferences>

                  <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="AddressID" />

                </OuterReferences>

                <RelOp AvgRowSize="177" EstimateCPU="0.0217324" EstimateIO="0.158681" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="9.33333" LogicalOp="Index Scan" NodeId="1" Parallel="false" PhysicalOp="Index Scan" EstimatedTotalSubtreeCost="0.180413" TableCardinality="19614">

                  <OutputList>

                    <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="AddressID" />

                    <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="AddressLine1" />

                    <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="AddressLine2" />

                    <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="City" />

                    <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="StateProvinceID" />

                    <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="PostalCode" />

                  </OutputList>

                  <RunTimeInformation>

                    <RunTimeCountersPerThread Thread="0" ActualRows="26" ActualEndOfScans="1" ActualExecutions="1" />

                  </RunTimeInformation>

                  <IndexScan Ordered="false" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">

                    <DefinedValues>

                      <DefinedValue>

                        <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="AddressID" />

                      </DefinedValue>

                      <DefinedValue>

                        <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="AddressLine1" />

                      </DefinedValue>

                      <DefinedValue>

                        <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="AddressLine2" />

                      </DefinedValue>

                      <DefinedValue>

                        <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="City" />

                      </DefinedValue>

                      <DefinedValue>

                        <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="StateProvinceID" />

                      </DefinedValue>

                      <DefinedValue>

                        <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="PostalCode" />

                      </DefinedValue>

                    </DefinedValues>

                    <Object Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Index="[IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]" IndexKind="NonClustered" />

                    <Predicate>

                      <ScalarOperator ScalarString="[AdventureWorks2012].[Person].[Address].[City]=N''''Bothell''''">

                        <Compare CompareOp="EQ">

                          <ScalarOperator>

                            <Identifier>

                              <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="City" />

                            </Identifier>

                          </ScalarOperator>

                          <ScalarOperator>

                            <Const ConstValue="N''''Bothell''''" />

                          </ScalarOperator>

                        </Compare>

                      </ScalarOperator>

                    </Predicate>

                  </IndexScan>

                </RelOp>

                <RelOp AvgRowSize="4059" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="8.33333" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="3" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.0272414" TableCardinality="19614">

                  <OutputList>

                    <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="SpatialLocation" />

                    <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="rowguid" />

                    <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="ModifiedDate" />

                  </OutputList>

                  <RunTimeInformation>

                    <RunTimeCountersPerThread Thread="0" ActualRows="26" ActualEndOfScans="0" ActualExecutions="26" />

                  </RunTimeInformation>

                  <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">

                    <DefinedValues>

                      <DefinedValue>

                        <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="SpatialLocation" />

                      </DefinedValue>

                      <DefinedValue>

                        <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="rowguid" />

                      </DefinedValue>

                      <DefinedValue>

                        <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="ModifiedDate" />

                      </DefinedValue>

                    </DefinedValues>

                    <Object Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Index="[PK_Address_AddressID]" TableReferenceId="-1" IndexKind="Clustered" />

                    <SeekPredicates>

                      <SeekPredicateNew>

                        <SeekKeys>

                          <Prefix ScanType="EQ">

                            <RangeColumns>

                              <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="AddressID" />

                            </RangeColumns>

                            <RangeExpressions>

                              <ScalarOperator ScalarString="[AdventureWorks2012].[Person].[Address].[AddressID]">

                                <Identifier>

                                  <ColumnReference Database="[AdventureWorks2012]" Schema="[Person]" Table="[Address]" Column="AddressID" />

                                </Identifier>

                              </ScalarOperator>

                            </RangeExpressions>

                          </Prefix>

                   </SeekKeys>

                      </SeekPredicateNew>

                    </SeekPredicates>

                  </IndexScan>

                </RelOp>

              </NestedLoops>

            </RelOp>

            <ParameterList>

              <ColumnReference Column="@1" ParameterCompiledValue="''''Bothell''''" ParameterRuntimeValue="''''Bothell''''" />

            </ParameterList>

          </QueryPlan>

        </StmtSimple>

      </Statements>

    </Batch>

  </BatchSequence>

</ShowPlanXML>

'')'

 

 

Run the index force query again and see that query plan of the ad-hoc query is changed and it does not use forced index anymore.

 

 

So ad-hoc query used the plan guide and forced plan. I can review it on SQL Server Profiler with “Plan Guide Successful” event.

 

 

When I run the query, this event is fired means that plan guide is used by the query.

 

 

We have also “Plan Guide Unsuccessful” event. If there is a problem with the “forced plan” we might see a “Plan Guide Unsuccessful” event which indicates the plan guide cannot be used.

 

For an example; let’s drop the index which is used in the forced plan and run the ad-hoc query again

 

drop index [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]

       on Person.Address

 

select *

from Person.Address WITH (INDEX=PK_Address_AddressID)

where city='Bothell'

 

 

This time the plan guide is not used and “Plan Guide Unsuccessful” event is fired

 

 

To determine why the plan guide is not used, we can use sys.fn_validate_plan_guide function.

 

declare @planguideid int=0

 

SELECT @planguideid = plan_guide_id FROM sys.plan_guides

WHERE name = N'myPlanGuide';

 

SELECT * FROM sys.fn_validate_plan_guide(@planguideid)

 

 

When I run the above query, the message is quite clear J

Index 'AdventureWorks2012.Person.Address.IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode', specified in the USE PLAN hint, does not exist. Specify an existing index, or create an index with the specified name.

 

That’s all. I hope that you find this blog post useful J