Using Plan Guides and Plan Freezing

Artikel
02/28/2013

Today one of my customers came to me with a question. The question was “We have some 3^rd 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''

<Batch>

</ColumnGroup>

</MissingIndex>

</MissingIndexGroup>

</MissingIndexes>

</OutputList>

</RunTimeInformation>

</OuterReferences>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</Identifier>

</ScalarOperator>

</ScalarOperator>

</Compare>

</ScalarOperator>

</Predicate>

</IndexScan>

</RelOp>

</OutputList>

</RunTimeInformation>

</DefinedValue>

</DefinedValue>

</DefinedValue>

</DefinedValues>

</RangeColumns>

</Identifier>

</ScalarOperator>

</RangeExpressions>

</Prefix>

</SeekKeys>

</SeekPredicateNew>

</SeekPredicates>

</IndexScan>

</RelOp>

</NestedLoops>

</RelOp>

</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

Comments

Anonymous
October 19, 2013
It's really useful article.Maybe you can look at this article (www.mssqltips.com/.../how-to-use-a-sql-server-plan-guide-to-tune-queries) It's also useful if you don't have access to app code and want to some improvement in your database queries.. keep in charm
Anonymous
March 08, 2016
Great Tips man i hope this helps everyone as it helped me love it www.snapchathackpass.com |

Freigeben über

Using Plan Guides and Plan Freezing

Comments

Zusätzliche Ressourcen