Execution Plan Caching and Reuse

Microsoft® SQL Server™ 2000 has a pool of memory used to store both execution plans and data buffers. The percentage of the pool allocated to either execution plans or data buffers fluctuates dynamically depending on the state of the system. The part of the memory pool used to store execution plans is called the procedure cache.

SQL Server 2000 execution plans have the following main components:

• Query plan

  The bulk of the execution plan is a reentrant, read-only data structure used by any number of users. This is called the query plan. No user context is stored in the query plan. There are never more than one or two copies of the query plan in memory: one copy for all serial executions and another for all parallel executions. The parallel copy covers all parallel executions, regardless of their degree of parallelism.

• Execution context

  Each user currently executing the query has a data structure that holds the data specific to their execution, such as parameter values. This data structure is called the execution context. The execution context data structures are reused. If a user executes a query and one of the structures is not in use, it is reinitialized with the context for the new user.

When any SQL statement is executed in SQL Server 2000, the relational engine first looks through the procedure cache to verify that an existing execution plan for the same SQL statement exists. SQL Server 2000 reuses any existing plan it finds, saving the overhead of recompiling the SQL statement. If no existing execution plan exists, SQL Server 2000 generates a new execution plan for the query.

SQL Server 2000 has an efficient algorithm to find any existing execution plans for any given SQL statement. In most systems, the minimal resources used by this scan are less than the resources saved by being able to reuse existing plans instead of compiling every SQL statement.

The algorithms to match new SQL statements to existing, unused execution plans in the cache require that all object references be fully qualified. For example, the first of these SELECT statements is not matched with an existing plan, and the second is matched:

SELECT * FROM Employees

SELECT * FROM Northwind.dbo.Employees

There is a higher probability that individual execution plans will be reused in an instance of SQL Server 2000 than in SQL Server version 6.5 and earlier.

Aging Execution Plans

After an execution plan is generated, it stays in the procedure cache. SQL Server 2000 ages old, unused plans out of the cache only when space is needed. Each query plan and execution context has an associated cost factor that indicates how expensive the structure is to compile. These data structures also have an age field. Each time the object is referenced by a connection, the age field is incremented by the compilation cost factor. For example, if a query plan has a cost factor of 8 and is referenced twice, its age becomes 16. The lazywriter process periodically scans the list of objects in the procedure cache. The lazywriter decrements the age field of each object by 1 on each scan. The age of our sample query plan is decremented to 0 after 16 scans of the procedure cache, unless another user references the plan. The lazywriter process deallocates an object if these conditions are met:

• The memory manager requires memory and all available memory is currently in use.
  
  
• The age field for the object is 0.
  
  
• The object is not currently referenced by a connection.

Because the age field is incremented each time an object is referenced, frequently referenced objects do not have their age fields decremented to 0 and are not aged from the cache. Objects infrequently referenced are soon eligible for deallocation, but are not actually deallocated unless memory is required for other objects.

Recompiling Execution Plans

Certain changes in a database can cause an execution plan to be either inefficient or invalid, given the new state of the database. SQL Server detects the changes that invalidate an execution plan, and marks the plan as invalid. A new plan must then be recompiled for the next connection that executes the query. The conditions that cause a plan to be invalidated include:

• Any structural changes made to a table or view referenced by the query (ALTER TABLE and ALTER VIEW).
  
  
• New distribution statistics generated either explicitly from a statement such as UPDATE STATISTICS or automatically.
  
  
• Dropping an index used by the execution plan.
  
  
• An explicit call to sp_recompile.
  
  
• Large numbers of changes to keys (generated by INSERT or DELETE statements from other users that modify a table referenced by the query).
  
  
• For tables with triggers, if the number of rows in the inserted or deleted tables grows significantly.