Plan Freezing

Plan freezing refers to forcing a plan for specific query. There are different reasons to freeze a plan, one is to provide plan stability for solving parameter sniffing issues.

This functionality was first introduced in SQL Server 2005 via using the USE PLAN query hint or using a plan guide to force a plan. However, using this feature was rather difficult with the requirement to capture the exact SQL statement. That was normally done using SQL Profiler trace or via querying the Dynamic Management Objects. Another issue was that changing the metadata (like dropping an index that affects the plan) resulted in error when trying to use the plan guide.

SQL Server 2008 added an array of enhancements. First, it simplified the mechanism to create a plan guide from plan handle via the sp_create_plan_guide_from_handle stored procedure. This eliminates the need to extract and match the SQL statement. Another enhancement is that if the metadata changes then the plan guide is silently skipped and the query execution continues with whatever execution plan is best.

Below is example of utilizing the new capabilities in SQL Server 2008 to freeze a plan.

-- Clear procedure cache

DBCC FREEPROCCACHE;





GO



-- First query run to get plan for freezing

SET STATISTICS XML ON;





EXEC sp_executesql   

N'SELECT orderid, customerid, orderdate, shippeddate

FROM Orders

WHERE customerid = N''CACYK''';

   

SET STATISTICS XML OFF;



GO



-- Create plan quide based on plan 

DECLARE @plan_handle VARBINARY(64);





SELECT @plan_handle = plan_handle 

FROM sys.dm_exec_query_stats AS S 

CROSS APPLY sys.dm_exec_sql_text(S.sql_handle) AS T

WHERE text LIKE '%Order%';





EXEC sp_create_plan_guide_from_handle 'PlanGuide2008', @plan_handle = @plan_handle;





GO





-- Check that plan guide was created

SELECT * FROM sys.plan_guides;





GO



-- Execute and verify UsePlan="1" and PlanGuideName="PlanGuide2008" 

-- in the XML plan output 



SET STATISTICS XML ON;





EXEC sp_executesql   

N'SELECT orderid, customerid, orderdate, shippeddate

FROM Orders

WHERE customerid = N''CACYK''';        

   

SET STATISTICS XML OFF;





GO





EXEC sp_control_plan_guide N'DROP', N'PlanGuide2008';