SQL Server Execution Plan

SQL Server submits a user-submitted DML statement that, through a series of optimizations, produces a scenario that can be identified and efficiently "responded" by SQL Server using the Ctrl+m (actual execution plan) in Ctrl+l (estimated execution plan)

Committing a DML statement (CRUD) can cause a series of activities.

1. Activities that occur in the relationship engine

2. Activities that occur in the storage engine

In the relational engine, the query is parsed and passed to the query optimizer, and in the query optimizer, the query is parsed to generate an execution plan, which is then issued to the storage engine, which the storage engine looks for or modifies data as scheduled, and then returns it to the client

It is important to note that the estimated execution plan produced by the optimizer is the actual execution plan that is generated in the storage engine. The plan may differ from the estimated execution plan, which can cause discrepancies.

1. Due to exceeding the threshold for parallel execution, the original plan changes, parallel execution is used,

2. Statistical information changes, outdated, this time will also change the estimated execution plan

3. Due to certain circumstances, the recompilation

Second: Forecast and actual implementation plan

1. There are two types of implementation plans: One is the estimation of the execution plan, which is generated by the optimizer, the logical step to identify execution, and the actual execution plan, which is generated in the actual execution plan and identifies the actual execution.

In most cases, the actual execution plan and the estimated execution plan are the same, the estimated execution plan is stored in the plan cache, and statistics can be obtained by accessing the plans in those caches. In particular, for some large queries, getting actual execution plans is often unrealistic. The actual execution plan can be used to get the actual number of rows, actual statistics.

2. Execution Plan Reuse

By reusing an execution plan that already exists in the plan cache. Can greatly reduce the cost of the server.

After the query is submitted, a hash is created at the Algebrizer stage to uniquely identify the query, and the query's statement is identified, the optimizer compares the hash to the hash in the cache, and if the query already exists, it skips optimizations and reuses the execution plan in the cache.

In order to reuse the execution plan, when coding, as far as possible to write some SQL Server reuse code, parameterized query is one of them, stored procedure is also a good choice, if you write the statement in a hard-coded way, even if a small number of changes will cause the cache loss, because the script is different, SQL Server cannot find the cached hash value, which can cause unnecessary optimization overhead.

SQL Server does not permanently save the planned cache, and there is no permanent plan in the cache, and each plan will have an age value, which will be scanned when the algebrizer is triggered, and each time the value is lowered.

When all of the following conditions are met, the estimated execution plan is removed from memory.

1. The operating system requires more memory

2.Age value has been reduced to 0

3. The execution plan is not being used by the current connection

Cause the execution plan to recompile to avoid the following scenario.

1. Query the table structure or schema changes referenced

2. Index changes used by the query

3. The index used for the query is deleted

4. Show Call sp_recompile

5. Query the referenced table, due to a large number of insert/delete operations on the key value, resulting in a change in statistics

6. Mixed DDL and DML operations in a single query, called deferred compilation

7. The SET option was modified in the query

8. Schema, structure modification of the temporary table used by the query

9. The target selection changes in the query process

Three: Clear the execution plan in the cache

DBCC Freeproccache

Four: Execution plan format

SQL Server Execution Plan