[SQL Server] cache and reuse of execution plans

Cache and reuse of execution plans

 

SQL Server 2005 has a memory pool for storing 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 2005 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. There will never be two or more query plan copies in the memory: one for all serial execution, and the other 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.

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

SQL Server 2005 has an efficient algorithm for searching existing execution plans for any specific SQL statement. In most systems, this scan consumes less resources than it saves by reusing an existing plan instead of compiling each SQL statement.

This algorithm matches the new SQL statement with the existing unused execution plan in the cache, and requires that all object references are completely legal. For example, the first statement in the two select statements does not match the existing plan, and the second statement does:

	{ Copycode (this) } "Onmouseover =" function onmouseover () { Changecopycodeicon (this) } "Onmouseout =" function onmouseout () { Changecopycodeicon (this) } "Onkeypress =" function onkeypress () { Copycode_checkkey (this) } "> Copy the code
SELECT * FROM Person.ContactSELECT * FROM AdventureWorks.Person.Contact

In SQL Server 2000 and SQL Server 2005 instances, the probability of re-use of individual execution plans is higher than that of SQL Server 6.5 and earlier versions.

{
Expandcollapse (sectiontoggle0)
} "Onkeypress =" function onkeypress ()
{
Expandcollapse_checkkey (sectiontoggle0)
} "> The execution plan is aging

After an execution plan is generated, it is in the process cache. Only when space is required, SQL Server 2005 will aging the old unused plan from the cache. Each query plan and execution environment have related cost factors, which indicate the cost required for compiling the structure. The data structure also has an age field. Each time an object is referenced by a connection, its age field increases progressively according to the compilation cost factor. For example, if the cost factor of a query plan is 8 and is referenced twice, the age of the query plan is 16. The inert writer process periodically scans the list of objects cached during the process. The inert writer then reduces the age field of each object by 1 for each scan. In this example, the age of the query plan is reduced to 0 after 16 cached scans, unless other users reference the plan. If the following three conditions are met, the inert writer process releases the object:

• The Memory Manager requires memory and all available memory is in use.
• The age field of the object is 0.
• The object is not currently referenced by the connection.

Because the age field increases every time an object is referenced, the age field of the object that is often referenced will not be reduced to 0, nor will it be aging from the cache. Objects that are not often referenced will soon meet the release conditions, but will not be released unless other objects have memory requirements.

{
Expandcollapse (sectiontoggle1)
} "Onkeypress =" function onkeypress ()
{
Expandcollapse_checkkey (sectiontoggle1)
} "> Re-compile the execution plan

Based on the new status of the database, some changes in the database may cause the execution plan to be inefficient or no longer valid. SQL server detects these 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 statistical information used by the execution plan. The update may be automatically generated or displayed in the statement (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.

To make the statements correct or obtain a query execution plan that may be faster, most of them need to be re-compiled.

In SQL Server 2000, if a statement in the batch processing results in re-compilation, whether submitted through stored procedures, triggers, special batch processing, or prepared statements, will recompile the entire batch. In SQL Server 2005, only statements that cause re-compilation in batch processing will be re-compiled. Due to this difference, the re-compilation count will be performed in SQL Server 2000, which is not required in SQL Server 2005. In addition, there are more types of recompilation in SQL Server 2005, because it extends the feature set.

Statement-level re-compilation helps improve performance, because in most cases, only a few statements lead to re-compilation and related losses (CPU time and lock ). Therefore, the loss of other statements that do not need to be re-compiled in batch processing is avoided.

In SQL Server 2005, SQL Server Profiler SP: recompile traces event report statement-level re-compilation. In SQL Server 2000, this trace event only reports batch recompilation. In SQL Server 2005, The textdata column of this event is filled. Therefore, you no longer need the methods used in SQL Server 2000: You must track SP: stmtstarting or SP: stmtcompleted to obtain the transact-SQL text that causes recompilation.

SQL Server 2005 also adds a new tracking event called SQL: stmtrecompile, which reports statement-level re-compilation. It can also be used for tracking and debugging re-compilation. Only sp: recompile is generated for stored procedures and triggers, while SQL: stmtrecompile is generated for stored procedures, triggers, special batch processing, batches executed using sp_executesql, prepared queries, and dynamic SQL.

The SP: recompile and SQL: stmtrecompile eventsubclass columns all contain an integer code to indicate the cause of re-compilation. The following table shows the meaning of each code number.

Eventsubclass Value	Description
1	The architecture has been changed.
2	The statistics have been changed.
3	Compilation has been delayed.
4	The Set option has been changed.
5	The temporary table has been changed.
6	The remote row set has been changed.
7	The for browse permission has been changed.
8	The query notification environment has changed.
9	The partition view has been changed.
10	The cursor option has been changed.
11	Option (recompile) requested ).

Note:

When the auto_update_statistics database option is set to on, if the query targets tables or index views, the statistical information of the table or index view has been updated since the last execution or the base has changed significantly. The query will be re-compiled. This behavior applies to standard user-defined tables, temporary tables, and inserted and deleted tables created by DML triggers. If too much re-compilation affects the query performance, consider changing this setting to off. When the auto_update_statistics database option is set to off, recompilation based on statistics or base changes does not occur. Note that in SQL Server 2000, even if this parameter is set to off, the query is recompiled based on the base changes of the DML trigger inserted and deleted tables. For more information about disabling auto_update_statistics, see index statistics.