Caching mechanism for executing a plan in SQL Server

SQL query procedure　　　　　　

When executing an SQL statement or stored procedure, the approximate process for SQL Server is

1. Analyze the query statement, generate a logical unit, and perform a basic grammar check

2. Build a query tree (all operations in a query statement are converted to operations on the base table, such as converting a view to a base table query)

3. Generate execution plan (many different execution plans are generated for the query tree)

4. The query optimizer selects an optimal execution plan based on the query cost of each execution plan and the statistics in the database

5. The optimal execution plan is cached in the database's cache pool. When the same SQL statement is executed again, the cached execution plan is used directly. This can improve performance.

View Cache Execution Plan DMV

The execution plan for the current system cache can be queried by Sys.dm_exec_cached_plans. For example, the following code can query the cache for all the optimal execution plans generated for the stored procedure

Select *  from where = ' Proc '

The number of times that the execution plan has been reused is shown in the query results.

Note: Normal stored procedures are compiled the first time they are executed, and then the execution plan is generated and cached.

Through the description of the query process above, the execution plan of the statements that we execute directly through Management Studio is also cached.

A list of caches can be obtained by following the query

Select *  from where = ' Adhoc '

At the same time, you can get detailed information about a query plan by querying Sys.dm_exec_text_query_plan. For example, the following code can query the details of the execution plan of the stored procedure described above

Declare @planHandle varbinary (+) SELECT @planHandle =  from where = 6124 Select *  from Sys.dm_exec_text_query_plan (@planHandle,0,-1)

The results are as follows:

The value of the Query_plan column is the XML that describes the execution plan details

Management of the execution plan

1. When the database restarts, it is emptied. Then we can also empty the cache by executing DBCC FREEPROCCACHE.

2. When the database is low on memory, a specific mechanism is used to purge the execution plan from the cache.

Caching mechanism for executing a plan in SQL Server