SQL Server–Plan Freezing February 26th, 2013

Vinod Kumar

This is in continuation to the posts around hints written earlier – SQL Server TABLE HINT and SQL Server FORCESEEK Hint. In a lot of places we did mention about Plan Guides and Plan freezing over those articles and didn’t show what it actually is. The use of Plan freezing is something similar to KEEPFIXED PLAN which we are used too before. But this is even more powerful than you can ever think. The series of posts are not to encourage you to use these techniques extensively in your environments but should be used as a last resort in your code. SQL Server does a more than decent job is choosing the right plan and sometimes we need to guide it in case of upgraded code, old behaviors, third party code that cannot be changed etc.

SQL Server first added the ability to create plan guides in SQL Server 2005. By applying a hint at the server side, a DBA could override the behavior for a specific problematic query. However, with the first release of plan guides it was recommended that you only create a plan guide when it was strictly necessary and keep the number of plan guides to a minimum.

The plan freezing framework in SQL Server 2008 builds on plan guides and is intended to “lock down” plans for all repeated queries against a database or the entire system as opposed to just a handful of queries. If the server handles a well defined, repeated set of parameterized queries such as queries from an OLTP-based application, then plan freezing allows an administrator to pick a time when the system is running well (i.e., they are happy with the query plans that are being used), “freeze” those plans, thereby ensuring that any subsequent compiles/recompiles don’t pick a different plan and introduce unpredictability into the system.

changes to plan guides and plan forcing in SQL 2008:

  1. In previous versions, if a plan guide applied a hint which resulted in a “no plan” error (8722), the error was returned to the client and the query did not run. In SQL Server 2008, if the hint results in a no plan error the error is caught internally and the query is re-optimized without the hints. You can monitor when this occurs by capturing the Plan Guide Unsuccessful trace event or by using the SQL Server:SQL Statistics\Misguided plan executions/sec performance counter.
  2. Management Studio now allows you to script out plan guides
  3. A new sys.fn_validate_plan_guide function is added which allows you to validate a plan guide and see any error(s) that might occur because of the hints that were applied
  4. The new sys.sp_create_plan_guide_from_handle procedure allows you to create a plan guide with a USE PLAN hint on a statement based on the current cached plan. This feature is what makes it possible to “freeze” plans for a whole database or system.

Knowing the fineprints

The hint in a plan guide created by sys.sp_create_plan_guide_from_handle is a USE PLAN hint (MSDN) based on the current Showplan XML for the statement. The Showplan XML output always contains fully qualified object names ([dbname].[schema].[owner]). When attempting to force a plan, the object names must be valid. If you freeze plans in a database, backup that database and then restore it with a different database name (such as on a test server, bring in a clone database, etc) all of these plan guides with the USE PLAN hints become invalid since the database name changed.

Because SQL 2008 no longer fails the query when the hints are invalid, any queries will run with whatever query plan the optimizer would normally create for that statement rather than with the plan specified in the USE PLAN hint. Fn_validate_plan_guide can be used to determine if there are validation errors of this nature (as long as the original database doesn’t also exist on this server—in which case the object does exists and fn_validate_plan_guide succeeds but the plan guide isn’t used because the object isn’t in the correct database context). The only way to fix this is to script out the plan guide, perform a find/replace on the database name and then recreate the plan guide(s).

Plan Freezing at work

In this section we will walk through freezing a plan, modifying the table so that the forced plan is no longer valid, then using the new trace events and fn_validate_plan_guide to view how we could determine why the plan guide is no longer successful.

1. I am doing this on a test environment and to ensure we get consistent results – we are going to clear the procedure cache using the following command. Please DONOT do this on your production environments.


2. Next let us run a Dynamic SQL query that will insert into the cache.


FROM Sales.SalesOrderHeader h

JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID

WHERE h.OrderDate BETWEEN @P1 AND @P2′,

N’@P1 DATETIME,@P2 DATETIME’, ‘20050730’, ‘20050731’


3. Let us find the plan handle for the query that was run in the previous step.

SELECT plan_handle FROM sys.dm_exec_query_stats qs

CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle)

WHERE text LIKE ‘%SalesOrderHeader%’

4. Using the Plan Handle from previous step, let us create a plan guide for the query.

sp_create_plan_guide_from_handle ‘My_Plan_Guide’,


5. Let us run the query again now with SHOWPLAN XML enabled.



EXEC sp_executesql N’SELECT COUNT(*) as c

FROM Sales.SalesOrderHeader h

JOIN Sales.SalesOrderDetail d ON h.SalesOrderID = d.SalesOrderID

WHERE h.OrderDate BETWEEN @P1 AND @P2′,

N’@P1 DATETIME,@P2 DATETIME’, ‘20050730’, ‘20050731’



Click on the XML hyperlink to view the query plan. Note in the Properties pane that it shows the plan guide was used. Also note that the query plan uses an index named: [PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID]

6. For illustration purposes let us DROP the index used in the query plan. Remember, we are doing this with-in a transaction so that we can rollback the same for learning purposes.


ALTER TABLE Sales.SalesOrderDetail DROP CONSTRAINT PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID

7. Using Profiler, let us try to catch the Plan Guide UnSuccessful events. Below is the event from Profiler. Run the same query from Step 5 again to check if it now produces this event. The XML Showplan also verifies that it didn’t use the plan guide we created before.


8. To determine why the plan guide is not used, look up the plan guide ID and run fn_validate_plan_guide with this ID to see any errors are reported. Note that in this case it specifically indicates that the object referred to in the query is not present.

SELECT plan_guide_id FROM sys.plan_guides

WHERE name = N’My_Plan_Guide’;

SELECT * FROM sys.fn_validate_plan_guide(<plan_guide_id>)

It is interesting to see how the error gets reported. The message is as below.

Index ‘AdventureWorks2012.Sales.SalesOrderDetail.PK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID’, specified in the USE PLAN hint, does not exist. Specify an existing index, or create an index with the specified name.

9. We can Disable, Enable or even Drop a plan guide using the following commands.

–Disable the plan guide.

EXEC sp_control_plan_guide N’DISABLE’, N’My_Plan_Guide’;


–Enable the plan guide.

EXEC sp_control_plan_guide N’ENABLE’, N’My_Plan_Guide’;


–Drop the plan guide.

EXEC sp_control_plan_guide N’DROP’, N’My_Plan_Guide’;


We can use sp_control_plan_guide to disable ALL planguides for a given database too. Check the MSDN links for more information.

10. Since we started a transaction earlier, let us rollback the same.


Final words

Hope these steps gave you a high level understanding of how plan freezing works inside SQL Server. Maybe in future posts, will also show how to use Plan Guides with a step by step as above. Do tell me if you are using these in your environments and if so would love to know what is the scenario for the same.

Tags: , , , , , ,

This entry was posted on Tuesday, February 26th, 2013 at 08:30 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

2 Responses to “SQL Server–Plan Freezing”

  1. CodeSpread says:

    Nice Step-by-Step Walk-through of Plan freezing.

Leave a Reply