A talk about the execution plan cache in SQL Server (bottom)

Introduction

In the previous article we talked about the relationship between the query optimizer and the execution plan cache, and the conflicts between them. In this article, we will focus on the common issues of execution plan caching and some workarounds.

processes that take into account the execution of the cache

The process of parsing statements in the query optimizer is mentioned in the previous article, and when the plan cache is taken into account, you first need to see if the cache of statements already exists in the plan cache, and if not, the compilation process will be executed directly using the compiled execution plan. Therefore, the complete procedure is shown in 1.

Figure 1: Process for 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 for the planned process. So it is not difficult to guess, as long as this kind of search, must not run hash (hash) data structure. Some information about the hash table can be found by sys.dm_os_memory_cache_hash_tables this DMV, as shown in 2. It is worth noting that when too many execution plans cause the hash object to be too large in the same bucket, additional buckets are required, which can result in inefficient lookup plan caches. The workaround is to minimize the number of plans in the plan cache, which we will discuss later in this article.

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

When such problems arise, we can see the problem in the Buckets_avg_scan_miss_length column. Such cases are high in cache hit ratio (SQL Server:plan cache-cache hits Ratio), but can be considered as objects when the compilation time is too long.

parametric and non-parametric

The only identifier for a query plan is the query statement itself, but assuming that the body of the statement is the same as the query condition predicate, is the 1 execution plan or two execution plan counted in the execution plan? It ' s Depends.

Suppose the following two statements, 3, are shown.

Figure 3: Two statements with only a different predicate condition

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

Figure 4. The same statement, different conditions, has 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 solution to this problem is to make the query statements in the execution plan cache identical.

Parameterization of

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

Figure 5. Database-Level options

At this point we will execute the two statements in Figure 3, through the query execution plan cache, we find that the variable part is parameterized, so that the statements in the plan cache are consistent, 6, and can be reused.

Figure 6: Query statement after a parameter session

However, forcing parameters can cause problems, and the query optimizer is often unable to optimize some specific queries based on statistics, such as the inability to apply some indexes or the scan. The negative effects that have been made in the previous article have been stated, and this is not explained here.

Therefore 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 in a query is higher than the cost of compiling, using the RECOMPILE option in a stored procedure or using the RECOMPILE hint in an ad hoc query causes the execution plan to be rebuilt for each query, which causes the resulting execution plan not to be inserted into the execution plan cache. For OLAP class queries, the cost of an inaccurate execution plan tends to be much higher than the compilation cost, so consider this parameter or option, which you can use hint as shown in the query in Listing 1.

SELECT * from Sales.Customer

WHERE customerid>20000 and TerritoryID = 4

OPTION (RECOMPILE)

Code Listing 1. Using recompile

Except we can manually prompt for SQL Server recompilation, SQL Server will also automatically recompile under the following conditions:

• Metadata changes, such as indicating changes, deleting columns, changing data types, and so on.
• Statistical information changes.
• The set parameter changes of the connection, the values of SET ANSI_NULLS, and so on, can cause the cached execution plan not to be reused, thus recompiling. That's why we see the statements in the cached execution plan exactly the same, but they're not used, and we need the relevant parameters to be consistent, and these parameters can be viewed through sys.dm_exec_plan_attributes.

Using the Optimize for parameter

The recompile method provides a rhythm that does not use the plan cache at all. But sometimes, the execution plan of the attribute predicate is used more often, for example, if only those predicate conditions produce a large number of parameter compilations that return a result set, we can consider the optimize for parameter. 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

OPTION (OPTIMIZE for (@vari =4))

Code Listing 2. Using Optimize for hints

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

Additionally, a optimize for unknown parameter has been introduced since SQL Server 2008, which makes it possible to probe the value of the local parameter as a predicate condition while optimizing the query, rather than probing the statistics based on the initial values of the local variables.

Using local variables instead of stored procedure parameters in stored procedures

Instead of using procedure parameters in a stored procedure, using local variables is equivalent to disabling parameter sniffing directly. 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, forcing Query Analyzer to use the average of the condition column to estimate.

Although this approach makes parameter estimation very inaccurate, it becomes very stable, after all, the statistics do not change too often. The way is not recommended, if possible, use optimizer as much as possible.

Code Listing 3 shows this way.

CREATE PROC Testforlocalvari

@v INT

As

DECLARE @vari INT

SET @[email protected]

SELECT * from Sales.Customer

WHERE customerid>20000 and TerritoryID = @vari

Code Listing 3. Directly referencing local variables instead of stored procedure parameters

Forced parameterization

The mandatory parameterization has been mentioned earlier in this article and is not mentioned here.

Use Plan guidance

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

The plan guide allows the query statement to be thrown into SQL Server by the client application, and SQL Server adds hints or options to it, such as listing 4 to see an example of a plan guide.

EXEC sp_create_plan_guide N ' MyPlanGuide1 ',  

@type =n ' SQL ',  

@params =n ' @vari int ',

@hints =n ' OPTION (RECOMPILE)

Code Listing 4. Set up a plan guide for the queries we have earlier

When the program guidance is joined, when the batch arrives at SQL Server, it will also find out if there is a plan guide and match it when it finds the matching plan cache. If it matches, the prompts or options in the Plan guide are applied. It is important to note here that the @stmt parameter must be exactly the same as the one in the query statement, and a space difference will be considered mismatched.

PARAMETERIZATION Simple

When we enable forced parameterization at the database level, we do not want to enable forced 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. Applying simple parameterization to a single statement through a plan guide

Summary

The execution plan cache wants to reuse the execution plan as much as possible, which reduces the CPU consumed by the compilation and the memory used to execute the cache. The query optimizer wants to generate a more accurate execution plan, which is bound to result in a large number of execution plans, which will not only lead to a lot of CPU depletion, but also memory pressure, even if the execution plan cache is too much more than the bucket limit, the steps in the cache to match the execution plan will also consume more time.

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

A talk about the execution plan cache in SQL Server (bottom)