Share via

7.0 Costing Cache Entries

  • Article

 

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

on cp.memory_object_address = ce. memory_object_address

go

 

The pages_allocated_count in the above query includes the compile time and the execution time memory as well. The contribution of memory to the cost of the query is based only on the compile time memory. The cost of a query is non deterministic due to the dependency of the cost on disk IO’s and the number of context switches. If the system is heavily loaded, the contribution of the number context switches can potentially be very much higher than if the same query was compiled when the system was not heavily loaded. The contribution of disk IO’s can also be high when there are no entries in the meta data cache causing meta data cache lookups from disk.

 

In order to avoid potential flooding of the plan cache some heuristics have been put in place to make a decision regarding the cache-ability of a plan after the cost is computed. Adhoc complied plans that are evaluated to a zero cost are not cached. Adhoc compiled plan entries are inserted into the cache with a cost of zero regardless of their original cost (as computed using the above formula). Their cost is re-set to their original cost on first re-use. Since in most cases it is not likely that these adhoc compiled plans will be re-used we set their cost to zero making them the first candidates for removal under memory pressure conditions. The maximum cost of these entries is 4, and therefore they can survive up to a maximum of 2 clock rounds under memory pressure conditions. Prepared compiled plans are inserted with their original cost, and they can survive up to 8 rounds under memory pressure before they are evicted from cache making their maximum cost 256. Entries in the OBJCP cache store are inserted with their original cost since they are highly reuse-able making it valuable to cache them.

Comments