I am reading SQL Server Execution plans by Grant Fritchey, He mentions:
SQL Server does not keep execution plans in memory forever. They are slowly aged out of the system using an "age" formula that multiplies the estimated cost of the plan by the number of times it has been used. The lazywriter process, an internal process that works to free all types of cache (including the plan cache), periodically scans the objects in the cache and decreases this value by one each time.
If the following criteria are met, the plan is removed from memory:
- more memory is required by the system
- the "age" of the plan has reached zero
- the plan isn't currently being referenced by an existing connection.
He also mentions earlier in the book the following:
Once the optimizer arrives at an execution plan, the estimated plan is created and stored in a memory space known as the plan cache – although this is all different if a plan already exists in cache.
I would assume that the plan could theoretically reach zero if the actual and estimated plan differ. This would give the estimated plan execution count zero even though it was stored in the cache.
My question is What are the different scenarios that a plan age could reach zero? And am I correct in my assumption?
Fritchey, G. (2012). SQL Server Execution Plans. Springfield, USA: Simple Talk Publishing.