﻿﻿Plan Guides

In the plan guide, you can specify either the OPTION clause or a specific query plan for the statement you want to optimize. You also specify the T-SQL statement for which the plan guide is intended. The SQL Server Query Optimizer matches the executing T-SQL statement with the statement specified in the plan guide and then uses the guide to create the execution plan. Note that you cannot use plan guides in SQL Server 2012 Express edition. You can create the following types of plan guides: – OBJECT plan guides are used by the Query Optimizer to match queries inside stored procedures, scalar user-defined functions, multistatement table-valued user-defined functions, and DML triggers. – SQL plan guides are used by the Query Optimizer to match stand-alone queries or queries in ad hoc batches. – TEMPLATE plan guides are used by the Query Optimizer to match stand-alone queries that can be parameterized to a specified form. You can force parameterization with template guides.

You create plan guides by using the sys.sp_create_plan_guide system procedure. You can disable, enable, or drop a plan guide by using the sys.sp_control_plan_guide system procedure. You can create a plan guide from a cached query plan by using the sys.sp_create_plan_guide_from_handle system procedure. You can validate a plan by using the sys.fn_validate_plan_guide system function. A plan guide might become invalid because of a database schema change. You can use the sys.sp_get_query_template system procedure to get the parameterized form of a query. This procedure is especially useful to get the parameterized query for the TEMPLATE plan guide. Consider the following stored procedure. CREATE PROCEDURE Sales.GetCustomerOrders (@custid INT) AS SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE custid = @custid; For the vast majority of customers—for example, a customer that has a custid equal to 71—the query in the procedure is not very selective; therefore, a table or clustered index scan would be the most appropriate to use. However, for some rare customers with only a few orders—for example, a customer that has a custid equal to 13—an index seek with a lookup would be better. If a user executes the procedure for customer 13 first, then the procedure plan in the cache would not be appropriate for most of the further executions. By creating a plan guide that uses a query hint that forces optimization of the query in the procedure for the customer that has a custid equal to 71, you are optimizing the stored procedure execution for most of the customers. The following code creates the plan guide. EXEC sys.sp_create_plan_guide @name = N’Cust71′, @stmt = N’ SELECT orderid, custid, empid, orderdate FROM Sales.Orders WHERE custid = @custid;’, @type = N’OBJECT’, @module_or_batch = N’Sales.GetCustomerOrders’, @params = NULL, @hints = N’OPTION (OPTIMIZE FOR (@custid = 71))’; If you execute the procedure by using different parameters after you cleaned the cache to make sure that an older plan for this procedure is not present in the cache, SQL Server always optimizes the query for the custid value 71, and thus uses a clustered index scan. This is true even if you execute the query with value 13 for the custid first, like the following code shows. — Clearing the cache DBCC FREEPROCCACHE; — Executing the procedure with different parameters EXEC Sales.GetCustomerOrders @custid = 13; EXEC Sales.GetCustomerOrders @custid = 71;

Figure 17-24 shows the execution plan for this batch. You can see that in both executions, the Clustered Index Scan iterator was used.

Figure 17-24 A plan for two executions of a stored procedure that uses a plan guide. You can always get a list of all plan guides in a database by querying the sys.plan_guides catalog view. You can also list all of the hints used in each plan guide, like the following query shows. SELECT plan_guide_id, name, scope_type_desc, is_disabled,query_text, hints FROM sys.plan_guides; The following code cleans up the TSQL2012 database. EXEC sys.sp_control_plan_guide N’DROP’, N’Cust71′; DROP PROCEDURE Sales.GetCustomerOrders;

About Techveze.com

Techveze is a test-preparation site for the IT certifications. It offers complete exam knowledge and a customized practice ground for those who are preparing for the certifications and want to rapid progress in preparation. Our Web site holds lots of questions with answers for you to practice, and progress report customized to serve you with your exam readiness. You can increase your scores through practice.