Talk about the execution plan cache (on) in SQL Server)

Introduction

The SQL statements we usually write are essentially the logic for obtaining data, rather than the physical path for obtaining data. When the SQL statements we write are passed to the SQL Server, the query analyzer parses, binds, and optimizes the statements in sequence), Execution (Execution ). In addition to the execution steps, the execution plan is generated after the first three steps, that is, the SQL Server obtains physical data according to the plan, and finally the execution steps are executed according to the execution plan to obtain the results. However, the query optimizer is not the focus of this article. This article mainly describes the mechanism and common problems related to the cache execution plan after the query optimizer generates the execution plan.

 

Why is execution plan cache required?

From the introduction, we know that the process steps used to generate the execution plan occupy a large proportion, which will consume various CPU and memory resources. In fact, the query optimizer generates an execution plan to do more, which is divided into three parts:

• First, the table name, stored procedure name, and view name are parsed based on the input query statement text. Then, a tree representing the query text is generated based on logical data operations.
• The second step is optimization and simplification. For example, the subquery is converted to a pair of connections, the filtering conditions are prioritized, and unnecessary connections are deleted (for example, the original table may not need to be referenced if an index exists).
• Step 3: perform Cost-based evaluation based on the statistical information in the database.

 

After the preceding three steps are completed, multiple candidate execution plans are generated. Although our SQL statement has only one logic, there can be multiple physical data retrieval orders in this logical order. For example, you want to go from Beijing to Shanghai, that is, high-speed trains or planes can be used, but the descriptions from Beijing to Shanghai are logical descriptions. There are multiple implementation paths. Let's look at an example in SQL Server, such as the query in code list 1.

   1: SELECT * 

   2: FROM A INNER JOIN B ON a.a=b.b

   3: INNER JOIN C ON c.c=a.a

Code List 1.

 

For this query, no matter whether A first joins B or B first joins C, the results are the same. Therefore, multiple execution plans can be generated, however, the basic principle is that SQL Server does not necessarily select the best execution plan, but selects a good plan. This is because the cost of evaluating all execution plans should not be too high. In the end, SQL Server evaluates the cost of the execution plan based on the data base and the CPU and IO costs consumed by each step. Therefore, the selection of the execution plan is heavily dependent on the statistics, I will not elaborate on the statistics.

The previous process of querying analyzer to generate an execution plan is not difficult to see, the resource cost consumed by this step is also amazing. Therefore, when the same query is executed once, caching it will greatly reduce the compilation of the execution plan, thus improving the efficiency. This is the original intention of the Execution Plan cache.

 

Objects cached in the execution plan

The objects cached in the execution plan are divided into four categories:

• Compiled plan: the relationship between the compiled execution plan and execution plan is the same as that between MSIL and C.
• Execution context: There is a context environment when executing the compiled plan. Because the compiled plan can be shared by multiple users, but the Query Needs to store the SET information and the value of local variables, the context environment needs to be associated with the execution plan. The execution context is also called Executable Plan.
• Cursor: the stored cursor state is similar to the relationship between execution context and compilation plan. The cursor itself can only be used by a connection, but the execution plan associated with the cursor can be shared by multiple users.
• Algebra tree: an algebra tree (also known as the parsing tree) represents the query text. As we mentioned earlier, the query analyzer does not directly reference the query text, but rather an algebra tree. Here you may have doubts that the algebra tree is used to generate the execution plan, and the algebra trunk hair is also cached here? This is because views, defaults, and constraints may be used repeatedly by Different queries, and the algebra tree of these objects is cached to save the parsing process.

 

For example, you can use the DMV dm_exec_cached_plans to find the cached execution plan, as shown in 1.

Figure 1. cached execution plan

 

So what about the memory used by these types of object cache? We can see through the DMV dm_ OS _memory_cache_counters that the above several types of cached objects are shown in 2.

Figure 2. Memory occupied by these types of object cache in memory

 

In addition, the execution plan cache is a kind of cache. Objects in the cache will be replaced according to the algorithm. For Execution Plan caching, the replaced algorithm is mainly based on memory pressure. Memory pressure is divided into two types: internal pressure and external pressure. The external pressure is caused by the reduction of the available space of the Buffer Pool to a certain critical value (the critical value varies depending on the size of the physical memory. If the maximum memory is set, it depends on the maximum memory ). The internal pressure is because the objects in the execution plan cache exceed a certain threshold. For example, the 32-bit SQL Server has a threshold of 40000, And the 64-bit SQL Server has a threshold of 160000.

The cached identifier is the query statement, so select * from SchemaName. although TableName and Select * from TableName have the same effect, they need to cache two execution plans. Therefore, a Best Practice should include the schema name When referencing the table name and other object names.

Optimize statements based on the cached execution plan

Cached execution plans are rich in content, including not only the cached execution plans and statements, but also the statistics of cached execution plans, for example, CPU usage and waiting time. However, it is worth noting that the statistics here only count on the execution time, not the Compilation Time. For example, you can use the code in code list 2 to find the 20 longest query statements in the database based on the cached execution plan.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SELECT TOP 20 

  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(28, 2)) AS [% CPU] 

  , CAST((qs.total_elapsed_time - qs.total_worker_time)* 100.0 / 

        qs.total_elapsed_time AS DECIMAL(28, 2)) AS [% Waiting] 

  , qs.execution_count 

  , CAST(qs.total_elapsed_time / 1000000.0 / qs.execution_count 

                AS DECIMAL(28, 2)) AS [Average Duration (s)] 

  , SUBSTRING (qt.text,(qs.statement_start_offset/2) + 1,      

    ((CASE 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 DatabaseName 

  , 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 List 2. Find the 20 query statements with the longest total database time using the execution plan Cache

 

In the preceding statement, you can modify Order By to find the statement you want based on different conditions.

Compared with Trace on the server or Profiler on the client, this method has some advantages. If Trace capture is used for analysis, it is not only time-consuming and laborious, but also brings additional overhead to the server, using this method to find time-consuming query statements is much simpler. However, this statistics is only based on the previous instance restart or after DBCC FreeProcCache is not run. However, this method also has some drawbacks, such:

• Statements such as index reconstruction and Statistics Update are not cached, and the cost of these statements is very high.
• The cache may be replaced at any time, so this method cannot see statements that are no longer cached.
• The statistics only show the execution cost and the compilation cost.
• A cache without parameterization may present different execution plans for the same statement, so different caches may appear. In this case, the statistical information cannot be accumulated, which may result in inaccurate results.

 

Conflict between execution plan cache and Query Optimizer

Do you remember what we said before? The compilation and selection of execution plans are divided into three steps. The first two steps are only based on the metadata of the query statement, table, and other objects, in the stage of Execution Plan Selection, statistics are heavily dependent. Therefore, the query optimizer generates different execution plans when the same statement is just different parameters, for example, let's look at a simple example, as shown in 3.

Figure 3. the query optimizer selects different execution plans only because of different parameters

 

You may think that this is not quite good. Different execution plans are generated based on parameters. Let's consider another question. If we put the above query in a stored procedure, the parameter cannot be sniffed directly. When the first execution plan is cached, the second execution will reuse the first execution plan! Compilation Time is not required, but the cost of poor execution plans is higher! Let's take a look at this example, as shown in 4.

Figure 4. Different parameters are the same execution plan!

 

Let's take a look at the same example. After reversing the execution order, we will see Figure 5.

Figure 5. The execution plan has completely changed

 

We can see that the statement for the second execution completely reused the first execution plan. There will always be a query sacrifice. For example, if the parameter is 4, there will be more than 5000 entries. At this time, index scanning should be the most efficient, but figure 4 reused the previous execution plan and used more than 5000 searches !!! This is undoubtedly inefficient. In addition, this situation may confuse DBAs, because the execution plan in the cache is uncontrollable and the objects in the cache may be deleted at any time, the performance problems caused by the execution of the first executor may also cause DBA headaches.

From this example, we can see that the query optimizer wants to select an efficient execution plan as much as possible, while the execution plan cache wants to reuse the cache as much as possible. These two mechanisms may conflict in some situations.

In the next article, we will continue to look at the conflicts between the execution plan cache and the query analyzer, as well as the common problems and solutions brought about by compiling the execution plan.

 

Summary

In this article, we briefly describe the process in which the query optimizer generates the execution plan and the Execution Plan cache mechanism. When the query optimizer and execution plan cache converge in some bad situations, some problems will occur. In the next article, we will continue to explore the execution plan cache in SQL Server.