Stored Procedures Cache

The stored procedure cache is an area of memory where SQL Server keeps the compiled execution plans. Once a stored procedure has been executed, the execution remains in memory, so the subsequent users, each with a different execution context (including parameters and variables) can share one copy of the procedure in memory. SQL Server 2000 has one unified cache, where it stores data pages with the stored procedures and queries plans. Because SQL Server 2000 can dynamically allocate more memory when it is needed, the execution plan can stay in the cache as long as it is useful. However, if there is not enough memory for the current SQL Server work, the older plans could be deleted from the cache to free up memory.
Each execution plan has an associated cost factor that indicates how expensive the structure is to compile. The more expensive the stored procedure is to compile, the larger the associated cost factor it will have, and vice versa. Each time the stored procedure is referenced by a connection, its cost factor is incremented. Therefore, a cached plan can have a big cost factor when the object is referenced by a connection very frequently or when recreation of the execution plan is very expensive. The lazywriter process is responsible for determining whether to free up the memory the cache is using, or keep the plan in cache. This process periodically scans the list of objects in the procedure cache. If the cost of a buffer is greater than zero when the lazywriter inspects it, the lazywriter decrements the cost factor by one.
Every time a cached plan is reused, its cost reverts to its original value. The lazywriter process deletes the execution plan from the cache when the following three conditions are met:
The memory is required for other objects and all available memory is currently in use.
The cost factor for the cached plan is equal to zero.
The stored procedure is not currently referenced by a connection.
The frequently referenced stored procedures do not have their cost factor decremented to zero and are not aged from the cache. Even though the plan's cost factor will be equal to zero, it will stay in cache unless memory is required for other objects.
When you tune your stored procedures to maximize performance, you may want to clear the stored procedures cache to ensure fair testing. In this case, you can use the DBCC FREEPROCCACHE statement to clear the procedure cache.