Can a plan "age" reach zero?

Question

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.

Answer 1

My question is when could the Age of a plan reach zero?

The algorithm that SQL Server uses to determine when and how plans should be removed from cache is called the eviction policy.

The cost of plan is analyzed to determine which plans gets evicted. Upon detecting memory pressure, zero cost plans are removed from the cache and the cost of all other plans is reduced by half.

• For adhoc plans, the cost is considered to be zero, but it is increased by one every time the plan is reused.
• For other types of plans, the cost is a measure of the resources required to produce the plan. When one of these plans is reused, the cost is reset to the original cost.
• For non–adhoc queries, the cost is measured in units called ticks, with a maximum of 31. The cost is based on three factors: I/O, context switches, and memory. Each has its own maximum within the 31-tick total.

When not under memory pressure, costs are not decreased until the total size of all plans cached reaches 50 percent of the buffer pool size. At that point, the next plan access will decrement the cost in ticks of all plans by 1.

Once memory pressure is encountered, then SQL Server will start a dedicated resource monitor thread to decrement the cost of either plan objects in one particular cache (for local pressure) or all plan cache objects (for global pressure).

so the gist is ...

The Clock algorithm sweeps the cache at regular intervals. Every time an unused entry is found, the cost is decreased by some amount. If the cost is 0 and is not used, then it is removed from the cache.

Best references :

• Plan Cache Internals
• An in-depth look at SQL Server Memory–Part 1 and Part 2

Answer 2

My question is when could the Age of a plan reach zero?

As per Grant Fritchey's Simple Talk Article

Execution plans are not kept 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 (e.g. a plan with a cost of 10 that has been referenced 5 times has an “age” value f of 50).

So you can see more the number of times plan is reused the more its age gets and lesser is the chance for it to get removed.

So when plan is created and it is been referenced 0 number of times after creation the plan would be eventually be removed taking into other factors as well. I am sure there is much more to what I have written and that can be found from SQL Server 2008 execution plan and reuse.

My question is What are the different scenarios that a plan age could reach zero? And am I correct in my assumption?

I am quoting from the BOL document

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 a user process inserts an execution plan into the cache, the user process sets the current cost equal to the original query compile cost; for ad-hoc execution plans, the user process sets the current cost to zero. Thereafter, each time a user process references an execution plan, it resets the current cost to the original compile cost; for ad-hoc execution plans the user process increases the current cost. For all plans, the maximum value for the current cost is the original compile cost.

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.

An execution plan is frequently referenced so that its cost never goes to zero. The plan remains in the procedure cache and is not removed unless there is memory pressure and the current cost is zero.

An ad-hoc execution plan is inserted and is not referenced again before memory pressure exists. Since ad-hoc plans are initialized with a current cost of zero, when the database engine examines the execution plan, it will see the zero current cost and remove the plan from the procedure cache. The ad-hoc execution plan remains in the procedure cache with a zero current cost when memory pressure does not exist.