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

Brief introduction

The nature of the SQL statement we write is simply the logic to get the data, not the physical path to get the data. When we write SQL statements to SQL Server, Query Analyzer parses statements sequentially (Parse), bindings (BIND), Query optimizations (optimization, sometimes referred to as simplification), execution (Execution). In addition to the execution steps, the first three steps are followed by the execution plan, where SQL Server obtains physical data in accordance with the plan, and the final execution steps execute the query against the execution plan to obtain results. However, the query optimizer is not the focus of this article, and this article focuses on the mechanisms and frequently asked questions that the query optimizer has to cache execution plans after it generates execution plans.

Why do I need to execute a plan cache

As we know from the introduction, the process steps that generate the execution plan are a large proportion of the CPU and memory resources consumed. In fact, the query optimizer generates an execution plan for more work, probably divided into 3 parts:

First, the table name, stored procedure name, view name, and so on, are parsed according to the incoming query statement text. The tree that represents the query text is then generated based on the logical data operation.

The second step is to optimize and simplify, for example, to convert a subquery to a peer-to-peer connection, prioritize filtering conditions, delete unnecessary connections (for example, indexed, may not need to refer to the original table), and so on.

The third step is to conduct a cost based (cost-based) assessment based on the statistical information in the database.

The above three steps are completed before multiple candidate execution plans are generated. Although our SQL statement logically has only one, but the sequence of physical acquisition data that conforms to this logical sequence can be many, for example, you want from Beijing to Shanghai, that is, you can do high-speed rail, you can do aircraft, but from Beijing to Shanghai This description is a logical description of how to implement the path there are many. So let's look at an example in SQL Server, like the query in Listing 1.

SELECT * from 
A INNER join B on a.a=b.b
INNER join C on C.C=A.A

Code Listing 1.

For the query, whether a inner join B or B first inner join C, the result is the same, so multiple execution plans can be generated, but one basic principle is that SQL Server does not necessarily choose the best execution plan, but rather a good enough plan. This is because the cost of evaluating all the execution plans should not be too large. Ultimately, SQL Server evaluates the cost of the execution plan based on the cardinality of the data and the cost of the CPU and IO it consumes each step, so the choice of execution plan is heavily dependent on statistics, and I will not elaborate on the relevant content of the statistics.

It is easy to see how the resource cost of this step can be staggering for the previous Query Analyzer's process of generating execution plans. Therefore, when the same query is executed once, caching it will greatly reduce the compilation of the execution plan, thereby increasing efficiency, which is the intention of the execution plan cache existence.

The object cached by the execution plan

The execution plan caches objects that are grouped into 4 categories, respectively:

Compiled plan: The relationship between the compiled execution plan and the execution plan is the same as that of MSIL and C #.

Execution context: When a compiled plan is executed, there is a contextual environment. Because the compiled plan can be shared by multiple users, the query needs to store set information and the value of the local variable, so the context environment needs to be associated with the execution plan. The execution context is also known as executable plan.

Cursors: The stored cursor State is similar to the relationship between the execution context and the compiled plan. The cursor itself is used only by a connection, but the execution plan associated with the cursor can be shared by multiple users.

Algebraic tree: An algebraic tree (also known as a parse tree) represents the query text. As we have said before, Query Analyzer does not refer directly to query text, but to algebraic trees. Here you may have questions, the algebra tree is used to generate execution plans, and this also caches the algebraic trunk hairs? This is because views, Default, constraints can be reused by different queries, and caching the algebraic trees of these objects eliminates the parsing process.

For example, we can find the cached execution plan by Dm_exec_cached_plans this DMV, as shown in Figure 1.

Figure 1. Cached Execution Plan

So what do you think of the memory-related information that these types of object caches occupy? We can see by dm_os_memory_cache_counters this DMV that the above categories of cached objects are shown in Figure 2.

Figure 2 Memory consumed by these types of object caches in memory

In addition, the execution plan cache is a cache. The objects in the cache are replaced by the algorithm. For execution plan caching, the replaced algorithm is primarily based on memory pressure. And the memory pressure can be divided into two kinds, both internal and external pressure. External pressure is due to the buffer pool's free space down to a certain threshold value depending on the size of the physical memory, or maximum memory if the maximum memory is set. Internal pressure is due to an object exceeding a threshold in the execution plan cache, such as 32-bit SQL Server, which has a threshold of 40000, and 64-bit that is elevated to 160000.

The key point here is that the cached identifier is the query itself, so the select * from Schemaname.tablename and select * FROM TableName, while the results are consistent, require caching of two execution plans, so a best Practice refers to the name of the table and other objects, with the schema name on it.
To tune a statement based on a cached execution plan

The cached execution plan contains a wealth of content, not only cached execution plans, statements, but also statistics for cached execution plans, such as CPU usage, latency, and so on. But here it is worth noting that the statistics here count only the execution time, not the compile time. For example, we can use the code in Listing 2 to find the longest 20 query statements in the database based on the cached execution plan.

SET TRANSACTION Isolation Level READ uncommitted SELECT top CAST (qs.total_elapsed_time/1000000.0 as DECIMAL (28, 2) 
                As [total Duration (s)], CAST (Qs.total_worker_time * 100.0/qs.total_elapsed_time As DECIMAL (2)) as [% CPU], CAST ((qs.total_elapsed_time-qs.total_worker_time) * 100.0/qs.total_elapsed_ti Me as DECIMAL (2)) as [% waiting], Qs.execution_count, CAST (Qs.total_elapsed_time/1000000.0/qs.execution_coun T as DECIMAL (2)) as [Average Duration (s)], SUBSTRING (Qt.text, (QS.STATEMENT_START_OFFSET/2) + 1, (C 
   ASE when qs.statement_end_offset =-1 THEN LEN (CONVERT (NVARCHAR (MAX), Qt.text)) * 2 ELSE Qs.statement_end_offset End-qs.statement_start_offset)/2) + 1) As [individual query, qt.text as [Parent Query], db_name (qt.dbid) as Dat  Abasename, Qp.query_plan from sys.dm_exec_query_stats Qs CROSS APPLY sys.dm_exec_sql_text (qs.sql_handle) as Qt CROSS APPLY Sys.dm_exec_query_plan (qs.plan_handle) qp WHERE qs.total_elapsed_time > 0 ORDER by Qs.total_elapsed_time DESC 

Code Listing 2. Finds the database with the most time-consuming 20 query statements by executing the plan cache

The above statement allows you to modify the order by to find the statement you wish to find based on different conditions, which is no longer discussed here.

This approach has some advantages over whether it is a service-side trace or a client, and it is much simpler to find time-consuming query statements by capturing Trace Profiler, which is time-consuming and costly to the server. However, this statistic is based only on the last instance reboot or after the DBCC FREEPROCCACHE has not been run. But there are some drawbacks to this approach, such as:

Statements such as index rebuilding and update statistics are not cached, and these statements can be very expensive.
The cache may be replaced at any time, so the method cannot see statements that are no longer cached.
This statistic only sees the execution cost and cannot see the compilation cost.
The absence of a parameterized cache may render a different execution plan for the same statement, so that there are different caches, in which case the statistics are not cumulative and may not be very accurate.

Inconsistencies between the execution plan cache and the query optimizer

Remember what we said before, the compilation and selection of the execution plan is divided into three steps, the first two steps, depending on the metadata of objects such as query statements and tables, are heavily dependent on statistics at the stage of the execution plan selection, so that the same statement is just a different argument, and the query optimizer produces a different execution plan. For example, let's look at a simple example, as shown in Figure 3.

Figure 3. The query optimizer chooses a different execution plan just because of different parameters

You may think that this is not very good, according to the parameters of different implementation plan. So let's consider one more question: If the above query is placed in a stored procedure, the parameters cannot be sniffed directly, and the second execution will reuse the first execution plan when the first execution plan is cached. Although the compilation time is removed, the cost of a bad execution plan is higher! Let's take a look at this example, as shown in Figure 4.

Fig. 4. Different parameters, but the exact same implementation plan!

Let's look at the same example and turn the execution order upside down, as shown in Figure 5.

Figure 5. The implementation plan has changed completely.

We see that the second execution of the statement, fully reused the first execution plan. There's always a query to be sacrificed. For example, when the parameter is 4, there will be more than 5,000, the index scan should be the most effective, but figure 4 has reused the previous execution plan, using more than 5,000 to find!!! This is undoubtedly inefficient. And this situation can be very confusing to the DBA, because the execution plan in the cache is not controllable, the objects in the cache can be deleted at any time, and the performance problems that occur after the execution of who executes first are often a headache for the DBA.

As we can see from this example, the query optimizer wants to choose an efficient execution plan as much as possible, while the execution plan cache wants to reuse the cache as much as possible, and the two mechanisms conflict in some cases.

In the next article, we will continue to look at common problems and solutions arising from the execution of plan caching and Query Analyzer conflicts, as well as the compilation of execution plans.

Summary

In this article, we briefly describe the process by which the query optimizer generates execution plans and the mechanism for executing the plan cache. When the query optimizer and the execution plan cache meet in a bad situation, some problems arise. In the next article, we will continue to explore the execution plan cache in SQL Server.

The above is a small part of the description of the SQL Server in the implementation of the plan cache (on) The full description, I hope you like.