# 7.0 Costing Cache Entries

A uniform costing scheme has been implemented for all cache stores in SQL Server 2005. The cost of a query depends on 3 factors: number of disk IO requests, number of context switches during query compilation (in 4 ms quantums) and the number of memory pages, both from single and multi-page allocations. In SQL Server 2005, the original cost of the query is a power of two, the exponent being the sum of the contributions of disk IO’s, context switches and memory pages to the cost.

Mathematically:

Cost = 2 min (IO > 0 ? (IO - 1) / 2 + 1 : 0, 19) + min (CS > 1 ? (CS - 1) / 2 + 1 : 0, 8) + min (MP / 16, 4)

Where

IO - the number of IO requests (regardless of the number of bytes involved),

CS - the number of context switches, i.e. the number of 4 ms quantums,

MP - the number of memory pages (from both single and multi page allocations)

The contribution of context switches is as follows: for queries with zero or one context switches, the contribution is zero. For queries with number of context switches two, the contribution is one. For every two context switches over two, the contribution increases by one up to a maximum of eight. The disk IO’s contribution is zero if the number of disk IO’s is zero. For every two disk IO’s over zero, the contribution increases by one up to a maximum of nineteen. The contribution from memory pages is the number of pages divided by sixteen up to a maximum of four. In other words every 128KB contribute one up to 512 KB. To illustrate with an example: if a query has zero disk IO’s, four context switches, and 2 memory pages, then original cost is 2 (0 + 2 + 0) = 4.

To obtain the original and the current cost of a cached entry use the query below:

select text, usecounts, original_cost, current_cost, disk_ios_count, context_switches_count, pages_allocated_count

from sys.dm_exec_cached_plans cp cross apply sys.dm_exec_sql_text(plan_handle) st

join sys.dm_os_memory_cache_entries ce