In SQL Server 2005, what happens when the plan cache fills up? I have done some research, and from bits and pieces gathered it seems that cached plans are given an "age" that consists of it's weight, or cost to compile, multiplied by the number of times it has been run since being cached. Over time this age is decremented, until it reaches 0, when it is considered "aged out" and is a candidate for flushing when needed. When the plan cache fills, aged out plans are flushed from the cache.
Is there anything else that is happening behind the scenes to complicate this scenario?
Thanks,
Matt
Best Answer
Best info for the subject found on MSDN is here: Execution Plan Caching and Reuse . Quote from the MSDN article:
"Removing Execution Plans from the Procedure Cache
Execution plans remain in the procedure cache as long as there is enough memory to store them. When memory pressure exists, the Database Engine uses a cost-based approach to determine which execution plans to remove from the procedure cache. To make a cost-based decision, the Database Engine increases and decreases a current cost variable for each execution plan according to the following factors.
When memory pressure exists, the Database Engine responds by removing execution plans from the procedure cache. To determine which plans to remove, the Database Engine repeatedly examines the state of each execution plan and removes plans when their current cost is zero. An execution plan with zero current cost is not removed automatically when memory pressure exists; it is removed only when the Database Engine examines the plan and the current cost is zero. When examining an execution plan, the Database Engine pushes the current cost towards zero by decreasing the current cost if a query is not currently using the plan."
It's better to read the complete article, it's very well documented. See that it also has some links.