sys.dm_os_memory_cache_counters vs. sql server:Plan Cache Cache Pages

• icata

  Hall of Fame

  Points: 3176

  More actions

  February 14, 2008 at 10:14 am

  #154129

  Hi guys,

  I am looking at the plan caches/cached pages from the perspective of

  sys.dm_os_memory_cache_counters and sql server:Plan Cache - Cache Pages

  For the first one I am using

  select (sum(single_pages_kb) + sum(multi_pages_kb) )

  from sys.dm_os_memory_cache_counters

  where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'

  a slight change from a query in

  http://blogs.msdn.com/sqlprogrammability/

  For the second just perfmon.

  The first one gives me a count of about 670,000 pages only for the object and query cache and the second one gives me a total of about 100,000 pages for five type of caches including object and query.

  If I am using the query from http://blogs.msdn.com/sqlprogrammability/ to determin the plan cache size

  select (sum(single_pages_kb) + sum(multi_pages_kb) ) * 8 / (1024.0 * 1024.0) as plan_cache_in_GB

  from sys.dm_os_memory_cache_counters

  where type = 'CACHESTORE_SQLCP' or type = 'CACHESTORE_OBJCP'

  it gives me about 5 GB when in fact my SQL Server it can access only max 2GB with Total and Target Server Memory at about 1.5 GB.

  Does anyone have any idea what is going on?