SQL Server: Plan Freezing & Plan Guides in SQL Server – Part I

Hi Friends,

I recently delivered a session on SQL Server 2008 plan guides to a bunch of DBAs at my client’s location. This session was very well appreciated by the attendees as it taught them something new. Many of us do not know about plan guides and the concept of plan freezing. Let me talk something about them. Plan Freezing is a concept where you can freeze a particular execution/query plan that already exists in the procedure cache. When you freeze a plan for a query, every time you run the same query (exactly the same query), SQL Server optimizer will use the same execution plan. To some this may sound silly because as a SQL Server DBA, you know that optimizer will do a good job by creating an optimized plan. But if you consider some wild scenarios, you will realize that this concept it so important and meaningful. Let’s take an example, suppose you are upgrading from SQL Server 2005 to 2008. There is a query that runs fantastically well in SQL 2K5. After upgrade you see the same query performing very poorly. You also notice that the execution plan in SQL 2K8 is different from SQL 2K5. Now what? Yes, you would want to find out why SQL 2K8 is producing a different plan. You may want to get in to tuning & troubleshooting mode. But does it mean that till the time you complete your investigations in your test environment, you production users will continue to suffer. You may not want that. What if you can copy the plan from SQL 2K5 and paste it in SQL 2K8 and freeze it :). There you go ! Once you freeze the same plan, your query continues to perform well. In a nutshell, you have forced SQL Server 2008 to use the same plan again and again that performs well. This is one of the examples of plan freezing. There are many such scenarios, particularly in case of ISVs where you want to apply some hints to a query but you are restricted to do so – and Plan Freezing comes to rescue. So, now you many wonder how to achieve this. Plan Freezing is achieved with the help of plan guides. We shall discuss this shortly. In my next post, I shall give some more cases and scenarios where plan freezing is helpful.





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 →

Leave a Reply

Your email address will not be published.