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