Tuning T-SQL - Professional SQL Server 2005 Performance Tuning - page 266

Freeing the Procedure Cache
After a query plan is generated, it stays in the procedure cache. However, just as with data pages
in the data cache, query plans have to be freed up as well. For this task SQL Server uses a
different technique than the one employed for cleaning up the data cache. For the procedure cache
SQL Server uses an LRU technique. Each query plan and execution context has a cost and age
factor. Every time a query plan or execution context is referenced the age field is incremented by the
cost factor. Thus, if a query plan had a cost factor of 5 and was referenced 2 times the age factor
would be 10.

The work of applying this technique falls only to the lazywriter process for the procedure cache. The
process is much like the one for the data cache operation. The lazywriter process periodically scans the
list of plans and execution contexts in the procedure cache. The process then decrements the age by 1
for each object scanned. Thus, once an object's age reaches 0 it is removed from the procedure cache.
Note that a plan may be removed from the cache before its age reaches 0 if there is sufficient pressure on
memory or if the plan isn't referenced by any connections.

Recompiling Query Plans
One aspect about query plans is that they can become ineffective through no fault of their own. Changes
to the underlying tables used in a query can cause an existing query plan to become inefficient or invalid.
For example, suppose a query plan uses a table scan operator to fulfill a SQL statement. A DBA then
adds an index to the table. The optimal operator now might be an index seek, not a table scan. When
these changes occur SQL Server attempts to determine if the changes will invalidate an existing plan.
If so, it marks the plan as invalid. This, in turn, causes a new plan to be generated for the query. The
conditions that cause a plan to be invalidated include the following:

❑ Structural (DDL) changes made to a table or view referenced by the query (for example, adding a

column).

❑ Changing or dropping any indexes used by the query plan.

❑

Updated statistics. If statistics are updated either explicitly or automatically, then that may
invalidate existing query plans.

❑ Explicitly recompiling a query.

❑ Significantly changing the key values in the tables.

❑ A table with triggers that experience a significant growth in the number of rows in the inserted

or deleted tables.

In previous versions of SQL Server, whenever a SQL statement in a group of statements caused
recompiling, all the statements in the group get recompiled. Stored procedures, for instance, commonly
include a group of SQL statements rather than just one. Since SQL Server 2005, only the SQL statements
in the group that caused recompiling is actually recompiled. The other SQL statements in the group are
unaffected.

This type of statement-level recompiling enhances performance because only the changed queries cause
SQL Server to generate a new plan. Yet, in previous versions all the statements in the group got
recompiled whether they needed it or not.