SQL Server: Plan Freezing & Plan Guides in SQL Server 2008 – Part III

Hi Friends,

This is my 3rd post in this series, “Plan Freezing & Plan Guides”.

Last 2 posts can be found here:

Part I;

Part II;

In my last 2 posts I talked about the basics of Plan Freezing and Plan Guides. In my last post particularly, I tried to build a case for Plan Guides as to why do we really need them. I gave a couple of examples as to why do we really need a plan guide. So now lets see how to actually implement a plan guide.

A plan guide is nothing but a database object. Just like a SP or a trigger, its a database object. You can create a plan guide by using 2 system stored procedures:

sp_create_plan_guide
sp_create_plan_guide_from_handle

As the name suggests, the first one is to create a plan guide from scratch by specifying all the parameters and the second one is used to create a plan guide from a query plan handle.

The purpose of the both the stored procedures are completely different.

In this post, I shall focus on sp_create_plan_guide.

Basically, there are 3 different types of plan guides: 1. Object Plan Guides, 2. SQL Plan Guides & 3. Template Plan Guides. All these 3 types are created using the same stored procedure.

You create Object Plan Guides if you want your plan guide to affect objects like Stored Procedures, UDFs, Triggers, etc. Basically, you are interested in guiding a particular T-SQL statement in these objects. Lets look at an example:

——————————————————————-
sp_create_plan_guide
@name = N’Guide1′,
@stmt = N’SELECT *FROM Sales.SalesOrderHeader AS h,
Sales.Customer AS c,
Sales.SalesTerritory AS t
WHERE h.CustomerID = c.CustomerID
AND c.TerritoryID = t.TerritoryID
AND CountryRegionCode = @Country_region’,
@type = N’OBJECT’,
@module_or_batch = N’Sales.GetSalesOrderByCountry’,
@params = NULL,
@hints = N’OPTION (OPTIMIZE FOR (@Country_region = N”US”))’
——————————————————————-

Now, let me explain the code:
1. Parameter @name: The name of the plan guide is Guide 1
2. @stmt: The complete statement that you want to target. That means, this is the statement appearing in the SP or trigger or UDF for which you are creating the plan guide.
3. @type: This is where you specify that this is an object plan guide.
4. @module_or_batch: the name of the object (yes, dont forget this is an object plan guide so you need to mention the name of the object where the above statement appears)
5. @params: not used in object plan guides. this parameter is only used in template plan guides, thus its NULL here.
6. @hints: Well, this is the crux. In this parameter you specify the hints that you want to apply to the statement.

Now, you might wonder, what exactly are we doing? So let me summarize if you are still wondering what’s happening?

Explanation:
Sales.GetSalesOrderByCountry is a stored procedure which I am not allowed to modify (may be because its protected or I do not have permissions or may be because I am bound by an ISV agreement, etc). This stored procedure contains a query specified in the @stmt parameter. The query returns orders based CountryRegionCode. Now, I want to optimize this query by applying a query hint but I cannot do that since I am not allowed or I cannot due to reasons mentioned earlier. So, I create the plan guide and use the OPTIMIZE FOR query hint in @hints parameter. The moment I execute the above code, the plan guide is in enabled. Now whenever GetSalesOrderByCountry  is executed, OPTIMIZE FOR query hint will be applied on the select query. And you can see that I am optimizing this query for value ‘US’.

The above was just one example/case where we can use plan guides. If you refer to Part I of this series, I have explained this case. If we apply this scenario to real life situations, there are a number of times when we want to apply hints but are not able to because we are guided by license agreements that prohibits us to modify source code.

Following this post, I leave you to explore more on your own. Yes, self learning is the way to go. Please refer to books online to know more about SQL plan guide and template plan guides. This concept is pretty vast and there are more intricacies here. Do forward this post to your friends and colleagues.

Stay tuned !!!

 

 

Amit Bansal

About Amit Bansal

Amit Bansal is always brainstorming around SQL Server. Despite working with SQL since 1997, he is amazed that he keeps learning new things every single day. SQL Server is AB's first love, and his wife does not mind that. He tries to share as much and spreads the SQL goodness. Internals and Performance Tuning excites him, and also gives him sleepless nights at times, simply because he is not a genius, but quite a hard worker and does not give up. It has been a long and exciting journey since 1997, you can read here: http://sqlmaestros.com/amit-bansal/ He is on Twitter: https://www.twitter.com/A_Bansal

View all posts by Amit Bansal →

One Comment on “SQL Server: Plan Freezing & Plan Guides in SQL Server 2008 – Part III”

  1. Hi,

    Am getting error when Stored procedure contains more than one select statement

    Can you explain

    Ex:

    create proc a( @i int ) as

    select Projid

    into #temp

    from zcProject

    where Projid < @i

    select *

    from #temp

Leave a Reply

Your email address will not be published. Required fields are marked *