An analysis of execution plan caching in SQL Server (bottom) _mssql

In the previous article to introduce you to SQL Server execution plan cache (above), this article continues to introduce SQL Server implementation plan cache related knowledge, small partners to study together.

Brief introduction

In the previous article we talked about the relationship between the query optimizer and the execution plan cache, and the conflict between the two. In this article, we will focus on common problems with execution plan caching and some solutions.

Process to take into account the execution of caching

The process of parsing statements in the query optimizer is mentioned in the previous article, and when you take into account the plan cache, you first need to see if the cache of statements is already in the plan cache, and if not, the compilation process is performed, and if it exists, the compiled execution plan is used directly. Therefore, the complete process is shown in Figure 1.

Figure 1. The process of taking the plan cache into account

As we can see in Figure 1, there is one step that needs to be found in the cache to find the planned process. Therefore, it is not difficult to guess, as long as this kind of lookup, must not run hash (hash) of the data structure. By sys.dm_os_memory_cache_hash_tables This DMV can find some information about the hash table, as shown in Figure 2. It is worth noting that, when too many execution plans result in the hashing object being overloaded with the same bucket, additional bucket is required, which may cause the lookup plan cache to be inefficient. The solution is to minimize the number of plans in the plan cache, which we'll discuss later in this article.

Figure 2. Information about the hashtable of the storage plan cache

When this type of problem arises, we can see the problem in the Buckets_avg_scan_miss_length column. This type of situation is higher in cache hit rates (SQL Server:plan cache-cache Hit Ratio), but can be considered as an object when the compilation time is too long.

Parameterization and non-parametric

The unique identification of the query plan is the query itself, but assuming that the statement's body is the same as the query conditional predicate, does it count as 1 execution plans or two execution plans in the execution plan? It ' s depends.

Suppose the following two statements, as shown in Figure 3.

Figure 3. Only two statements with different predicate conditions

Although the execution plan is the same, two execution plans are retained in the execution plan cache, as shown in Figure 4.

Figure 4. The same statement, different conditions, two different execution plan caches

We know that the execution plan cache relies on the query statement itself to discriminate the cache, so the above two statements are treated as two different statements in the execution plan cache. The way to solve the problem is to make the query statement in the execution plan cache exactly the same.

Parameterized

So that only some of the parameters are different, and the query itself the same statement can be reused, is the meaning of parameterization. For example, the statement in Figure 3, if we enable the mandatory parameterization of the database, or the use of stored procedures. SQL Server enforces the arguments for these statements, such as the option to modify the database hierarchy based on Figure 5.

Figure 5. Database-Level options

At this point we are going to execute the two statements in Figure 3, and through the query execution plan cache, we find that the variables are parameterized and the statements in the plan cache become consistent, as shown in Figure 6, so that they can be reused.

Figure 6. Query statement after parameter words

However, forcing parameters can cause problems, and the query optimizer often fails to optimize some specific queries based on statistics, such as the inability to apply some indexes or the time of the scan to find them. The negative effects have been mentioned in the previous article, and there is no detail here.

So there are several solutions to the above problems.

Balanced parameterization and non-parametric

In specific cases, parameterization is sometimes good, but sometimes it is the culprit of performance problems, and here we look at several ways to balance the relationship between the two.

Using recompile

When the cost of an inaccurate execution plan is higher than the cost of the compilation, the RECOMPILE option is used in the stored procedure or the RECOMPILE hint is used in an ad hoc query so that each query rebuilds the execution plan, which causes the resulting execution plan not to be inserted into the execution plan cache. For OLAP class queries, inaccurate execution plans tend to cost more than compilation costs, so you can consider this parameter or option, and you can use hint as shown in the query in Code Listing 1.

SELECT * from Sales.Customer
WHERE customerid>20000 and TerritoryID = 4
OPTION (RECOMPILE)

Code Listing 1. Using recompile

Except that we can manually prompt SQL Server to recompile, SQL Server will automatically recompile under the following conditions:

Meta-data changes, such as indicating changes, deleting columns, changing data types, and so on.
Statistical information changes.

The set parameters of the connection change, the values of SET ANSI_NULLS, and so on, can cause the cached execution plan to be reused and thus recompile. That's why we see that the statements in the cached execution plan are exactly the same, but they are no longer needed, and the relevant parameters are consistent, which can be viewed through sys.dm_exec_plan_attributes.

Using the Optimize for parameter

The recompile approach provides a rhythm that does not use the plan cache at all. Sometimes, however, the execution plan of the attribute predicate is used more frequently, for example, if only those predicate conditions produce a large number of parameter compilations that return a result set, we can consider optimize for arguments. For example, let's look at Code Listing 2.

DECLARE @vari INT
SET @vari =4
SELECT * from Sales.Customer
WHERE customerid>20000 and TerritoryID = @vari C4/>option (OPTIMIZE for (@vari =4))

Code Listing 2. Using the OPTIMIZE for hint

Using this parameter causes the cached execution plan to generate and cache the execution plan according to the predicate condition after optimize for, which may also cause queries that are not in the parameter to be inefficient, but this parameter is our choice, so we usually know which predicate conditions will be used more often.

In addition, a optimize for unknown parameter has been added since SQL Server 2008, which makes it possible to probe the values of local parameters as predicate conditions in the process of optimizing queries, rather than probing statistics based on the initial values of local variables.

Using local variables instead of stored procedure parameters in stored procedures

Instead of using process parameters in stored procedures, using local variables is equivalent to directly disabling parameter sniffing. After all, the value of a local variable is only known at run time and is not known when the execution plan is compiled by the query optimizer, so the query parser is forced to estimate using the average of the conditional column.

Although this method makes the parameter estimates very inaccurate, it becomes very stable, after all, the statistics do not change too often. This approach is not recommended, and if possible, try to use the optimizer approach.

Code Listing 3 shows this approach.

CREATE PROC testforlocalvari
@vv int
as
DECLARE @vari int
SET @vari = @vv
SELECT * FROM Sales.Customer
WHERE customerid>20000 and TerritoryID = @vari

Code Listing 3. Direct reference to local variables rather than stored procedure parameters

Force parameterization

Mandatory parameterization is mentioned earlier in this article and is not mentioned here.

Use Plan guidance

In some cases, our environment does not allow us to directly modify SQL statements, such as the logic of not wanting to break the code or the application being developed by a third party, so whether adding hint or parameters is unrealistic. At this point we can use the Planning Guide.

Planning guidance enables SQL Server to add hints or options to SQL Server when it is thrown by a client application, such as Listing 4, where you can see an example of a plan guide.

EXEC sp_create_plan_guide N ' MyPlanGuide1 ',
@stmt =n ' SELECT * from Sales.Customer WHERE customerid>20000 and Territoryid= @vari ',
@type =n ' sql ',
@module_or_batch =null,
@params =n ' @vari int ',
@hints = N ' OPTION (RECOMPILE) '

Code Listing 4. Planning guidelines for our previous query setup

When the scheduling guide is joined, when the batch arrives in SQL Server, it also looks for a matching plan cache to see if there is a plan guide to match it. If it matches, the prompts or options in the Plan guide are applied. Note here that the @stmt parameter must be identical to the one in the query statement, and a space will be considered mismatched.

Parameterization Simple

When forced parameterization is enabled at the database level, we do not want to enable mandatory parameterization for specific statements, and we can use the parameterization simple option, as shown in Listing 5.

DECLARE @stmt NVARCHAR (max)
DECLARE @params NVARCHAR (max)
EXEC sp_get_query_template N ' SELECT * from Sales.Customer WHERE customerid>20000 and territoryid=2 ',
@stmt output, @params output
PRINT @stmt
PRINT @params
EXEC sp_create_plan_guide n ' mytemplateplanguide ', @stmt, n ' TEMPLATE ', NULL,
 @params, n ' OPTION ( parameterization simple) '

Code Listing 5. Apply simple parameterization to a single statement by planning Guide

Summary

Execution plan caching wants to reuse the execution plan as much as possible, which reduces the amount of memory consumed by compiling the CPU and executing the cache. And the query optimizer wants to produce more accurate execution plans as much as possible, this is bound to result in a large number of execution plans, which not only may cause the recompilation of a large amount of CPU consumption, but also memory pressure, and even when the execution plan cache exceeds the bucket limit, the steps to match the execution plan in the cache will consume more time.

Therefore, it becomes very important to use the method described in this article to balance the relationship between the two, based on the actual situation.