SQL Server execution plan cache (below)

Introduction

In the previous article, we talked about the relationship between the query optimizer and the Execution Plan cache, and the conflicts between the two. In this article, we will mainly discuss the common problems of execution plan caching and some solutions.

 

Process for taking execution cache into account

The previous article mentioned the process of parsing statements by the query optimizer. when taking into account the plan cache, you must first check whether the plan cache contains the statement cache. If not, will execute the compilation process, if there is, directly use the compiled execution plan. Therefore, the complete process 1 is shown.

Figure 1. process of taking plan cache into account

 

As shown in figure 1, we can see that one step is to find the plan process in the cache. Therefore, it is difficult to guess that as long as this type of search is used, the Hash data structure cannot be run. You can find information about the Hash table through the DMV sys. dm_ OS _memory_cache_hash_tables, as shown in figure 2. It is worth noting that when there are too many execution plans that lead to too many objects in the Same Bucket after the hash, additional buckets are required, which may lead to low cache efficiency of the search plan. The solution is to minimize the number of plans in the Plan cache, which will be discussed later in this article.

Figure 2. HashTable information about the storage plan Cache

 

When this problem occurs, we can see the problem in the buckets_avg_scan_miss_length column. In this case, the Cache Hit rate (SQL Server: Plan Cache-Cache Hit Ratio) is relatively high, but the compilation time is too long.

 

Parameterization and non-parameterization

The only identifier of a query plan is the query statement, but assuming that the statement body is the same, but only the query condition predicates are different, is one execution plan or two execution plans in the execution plan? It's Depends.

Assume the following two statements, as shown in 3.

Figure 3. Only two statements with different predicates

 

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

Figure 4. cache of two different execution plans for the same statement and different conditions

 

We know that the execution plan cache depends on the query statement itself to identify the cache. Therefore, the preceding two statements are considered as two different statements in the execution plan cache. The solution to this problem is to make the query statements in the execution plan cache identical.

 

Parameterization

So that only some parameters are different, and the same query statement can be reused, It is the significance of parameterization. For example, if the statements in Figure 3 enable forced database parameterization or use stored procedures. SQL Server enforces parameters for these statements. For example, we modified the database-level options according to Figure 5.

Figure 5. Database-level options

 

Now let's execute the two statements in Figure 3. by querying the execution plan cache, we find that the variable is parameterized, so that 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

 

However, mandatory parameters may cause some problems. The query optimizer often cannot optimize some specific queries based on statistical information. For example, it cannot apply some indexes or search during the scan. The negative effects have been mentioned in the previous article. I will not elaborate on them here.

Therefore, there are several solutions to the above problems.

 

Balanced parameterization and non-parameterization

Under specific circumstances, parameterization is sometimes good, but sometimes it is the culprit of performance problems. Let's look at several ways to balance the relationship between the two.

 

Use RECOMPILE

When the cost of inaccurate execution plans is higher than that of compilation during queries, if you use the RECOMPILE option in the stored procedure or use the RECOMPILE prompt in the ad hoc query, the execution plan will be regenerated for each query, this parameter prevents the generated execution plan from being inserted into the execution plan cache. For OLAP queries, inaccurate execution plans often cost much more than compilation costs. Therefore, you can consider this parameter or option, you can use Hint as shown in the query in code list 1.

SELECT * FROM Sales.Customer

WHERE CustomerID>20000 AND TerritoryID = 4

OPTION (recompile)

Code List 1. Use Recompile

 

In addition to manual prompts for SQL Server re-compilation, SQL Server will automatically re-compile under the following conditions:

• Metadata change, for example, indicating that the name is changed, the column is deleted, and the data type is changed.
• Statistics changes.
• When the SET parameter of the connection changes, the SET ANSI_NULLS and other values are different, the cache execution plan cannot be reused and re-compiled. This is why we can see that the statements in the cache execution plan are identical, but they are not reused. We also need to have the same related parameters. These parameters can be viewed through sys. dm_exec_plan_attributes.

 

Use the Optimize For Parameter

The RECOMPILE method allows you to skip the cache schedule. However, sometimes the execution plan of the characteristic predicates is used more times. For example, if only those predicates generate a large number of returned result sets For parameter compilation, we can consider the Optimize For parameter. For example, let's look at code list 2.

DECLARE @vari INT

SET @vari=4

SELECT * FROM Sales.Customer

WHERE CustomerID>20000 AND TerritoryID = @vari

OPTION (OPTIMIZE FOR (@vari=4))

Code List 2. Use the optimize for prompt

 

When this parameter is used, the cache execution plan is generated and cached according to the predicates following optimize for. This may also cause inefficient queries that are not included in this parameter, however, this parameter is selected, so we usually know which predicates will be used more.

 

In addition, an optimize for unknown parameter is added since SQL Server 2008, which allows you to detect the value of a local parameter as a predicate condition during the optimization Query Process, instead of testing statistics based on the initial values of local variables.

 

Use local variables instead of stored procedure parameters in Stored Procedures

If you do not use process parameters in a stored procedure, using local variables is equivalent to directly disabling parameter sniffing. After all, the value of a local variable can only be known at runtime, and cannot be known when the execution plan is compiled by the query optimizer, therefore, the query analyzer is forced to use the average value of the condition column for estimation.

Although this method makes the parameter estimation very inaccurate, it will become very stable. After all, the statistical information will not change too frequently. This method is not recommended. If possible, use the Optimizer method as much as possible.

Code listing 3 shows this method.

CREATE PROC TestForLocalVari

@v INT

AS

DECLARE @vari INT

SET @vari=@v

SELECT * FROM Sales.Customer

WHERE CustomerID>20000 AND TerritoryID = @vari

Code List 3. Directly reference local variables instead of stored procedure parameters

Forced parameterization

We have mentioned forced parameterization before this article, so we will not mention it here.

 

Usage plan guide

In some cases, our environment does not allow us to directly modify SQL statements. For example, we do not want to break the logic of the Code or the application is developed by a third party, therefore, it is unrealistic to add HINT or parameters. In this case, we can use the plan guidance.

Plan guidance allows SQL Server to add a prompt or option to the query statement when the client application is thrown to SQL Server. For example, you can see an example of plan guidance in code list 4.

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 list 4. Guidance on the preceding query Setting Plan

 

When the plan guidance is added, when the batch processing arrives at SQL Server, the Plan cache will also find whether the plan guidance matches the plan guidance. If yes, a prompt or option appears in the application plan guide. Note that the @ stmt parameter must be exactly the same as the one in the query statement. A space difference is considered mismatched.

 

PARAMETERIZATION SIMPLE

When forced PARAMETERIZATION is enabled at the database level, we do not want to enable forced PARAMETERIZATION for specific statements. 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 List 5. apply simple parameterization to a single statement through the scheduler Guide

 

Summary

The execution plan cache should reuse the execution plan as much as possible, which will reduce the CPU consumption for compilation and the memory consumed by the execution cache. The query optimizer tries its best to generate more precise execution plans, which will inevitably lead to a large number of execution plans. This may not only cause heavy CPU consumption for recompilation, but also cause memory pressure, even if the execution plan is cached too much than the BUCKET limit, it takes more time to match the execution plan in the cache.

Therefore, it is very important to use the methods described in this article to balance the relationship between the two based on actual conditions.