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

Hi All,

In my last post, I talked about the basics of Plan Freezing. And i gave an example of a query that gets migrated from SQL 2005 environment to SQL 2008 environment and starts misbehaving. Remember? If not, refer to my Part I post at: https://www.sqlservergeeks.com/sql-server-plan-freezing-plan-guides-in-sql-server-part-i/

Let me now give you some more examples where Plan Freezing can be useful.

Example 1. Many of you know about table & query hints. Hints can be used to tweak the query optimizer for better performance (yes, the intention is that). There are many hints like OPTIMIZE FOR, READPAST, RECOMPILE, Join hints, INDEX, etc. In order to use these hints you have to modify the original query. But, what if you are not allowed to modify the original query and you still want to use hints? For example, the query is embedded in an application or you are guided by an agreement where you cannot modify the source (ISV, etc). In many such scenarios you cannot modify the original query and thus you will not be able to use hints. Plan Guides comes to rescue here. How? We shall see that later.

Example 2. Suppose you have upgraded the hardware of your server. Surprisingly, your query has started performing poor. And you want to switch back to the old execution plan or rather you want to force the old execution plan. Plan Guides can again be used for such scenarios.

Example 3. You need to copy execution plans from one server to another.

Example 4. You want to overcome parameter sniffing issues (sometimes). Well this is a very important case for using plan guides. Parameter sniffing is a technique where the query optimizer sniffs the parameter value from a parameterized query and optimizes the query based on that parameter value. This is the default behavior and is good. But we know that once the execution plan is created, it is cached and will be re-used even if the parameter value changes which could have created another plan but that does not happen because a plan already exists in the cache. Well, plan guides can be useful here as well.

So, my intention here was to build a case for Plan Freezing & Plan Guides and the above examples just do that. In my next post (Part III), I shall start discussing how to implement plan guides in SQL Server 2008. Stay tuned. A lot more is coming up.