SQL Server execution plan cache (1)

SQL Server execution plan cache (1)

Overview

Understanding the execution plan is very important for database performance analysis. It involves statement Performance Analysis and storage. This is also the purpose of writing this article. Before learning about the execution plan, you must first understand some basic knowledge, therefore, some concepts mentioned earlier in this article will be boring to learn, but these basic knowledge is very important.

Basic Concepts

SQL Server has a memory pool used to store execution plans and data buffers. The percentage allocated to the execution plan or data buffer in the pool dynamically fluctuates with the system status. The part of the memory pool used to store execution plans is called process cache.

The SQL Server Execution Plan contains the following main components:

Query plan

The main body of the Execution Plan is a reentrant read-only data structure, which can be used by any number of users. This is called a query plan. User context is not stored in the query plan. In-memory query plan copies can never exceed two: one copy is used for all serial execution, and the other is used for all parallel execution. Parallel copies overwrite all parallel executions, regardless of the degree of parallelism of parallel executions.

Execution Context

Each user performing a query has a data structure that contains the specific data (such as the parameter value) for execution. This data structure is called the execution context. You can use the Context Data Structure again. If a user executes a query and a structure is not used, the structure is reinitialized with the context of the new user.

How to cache execution plans

SQL Server has an efficient algorithm to find existing execution plans for any specific SQL statement. When executing any SQL statement in SQL Server, the relational engine first checks whether there is an existing execution plan used for the same SQL statement in the process cache. SQL Server re-uses any existing plan found to save the overhead of re-compiling SQL statements. If no execution plan exists, SQL Server generates a new execution plan for the query.

SQL Server automatically deletes the execution plan

When will the execution plan be deleted?

If the cache is not manually cleared, if the memory is insufficient, SQL Server automatically clears some unused cache plans.

The maximum size of all caches depends on the size of max server memory.

How to determine the execution plan to be deleted

If there is insufficient memory, the database engine uses an overhead-based method to determine which execution plans are deleted from the process cache. How to determine the overhead of an execution plan? For the first execution plan, SQL Server sets its overhead value to 0, the SQL Server sets its overhead value as the overhead of the original compilation, therefore, the database engine checks the status of each execution plan repeatedly and deletes the execution plan with zero overhead. If there is insufficient memory, the execution plan with zero overhead will not be deleted automatically, but only when the database engine checks the execution plan and finds that its current overhead is zero, the plan will be deleted. When you check the execution plan, if no query currently uses the plan, the database engine reduces the current overhead to push it to zero.

The database engine checks the execution plan again until enough execution plans are deleted to meet memory requirements. If there is insufficient memory, the execution plan can increase or decrease its overhead multiple times. If the memory is insufficient, the database engine will no longer reduce the current overhead of the unused execution plan, and all execution plans will be stored in the process cache, even if the overhead is zero.

Re-compile the execution plan

Depending on the new status of the database, some changes in the database may cause the execution plan to be less efficient or invalid. SQL Server detects changes that make the execution plan invalid and marks the plan as invalid. After that, you must re-compile the new plan for the next connection that executes the query. Plan invalidation may be caused:

• Alter table and alter view ).
• Modify any indexes used in the execution plan.
• UPDATE the STATISTICS used by the execution plan. These updates may be generated explicitly or automatically from statements (such as update statistics.
• Delete the index used by the execution plan.
• Call sp_recompile explicitly.
• A large number of key changes (modifications made by other users to the tables referenced by the query using the INSERT or DELETE Statement ).
• For tables with triggers, the number of rows in the inserted or deleted tables increases significantly.
• Use the with recompile option to execute the stored procedure.