SQL Server Plan Cost - sys.dm_os_memory_cache_entries, original_cost not as documented?

• jacob.lovatt

  SSC Journeyman

  Points: 89

  More actions

  September 10, 2015 at 9:17 pm

  #311533

  In this Microsoft whitepaper (and word for word in Kalen Delaney's SQL Server 2012 Internals Book, except Delaney's book doesn't state that two IOs and two context switches account for 1 tick, her book says this is 1:1), it is claimed that a plan cache entries' cost is calculated as follows:

  ------------------------------------------------------------------------------

  Cost = I/O cost + context switch cost (a measure of CPU cost) + memory cost

  The individual parts of the cost are calculated as follows.

  •Two I/Os cost 1 tick, with a maximum of 19 ticks.

  •Two context switches cost 1 tick, with a maximum of 8 ticks.

  •Sixteen memory pages (128 KB) cost 1 tick, with a maximum of 4 ticks.

  ------------------------------------------------------------------------------

  When I join sys.dm_exec_cached_plans to sys.dm_os_memory_cache_entries on memory_object_address to view the cost of my plan cache entries, I can see this maps to the documented cost n as 2^n for some plans, but not all; in my testing the majority deviate.

  I see plans with the maximum negative signed integer (-2,147,483,648) as well, is anyone aware what that may mean?

  Is there any further information on the discrepancies?

  Query for Reference:

  SELECT

  db_name(st.dbid) [database_name]

  ,object_name(st.objectid, st.dbid) [OBJECT_NAME], objtype, refcounts, usecounts, cast((size_in_bytes/power(2.0,20)) as decimal(10,2)) [size_in_mb],

  disk_ios_count, context_switches_count,

  pages_kb as MemoryKB, original_cost, current_cost,

  case when original_cost > 0 then log(original_cost,2)

  else original_cost end as [lg_cost]

  --DOCUMENTED COST CALCULATION

  ,case when disk_ios_count/2 > 19 then 19 else disk_ios_count/2 end [disk_io_cost]

  ,case when context_switches_count/2 > 8 then 8 else context_switches_count/2 end [cpu_cost]

  ,case when (pages_kb/(8*16)) > 4 then 4 else (pages_kb/(8*16)) end [pages_cost]

  , (case when disk_ios_count/2 > 19 then 19 else disk_ios_count/2 end )

  + (case when context_switches_count/2 > 8 then 8 else context_switches_count/2 end)

  + (case when (pages_kb/(8*16)) > 4 then 4 else (pages_kb/(8*16)) end) [documented_cost_calculation]

  FROM sys.dm_exec_cached_plans p

  CROSS APPLY sys.dm_exec_sql_text(plan_handle) st

  JOIN sys.dm_os_memory_cache_entries e

  ON p.memory_object_address = e.memory_object_address

  WHERE cacheobjtype = 'Compiled Plan'

  AND type in ('CACHESTORE_SQLCP', 'CACHESTORE_OBJCP')

  and objtype = 'proc'

  order by original_cost

  Note: changing the 2:1 ratio to 1:1 results in a similar number of misses