Compartilhar via

Troubleshooting Plan Cache Issues

  • Artigo

In order to start addressing problems with plan cache usage and management, you first must determine that existing problems are actually caused by plan caching issues. Performance problems caused by misuse or mismanagement of plan cache, or inappropriate recompilation, can manifest themselves as simply a decrease in throughput or an increase in query response time. Problems with caching can also show up as out-of-memory errors or connection time-out errors, which can be caused by all sorts of different conditions.

Wait Statistics Indicating Plan Cache Problems

In order to determine that plan caching behavior is causing problems, one of the first things to look at is your SQL Server’s wait statistics. Wait statistics will be covered in more detail in Chapter 6 “Concurrency Problems,” but here we’ll tell you about some of the primary wait types that can indicate problems with your plan cache.

Wait statistics are displayed when you query the sys.dm_os_wait_stats view. The query below will list all of the resources that your SQL Server service might have to wait for, and it will display the resources with the longest waiting list:

SELECT *
FROM sys.dm_os_wait_stats
ORDER BY waiting_tasks_count DESC

As discussed in Chapter 1, the values shown in this view are cumulative, so if you need to see the resources being waited on during a specific time period, you will have to poll the view at the beginning and end of the period. If you see relatively large wait times for any of the following resources, or if these resources are near the top of the list returned from the query above, you should investigate your plan cache usage.

  • CMEMTHREAD waits

    This wait type indicates that there is contention on the memory object from which cache descriptors are allocated. A very high rate of insertion of entries into the plan cache can cause contention problems. Similarly, contention can also occur when entries are removed from cache and the resource monitor thread is blocked. There is only one thread-safe memory object from which descriptors are allocated, and as we’ve seen, there is only a single cache store for adhoc compiled plans.

    Consider the same procedure being called dozens or hundreds of times. Remember that SQL Server 2005 will cache the adhoc shell query that includes the actual parameter for each individual call to the procedure, even though there may be only one cached plan for the procedure itself. As SQL Server starts experiencing memory pressure, the work to insert the entry for each individual call to the procedure can begin to cause excessive waits resulting in a drop in throughput or even out-of-memory errors.

    SQL Server 2005 Service Pack 2 includes some changes to caching behavior to alleviate some of the flooding of cache that can occur when the same procedure or autoparameterized query is called repeatedly with different parameters. In Service Pack 2, zero-cost batches that contain SET statements or transaction control will not be cached at all. The only exception is for those batches that contain only SET and transaction control statements. This is not that much of a loss, as plans for batches containing SET statements can never be reused in any case. Also in Service Pack 2, the memory object from which cache descriptors are allocated has been partitioned across all the CPUs to alleviate contention on the memory object which should reduce CMEMTHREAD waits.

  • SOS_RESERVEDMEMBLOCKLIST waits

    This wait type can indicate the presence of cached plans for queries with a large number of parameters, or with a large number of values specified in an IN clause. These types of queries require that SQL Server allocate in larger units, called multipage allocations. You can look at the view sys.dm_os_memory_cache_counters to see the amount of memory allocated in the multipage units.

    SELECT name, type, single_pages_kb, multi_pages_kb, 
    single_pages_in_use_kb, multi_pages_in_use_kb 
FROM sys.dm_os_memory_cache_counters
WHERE type = 'CACHESTORE_SQLCP' OR type = 'CACHESTORE_OBJCP';

    Clearing out plan cache with DBCC FREEPROCCACHE can alleviate problems caused by too many multipage allocations, at least until the queries are reexecuted and the plans are cached again. In addition, the cache management changes in SQL Server 2005 Service Pack 2 can also reduce the waits on SOS_RESERVEDMEMBLOCKLIST. You can also consider rewriting the application to use alternatives to long parameters or long IN lists. In particular, long IN lists can almost always be improved by creating a table of the values in the IN list and joining with that table.

  • RESOURCE_SEMAPHORE_QUERY_COMPILE waits

    This wait type indicates that there are a large number of concurrent compilations. In order to prevent inefficient use of query memory, SQL Server 2005 limits the number of concurrent compile operations that need extra memory. If you notice a high value for RESOURCE_SEMAPHORE_QUERY_COMPILE waits, you can examine the entries in the plan cache through the sys.dm_exec_cached_plans view, as shown:

    SELECT usecounts, cacheobjtype, objtype, bucketid, text 
FROM sys.dm_exec_cached_plans
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) 
WHERE cacheobjtype = 'Compiled Plan' 
ORDER BY objtype;

    If there are no results with the objtype value of Prepared, it means that SQL Server is not autoparameterizing your queries. You can try altering the database to PARAMETERIZATION FORCED in this case, but this option will affect the entire database, including queries that might not benefit from autoparameterization. To force SQL Server to autoparameterize just certain queries, plan guides can be used. We’ll discuss plan guides in the next section.

    Keep in mind that caching is done on a per-batch level. If you try to force parameterization using sp_executesql or Prepare/Execute, all the statements in the batch must be parameterized for the plan to be reusable. If a batch has some parameterized statements and some using constants, each execution of the batch with different constants will be considered distinct, and there will be no value to the parameterization in only part of the batch.

Other Caching Issues

In addition to looking at the wait types that can indicate problems with caching, there are some other coding behaviors that can have a negative impact on plan reuse.

  • Verify parameter types, both for prepared queries and autoparameterization.

    With prepared queries, you actually specify the parameter datatype, so it’s easier to make sure you are always using the same type. When SQL Server parameterizes, it makes its own decisions as to datatype. If you look at the parameterized form of your queries of type Prepared, you’ll see the datatype that SQL Server assumed. We saw earlier in the chapter that a value of 12,345 will be assumed to be a different datatype than 12, and two queries that are identical except for these specific values will never be able to share the same autoparameterized plan.

    If the parameter passed is numeric, SQL Server will determine the datatype based on the precision and scale. A value of 8.4 will have a datatype of numeric (2, 1) and 8.44 will have a datatype of numeric (3, 2). For varchar data type, server side parameterization will not be so dependent on the length of the actual value. Take a look at these two queries in the Northwind2 database.

    SELECT * FROM Customers
WHERE CompanyName = 'Around the Horn'; 
GO
SELECT * FROM Customers
WHERE CompanyName = 'Rattlesnake Canyon Grocery'; 
GO

    Both of these queries will be autoparameterized to the following:

    (@0 varchar(8000))select * from Customers where CompanyName = @0

  • Monitor plan cache size and data cache size.

    In general, as more queries are run, the amount of memory used for data page caching should increase along with the amount of memory used for plan cache. However, as we saw previously when discussing plan cache size, in SQL Server 2005 prior to Service Pack 1, the maximum limit for plan cache could grow to be up to 80 percent of the total buffer pool before memory pressure would start forcing plans to be evicted. This can result in severe performance degradation for those queries that depend on good data caching behavior. For any amount of memory greater than 4 GB, Service Pack 2 changes the size limit that plan cache can grow to before memory pressure is indicated. One of the easiest places to get a comparison of the pages used for plan cache and the pages used for data cache is the performance counters. Take a look at the following counters: SQL Server: Plan Cache\Cache Pages(_Total) and SQLServer: BufferManager\Database pages.

Troubleshooting Caching and Recompilation Summary

There are two main tools for detecting excessive compiles and recompiles. Keep in mind that compiling and recompiling are not the same thing. Recompiling is done when an existing module or statement is determined to be no longer valid or no longer optimal. All recompiles are considered compiles, but not vice versa. For example, when there is no plan in cache, or when executing a procedure using the WITH RECOMPILE option, or executing a procedure that was created WITH RECOMPILE, SQL Server considers this a compile but not a recompile. You can use either System Monitor or SQL Trace to detect compilations and recompilations.

System Monitor (Perfmon)

The SQL Statistics object allows you to monitor compilations, as well as the types of requests that are sent to your SQL Server. You can monitor the number of query compilations and recompilations along with the number of batches processed to determine if the compiles are a major factor in system resource use, especially CPU use. Ideally, the ratio of SQL Recompilations/Sec to Batch Requests/Sec should be very low. Of course, low is a relative term, but if you have baseline measurements, you can determine when this ratio increases, and use that as an indication that recompiles are increasing, and you need to do further investigation into the causes.

SQL Trace

If the System Monitor counters indicate a high number of recompiles, you would then need to look at trace data to determine which batches or stored procedures were being recompiled. The SQL Trace data can give you that information along with the reason for the recompilation. You can use the following events to get this information:

  • SP:Recompile/SQL:StmtRecompile

    The SP:Recompile event (in the Stored Procedures category) and the SQL:StmtRecompile event (in the TSQL category) indicate which stored procedures and statements have been recompiled. When you recompile a stored procedure, one of each of these events is generated for every statement within a stored procedure that is recompiled. Keep in mind that when a stored procedure recompiles, only the statement that caused the recompilation is recompiled. This is different from SQL Server 2000, in which the stored procedures were always recompiled in their entirety. Some of the more important data columns for the SP:Recompile event class are listed below. The EventSubClass data column is particularly useful for determining the reason for the recompile. SP:Recompile is triggered only for procedures or triggers that are recompiled, so for SQL Server 2005, it might be more useful to monitor SQL:StmtRecompile as this event class is fired when any type of batch, adhoc statement, stored procedure, or trigger is recompiled.

If these tools indicate that you have excessive compilation or recompilation, you can consider the following actions:

  • If the recompile is caused by a change in a SET option, the SQL Trace text data for Transact-SQL statements immediately preceding the recompile event can indicate which SET option changed. It’s best to change SET options when a connection is first made, and avoid changing them after you have started submitting statements on that connection, or inside a store procedure.
  • Recompilation thresholds for temporary tables are lower than for normal tables, as we discussed earlier. If the recompiles on a temporary table are caused by statistics changes, a trace will have a data value in the EventSubclass column that indicates that statistics changed for an operation on a temporary table. You can consider changing the temporary tables to table variables, for which statistics are not maintained. Because no statistics are maintained, changes in statistics cannot induce recompilation. However, lack of statistics can result in suboptimal plans for these queries. Your own testing can determine if the benefit of table variables is worth the cost. Another alternative is to use the KEEP PLAN query hint, which sets the recompile threshold for temporary tables to be the same as for permanent tables.
  • To avoid all recompilations that are caused by changes in statistics, whether on a permanent or a temporary table, you can specify the KEEPFIXED PLAN query hint. With this hint, recompilations can only happen because of correctness-related reasons, as described earlier. An example might be when a recompilation occurs if the schema of a table that is referenced by a statement changes, or if a table is marked for recompile by using the sp_recompile stored procedure.
  • Another way to prevent recompiles caused by statistics changes is by turning off the automatic updates of statistics for indexes and statistics. Note, however, that turning off the autostatistics feature is usually not a good idea. If you do, the optimizer will no longer be sensitive to data changes and is likely to come up with a suboptimal plan. This method should only be considered as a last resort after exhausting all other options.
  • All Transact-SQL code should use two-part object names (for example, Inventory. ProductList) to indicate exactly what object is being referenced, which can help avoid recompilation.
  • Do not use DDL within conditional constructs such as IF statements.
  • Explore the Database Engine Tuning Advisor (DTA) to see if it recommends any indexing changes that might improve the execution time of your queries.
  • Check to see if the stored procedure was created with the WITH RECOMPILE option. In many cases, there are only one or two statements within a stored procedure that might benefit from recompilation on every execution, and in SQL Server 2005, we can use the RECOMPILE query hint for just those statements. This is much better than using the WITH RECOMPILE option for the entire procedure, which means every statement in the procedure will be recompiled every time the procedure is executed.

Plan Guides and Optimization Hints

In Chapters 3 and 4, we looked at execution plans to determine when a query was being executed optimally. We’ve looked at situations in which SQL Server will reuse a plan when it might have been best to come up with a new one, and we’ve seen situations in which SQL Server will not reuse a plan even if there is perfectly good one in cache already. One way to encourage plan reuse that has already been discussed in this chapter is to enable the PARAMETERIZATION FORCED database option. In other situations, where we just can’t get the optimizer to reuse a plan, we can use optimizer hints. Optimizer hints can also be used to force SQL Server to come up with a new plan in those cases in which it might be using an existing plan. Although there are dozens of hints that you can use in your Transact-SQL code to affect the plan that SQL Server comes up with, in this section we’ll only specifically describe those hints that affect recompilation, as well as the mother of all hints, USE PLAN, which is new in SQL Server 2005. Finally, we’ll discuss a new SQL Server 2005 feature called plan guides.

Optimization Hints

All of the hints that we’ll be telling you about in this section are referred to in the SQL Server Books Online as Query Hints, to distinguish them from Table Hints, which are specified in your FROM clause after a table name, and Join Hints, which are specified in your JOIN clause before the word join. However, since technically all hints affect your queries, we frequently refer to query hints as Option Hints, since they are specified in a special clause called the OPTION clause, which is used just for specifying this type of hint. An OPTION clause, if included in a query, is always the last clause of any Transact-SQL statement, as you’ll see in the code examples below.

RECOMPILE The RECOMPILE hint forces SQL Server to recompile a query. It is particularly useful when only a single statement within a batch needs to be recompiled. You know that SQL Server compiles your Transact-SQL batches as a unit, determining the execution plan for each statement in the batch, and it doesn’t execute any statements until the entire batch is compiled. This means that if the batch contains a variable declaration and assignment, the assignment doesn’t actually take place during the compilation phase. When the following batch is optimized, SQL Server doesn’t have a specific value for the variable.

USE Northwind2;
DECLARE @custID nchar(10); 
SET @custID = 'LAZYK';
SELECT * FROM Orders WHERE CustomerID = @custID;

The plan for the SELECT statement will show that SQL Server is scanning the entire clustered index, because during optimization, SQL Server had no idea what value it was going to be searching for, and couldn’t use the histogram in the index statistics to get a good estimate of the number of rows. If we had replaced the variable with the constant LAZYK, SQL Server could have determined that only a very few rows would qualify, and would have chosen to use the nonclustered index on customerID. The RECOMPILE hint can be very useful here, as it tells the SQL Server optimizer to come up with a new plan for the single SELECT statement, right before that statement is executed, which will be after the SET statement has executed.

USE Northwind2;
DECLARE @custID nchar(10); 
SET @custID = 'LAZYK';
SELECT * FROM Orders WHERE CustomerID = @custID 
OPTION (RECOMPILE);

Note that a variable is not the same as a parameter, even though they are written the same way. Because a procedure is only compiled when it is being executed, SQL Server will always have a specific parameter value to use. Problems arise when the previously compiled plan is then used for different parameters. However, for a local variable, the value is never known when the statements using the variable are compiled, unless the RECOMPILE hint is used.

OPTIMIZE FOR This hint tells the optimizer to optimize the query as if a particular value has been used for a variable. Execution will use the real value. Keep in mind that the OPTIMZE FOR hint does not force a query to be recompiled. It only instructs SQL Server to assume a variable or parameter has a particular value in those cases in which SQL Server has already determined that the query needs optimization. As the OPTIMIZE FOR hint was discussed in Chapter 4, “Troubleshooting Query Performance,” we won’t say any more about it here.

KEEP PLAN This hint relaxes the recompile threshold for a query, particularly for queries accessing temporary tables. As we saw earlier in this chapter, a query accessing a temp table can be recompiled when as few as six changes have been made to the table. If the query uses the KEEP PLAN hint, the recompilation threshold for temp tables is changed to be the same as for permanent tables.

KEEPFIXED PLAN This hint inhibits all recompiles because of optimality issues. With this hint, queries will only be recompiled when forced, or if the schema of the underlying tables is changed, as described in the section on correctness-based recompiles above.

PARAMETERIZATION SIMPLE | FORCED This hint overrides the PARAMETERIZATION option for a database. If the database is set to PARAMETERIZATION FORCED, individual queries can avoid that and only be parameterized if they meet the strict list of conditions. Alternatively, if the database is set to PARAMETERIZATION SIMPLE, individual queries can be parameterized on a case-by-case basis. Note however that the PARAMETERIZATION hint can only be used in conjunction with plan guides, which we’ll discuss shortly.

USE PLAN This hint was discussed in Chapter 4, as a way to force SQL Server to use a plan that you might not be able to specify using the other hints. The plan specified must be in XML format, and can be obtained from a query that uses the desired plan by using the option SET SHOWPLAN_XML ON. Because USE PLAN hints contain a complete XML document in the query hint, they are best used within plan guides, which are discussed in the next section.

Purpose of Plan Guides

Although in most cases it is recommended that you allow the SQL Server query optimizer to determine the best plan for each of your queries, there are times when the optimizer just can’t come up with the best plan and you may find that the only way to get reasonable performance is to use a hint. This is usually a straightforward change to your applications, once you have verified that the desired hint is really going to make a difference. However, in some environments you have no control over the application code. In cases in which the actual SQL queries are embedded in inaccessible vendor code, or in which modifying vendor code would break your licensing agreement or invalidate your support guarantees, you might not be able to simply add a hint onto the misbehaving query.

SQL Server 2005’s Plan Guides provide a solution by giving you a mechanism to add hints to a query without changing the query itself. Basically, a plan guide tells SQL Server’s optimizer that if it tries to optimize a query having a particular format, it should add a specified hint to the query. SQL Server 2005 supports three kinds of plan guides: SQL, Object, and Template, which we’ll explore shortly.

Plan guides are available in the Standard, Enterprise, Evaluation, and Developer Editions of SQL Server 2005. If you detach a database containing plan guides from a supported edition and attach the database to an unsupported edition, such as Workgroup or Express, SQL Server will not use any plan guides. However the metadata containing information about plan guides will still be available.

Types of Plan Guides

The three types of plan types can all be created using the sp_create_plan_guide procedure. The general form of the sp_create_plan_guide procedure is as follows:

sp_create_plan_guide 'plan_guide_name', 'statement_text',
'type_of_plan_guide', 'object_name_or_batch_text', 
'parameter_list', 'hints'

We’ll discuss each of the types of plan guides, and then we’ll look at the mechanisms for working with plan guides and the metadata that keeps track of information about them.

Object Plan Guides A plan guide of type object indicates that you are interested in a Transact-SQL statement appearing in the context of a SQL Server object, which can be a stored procedure, a user defined function, or a trigger in the database in which the plan guide is created. As an example, suppose we have a stored procedure called Sales.GetOrdersByCountry that takes a country as a parameter, and after some error checking and other validation, it returns a set of rows for the Orders placed by Customers in the specified country. Suppose further that our testing has determined that a parameter value of US gives us the best plan. Here is an example of a plan guide that tells SQL Server to use the OPTIMIZE FOR hint, whenever the specified statement is found in the Sales.GetOrdersByCountry procedure:

EXEC sp_create_plan_guide
@name = N'plan_US_Country', 
@stmt =
N'SELECT SalesOrderID, OrderDate, h.CustomerID, h.TerritoryID
FROM Sales.SalesOrderHeader AS h 
INNER JOIN Sales.Customer AS c
ON h.CustomerID = c.CustomerID 
INNER JOIN Sales.SalesTerritory AS t
ON c.TerritoryID = t.TerritoryID 
WHERE t.CountryRegionCode = @Country',
@type = N'OBJECT',
@module_or_batch = N'Sales.GetOrdersByCountry', 
@params = NULL,
@hints = N'OPTION (OPTIMIZE FOR (@Country = N''US''))';

Once this plan is created in the AdventureWorks database, every time the Sales.GetOrdersByCountry procedure is compiled, the statement indicated in the plan will be optimized as if the actual parameter passed was the string US. No other statements in the procedure will be affected by this plan, and if the specified query occurs outside of the Sales.GetOrdersByCountry procedure, the plan guide will not be invoked. (The companion website, which contains all the code used in all the book examples, also contains a script to build the Sales.GetOrdersByCountry procedure.)

SQL Plan Guides A plan guide of type SQL indicates you are interested in a particular SQL statement, either as a stand-alone statement, or in a particular batch. Transact-SQL statements that are sent to SQL Server by CLR objects or extended stored procedures, or that are part of dynamic SQL invoked with the EXEC (sql_string) construct, will be processed as batches on SQL Server. To use them in a plan guide, their type should be set to SQL. For a stand-alone statement, the @module_or_batch parameter to sp_create_plan_guide should be set to NULL, so that SQL Server will assume the batch and statement have the same value. If the statement you are interested in is in a larger batch, the entire batch text needs to be specified in the @module_or_batch parameter. If a batch is specified for a SQL plan guide, the text of the batch needs to be exactly the same as it will appear in the application. The rules aren’t quite as strict as those for adhoc query plan reuse, discussed earlier in this chapter, but they are close. Make sure you use the same case, the same whitespace, etc. as your application does.

Here is an example of a plan guide that tells SQL Server to use only one CPU (no parallelization) when a particular query is executed as a stand-alone query:

EXEC sp_create_plan_guide
@name = N'plan_SalesOrderHeader_DOP1', 
@stmt = N'SELECT TOP 10 *
FROM Sales.SalesOrderHeader 
ORDER BY OrderDate DESC',
@type = N'SQL',
@module_or_batch = NULL, 
@params = NULL, 
@hints = N'OPTION (MAXDOP 1)';

Once this plan is created in the AdventureWorks database, every time the specified statement is encountered in a batch by itself, it will have a plan created that uses only a single CPU. If the specified query occurs as part of a larger batch, the plan guide will not be invoked.

Template Plan Guides A SQL plan guide of type Template can only use the PARAMETERIZATION FORCED or PARAMETERIZATION SIMPLE hints, to override the PARAMETERIZATION database setting. Template guides are a bit trickier to work with, as you have to have SQL Server construct a template of your query in the same format that it will be in once it is parameterized. This isn’t hard, because SQL Server supplies us with a special procedure called sp_get_query_template, but to use template guides you need to perform several prerequisite steps. If you take a look at the two plan guide examples above, you’ll see that the parameter called @params was NULL for both OBJECT and SQL plan guides. You only specify a value for @params with a TEMPLATE plan guide.

To see an example of using a template guide and forcing parameterization, first clear your procedure cache and then execute these two queries in the AdventureWorks database:

DBCC FREEPROCCACHE; 
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h 
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID 
WHERE h.SalesOrderID = 45639; 
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h 
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID 
WHERE h.SalesOrderID = 45640;

These queries are very similar, and the plans for both are identical, but because the query is considered too complex, SQL Server will not autoparameterize them. If, after executing both queries, you look at the plan cache, you’ll see only adhoc queries. If you’ve created the sp_cacheobjects view described earlier in the chapter, you could use that, otherwise, replace sp_cacheobjects with sys.syscacheobjects.

SELECT objtype, dbid, usecounts, sql 
FROM sp_cacheobjects 
WHERE cacheobjtype = 'Compiled Plan';

To create a plan guide to force statements of this type to be parameterized, we first need to call the procedure sp_get_query_template and pass two variables as output parameters. One parameter will hold the parameterized version of the query, and the other will hold the parameter list and the parameter datatypes. The code below will then SELECT these two output parameters so you can see their contents. Of course, you can remove this SELECT from your own code. Finally, we call the sp_create_plan_guide procedure, which instructs the SQL Server optimizer to use PARAMETERIZATION FORCED anytime it sees a query that matches this specific template. In other words, anytime a query that parameterizes to the same form as the query here, it will use the same plan already cached.

DECLARE @sample_statement nvarchar(max); 
DECLARE @paramlist nvarchar(max); 
EXEC sp_get_query_template
N'SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID 
WHERE h.SalesOrderID = 45639;',
@sample_statement OUTPUT, 
@paramlist OUTPUT
SELECT @paramlist as parameters, @sample_statement as statement 
EXEC sp_create_plan_guide @name = N'Template_Plan',
@stmt = @sample_statement, 
@type = N'TEMPLATE', 
@module_or_batch = NULL, 
@params = @paramlist,
@hints = N'OPTION(PARAMETERIZATION FORCED)';

After creating the plan guide, run the same two statements as above, and then examine the plan cache:

DBCC FREEPROCCACHE; 
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h 
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID 
WHERE h.SalesOrderID = 45639; 
GO
SELECT * FROM AdventureWorks.Sales.SalesOrderHeader AS h 
INNER JOIN AdventureWorks.Sales.SalesOrderDetail AS d
ON h.SalesOrderID = d.SalesOrderID 
WHERE h.SalesOrderID = 45640; 
GO
SELECT objtype, dbid, usecounts, sql 
FROM sp_cacheobjects 
WHERE cacheobjtype = 'Compiled Plan';

You should now see a prepared plan with the following parameterized form:

(@0 int)select * from AdventureWorks.Sales.SalesOrderHeader as h 
    inner join AdventureWorks.Sales.SalesOrderDetail as d 
    on h.SalesOrderID = d.SalesOrderID 
    where h.SalesOrderID = @0

Managing Plan Guides

In addition to the sp_create_plan_guide and sp_get_query_template procedures, the other basic procedure for working with plan guide is sp_control_plan_guide. This procedure allows you to DROP, DISABLE, or ENABLE a plan guide, using the following basic syntax:

sp_control_plan_guide '<control_option>' [, '<<msl_i>plan_guide_name</msl_i>>']

There are six possible control_option values: DISABLE, DISABLE ALL, ENABLE, ENABLE ALL, DROP, and DROP ALL. The plan_guide_name parameter is optional, because with any of the ALL control_option values, no plan_guide_name value is supplied. Plan guides are local to a particular database, so the DISABLE ALL, ENABLE ALL, and DROP ALL values apply to all plan guides for the current database. In addition, plan guides behave like schema-bound views in a way; the stored procedures, triggers, or functions referred in any OBJECT plan guide in a database cannot be altered or dropped. So for our OBJECT plan guide above, as long as the plan guide exists, the AdventureWorks.Sales.GetOrdersByCountry procedure cannot be altered or dropped. This is true whether the plan guide is disabled or enabled, and will remain true until all plan guides referencing those objects are dropped with sp_control_plan_guide.

The metadata view that contains information about plan guides in a particular database is sys.plan_guides. This view contains all the information supplied in the sp_create_plan_guide procedure plus additional information such as the creation date and last modification date of each plan guide. Using the information in this view, you can manually reconstruct the plan guide definition, if necessary. However, no built-in mechanism automatically scripts the plan guide definitions, as you can do with most other SQL Server objects, so it is strongly recommended that you save your actual sp_create_plan_guide scripts in case you ever need to move your definitions to a new server. Eric Hanson, from the SQL Server development team at Microsoft, has created a set of procedures to interpret the information in sys.plan_guides and generate the appropriate sp_create_plan_guide statements. These scripts are available on the companion website.

Plan Guide Considerations

In order for SQL Server to determine that there is an appropriate plan guide to use, the statement text in the plan guide must exactly match the query being compiled. This must be an exact character-for-character match, including case, white space, and comments, just like when SQL Server is determining whether it can reuse adhoc query plans, as we discussed earlier in the chapter. If your statement text is close, but not quite an exact match, this can lead to a situation that is very difficult to troubleshoot. When matching a SQL template, whether the definition also contains a batch that the statement must be part of, SQL Server does allow more leeway in the definition of the batch. In particular, keyword case, whitespace, and comments will be ignored.

To make sure your plan guides use the exact text that is submitted by your applications, you can run a trace using SQL Server Profiler, and capture the SQL:BatchCompleted and RPC:Completed events. After the relevant batch (the one you want to create a plan guide for) shows up in the top window of your Profiler output, you can right-click the event and select Extract Event Data to save the SQL Text of the batch to a text file. It is not sufficient to copy and paste from the lower window in the Profiler, because the output there can introduce extra line breaks.

To verify that your plan guide was used, you can look at the XML plan for the query. If you can run the query directly, you can use the option SET SHOWPLAN_XML ON, or you can capture the showplan XML through a trace. An XML plan will have two specific items, indicating that the query used a plan guide. These items are PlanGuideDB and PlanGuideName. If the plan guide was a template plan guide, the XML plan will also have the items TemplatePlan-GuideDB and TemplatePlanGuideName.

When a query is submitted for processing, if there are any plan guides in the database at all, SQL Server will first check to see if the statement matches a SQL plan guide or OBJECT plan guide. The query string is hashed to make it faster to find any matching strings in the database’s existing plan guides. If no matching SQL or OBJECT plan guides are found, SQL Server will then check for a TEMPLATE plan guide. If it finds a TEMPLATE guide, it will then try to match the resulting parameterized query to a SQL plan guide. This gives you the possibility of applying additional hints to your queries using forced parameterization. Figure 5-2, copied from SQL Server Books Online, shows the process that SQL Server will use to check for applicable plan guides.

The key steps are the following, which follow the flowchart from the top left, take the top branch to the right, the middle branch down, and then right at the center, to the point where the statement is modified based on the plan guide and its hints.

  1. For a specific statement within the batch, SQL Server tries to match the statement to a SQL-based plan guide, whose @module_or_batch argument matches that of the incoming batch text, including any constant literal values, and whose @stmt argument also matches the statement in the batch. If this kind of plan guide exists and the match succeeds, the statement text is modified to include the query hints specified in the plan guide. The statement is then compiled using the specified hints.
  2. If a plan guide is not matched to the statement in step 1, SQL Server tries to parameterize the statement by using forced parameterization. In this step, parameterization can fail for any one of the following reasons:
    1. The statement is already parameterized or contains local variables.
    2. The PARAMETERIZATION SIMPLE database SET option is applied (the default setting), and there is no plan guide of type TEMPLATE that applies to the statement and specifies the PARAMETERIZATION FORCED query hint.
    3. A plan guide of type TEMPLATE exists that applies to the statement and specifies the PARAMETERIZATION SIMPLE query hint.

Cc293620.figure_C05621961_2(en-us,TechNet.10).jpg

Figure 5-2 Checking for applicable plan guides

Let’s look at an example that involves the distribution of data in the SpecialOfferID column in the Sales.SalesOrderDetail table in the AdventureWorks database. There are 12 different SpecialOfferID values, and most of them only occur a few hundred times at most, out of the 121,317 rows in the Sales.SalesOrderDetail, as the following script and output illustrates:

USE AdventureWorks
SELECT SpecialOfferID, COUNT(*) as Total
FROM Sales.SalesOrderDetail
GROUP BY SpecialOfferID;
RESULTS:
SpecialOfferID      Total
--------------      -----------
1           115884
2           3428
3           606
4           80
5           2
7           137
8           98
9           61
11          84
13          524
14          244
16          169

As there are 1,238 pages in the table, for most of the values a nonclustered index on Special-OfferID could be useful, so here is the code to build one:

CREATE INDEX Detail_SpecialOfferIndex ON Sales.SalesOrderDetail(SpecialOfferID);

We assume that there are very few queries that actually search for a SpecialOfferID value of 1 or 2, and 99 percent of the time the queries are looking for the less popular values. We would like the SQL Server optimizer to autoparameterize queries that access the Sales.SalesOrderDetail table, specifying one particular value for SpecialOfferID. So we will create a template plan guide to autoparameterize queries of this form:

SELECT * FROM Sales.SalesOrderDetail WHERE SpecialOfferID = 4;

However, we want to make sure that the initial parameter that determines the plan is not one of the values that might use a Clustered Index scan, namely the values one or two. So we can take the autoparameterized query produced by the sp_get_query_template procedure, and use it to first create a template plan guide, and then to create a SQL plan guide with the OPTIMIZE FOR hint. The hint will force SQL Server to assume a specific value of 4 every time the query needs to be reoptimized.

USE AdventureWorks;
-- Get plan template and create plan Guide 
DECLARE @stmt nvarchar(max); 
DECLARE @params nvarchar(max); 
EXEC sp_get_query_template
N'SELECT * FROM Sales.SalesOrderDetail WHERE SpecialOfferID = 4', 
@stmt OUTPUT, 
@params OUTPUT
--SELECT @stmt as statement -- show the value when debugging 
--SELECT @params as parameters -- show the value when debugging
EXEC sp_create_plan_guide N'Template_Plan_for SpecialOfferID',
@stmt, N'TEMPLATE', NULL, @params,
N'OPTION (PARAMETERIZATION FORCED)';
EXEC sp_create_plan_guide
@name = N'Force_Value_for_Prepared_Plan', 
@stmt = @stmt, 
@type = N'SQL', 
@module_or_batch = NULL, 
@params = @params, 
@hints = N'OPTION (OPTIMIZE FOR (@0 = 4))';
GO

You can verify that the plan is being autoparameterized, and optimized for a value that uses a nonclustered index on SpecialOfferID by running a few tests:

DBCC FREEPROCCACHE; 
SET STATISTICS IO ON;
SELECT * FROM Sales.SalesOrderDetail 
WHERE SpecialOfferID = 3; 
GO
SELECT * FROM Sales.SalesOrderDetail 
WHERE SpecialOfferID = 4; GO
SELECT * FROM Sales.SalesOrderDetail 
WHERE SpecialOfferID = 5; 
GO

You should note in the STATISTICS IO output that each execution uses a different number of reads, because it is finding a different number of rows through the nonclustered index. You can also verify that SQL Server is using the prepared plan by examining the STATISTICS XML output. If you set that option on, and run the query looking for a value of 5, you should have a node in your XML document very much like this:

<ParameterList>
<ColumnReference Column=“@0” ParameterCompiledValue=“(4)”
ParameterRuntimeValue=“(5)” /> 
</ParameterList>

Plan guides are not intended to speed up query compilation time. Not only does SQL Server first have to determine if there is a plan guide that could be a potential match for the query being compiled, but the plan enforced by the plan guide has to be one that the optimizer would have come up with on its own. In order to know that the forced plan is valid, the optimizer has to go through most of the process of optimization. The benefit of plan guides is to reduce execution time for those queries in which the SQL Server optimizer is not coming up with the best plan on its own.

< Back      Next >

 

 

© Microsoft. All Rights Reserved.