Microsoft® SQL Server 2008 R2 Unleashed

Query Plan Aging

A query plan is saved in cache along with a cost factor that reflects the cost of actually creating the plan when compiling the query. For ad hoc query plans, SQL Server sets its cost to 0, which indicates that the plan can be removed from the plan cache immediately if space is needed for other plans. For other query plans, such as for a stored procedure, the query plan cost is a measure of the amount of resources required to produce it. This cost is calculated in “number of ticks.” The maximum plan cost is 31. The plan cost is determined as follows:

Every 2 I/Os required by the plan = 1 tick (with a maximum of 19 ticks)

Every 2 context switches in the plan = 1 tick (with a maximum of 8 ticks)

Every 16 pages (128KB) of memory ...