O'Reilly logo

Microsoft® SQL Server 2012 Unleashed by Alex T. Silverstein, Chris Gallelli, Paul T. Bertucci, Ray Rankins

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required