Microsoft® SQL Server™ version 7.0 introduces significant changes in the way execution plans are managed. There is a much higher chance that individual execution plans will be reused in a SQL Server 7.0 system than in earlier versions.
SQL Server 7.0 has a pool of memory that is 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 7.0 execution plans have two main components:
The bulk of the execution plan is a reentrant, read-only data structure that can be 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.
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 7.0, the relational engine first looks through the procedure cache to see if there is an existing execution plan for the same SQL statement. SQL Server 7.0 reuses any existing plan it finds, saving the overhead of recompiling the SQL statement. If there is no existing execution plan, SQL Server 7.0 goes ahead and generates a new execution plan for the query.
SQL Server 7.0 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, while the second is:
SELECT * FROM Employees
SELECT * FROM Northwind.dbo.Employees
After an execution plan is generated, it stays in the procedure cache. SQL Server 7.0 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. On each scan, the lazywriter decrements the age field for each object by 1. 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 three conditions are met:
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 that are infrequently referenced are soon eligible for deallocation, but are not actually deallocated unless memory is needed for other objects.
Certain changes in a database can cause an execution plan to be either inefficient or no longer valid, given the new state of the database. SQL Server detects these 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: