Post reply

SQL Buffer Manager Size

  • November 15, 2012 at 1:29 am

    #284841

    Hi Guys

    I use the following script to assess the current size used by my plan cache and the data cache

    select object_name,counter_name,cntr_value,cntr_value*8/1024 MBs from sys.dm_os_performance_counters

    where object_name like '%Plan Cache%' and counter_name like '%Cache Pages%' and instance_name like '%_Total%'

    or object_name like '%Buffer Manager%' and counter_name like '%Database Pages%'

    Regarding the plan cache pages, considering that when a plan that exceeds 8KB is allocated outside of the buffer pool by the multipage allocator. Does this query include that or is this just what's in my buffer pool?

    Any Help?

    Thanks

  • November 15, 2012 at 2:19 am

    #1559239

    you need to look in sys.dm_os_memory_clerks using a query such as

    SELECT SUM(multi_pages_kb)

    FROM sys.dm_os_memory_clerks

  • November 15, 2012 at 3:46 am

    #1559272

    Ok

    I've verified that the performance counter includes memory for plans allocated through both MPA and SPA by using the below

    SELECT [type]

    , SUM(single_pages_kb)/1024 AS [SPA Mem, Kb]

    , SUM(multi_pages_kb)/1024 AS [MPA Mem, Kb]

    , SUM(single_pages_kb)/1024+ SUM(multi_pages_kb)/1024 Total

    FROM sys.dm_os_memory_clerks

    where type in ('CACHESTORE_SQLCP','CACHESTORE_OBJCP','CACHESTORE_PHDR')

    GROUP BY [type] with rollup

    ORDER BY SUM(single_pages_kb)

    select object_name,counter_name,cntr_value*8/1024 MBs from sys.dm_os_performance_counters

    where object_name like '%Plan Cache%' and counter_name like '%Cache Pages%' and instance_name like '%_Total%'

    That's all good and well.

    However, I am assuming that the DMV sys.dm_exec_cached_plans reports on all plans, irrespective of whether they were allocated using SPA or MPA

    The problem is, why do the below queries return 2 different results?

    SELECT

    sum(cast(size_in_bytes as decimal(18,2)))/1024/1024 AS [Total MBs]

    FROM sys.dm_exec_cached_plans

    SELECT

    SUM(single_pages_kb)/1024+ SUM(multi_pages_kb)/1024 [Total MBs]

    FROM sys.dm_os_memory_clerks

    where type in ('CACHESTORE_SQLCP','CACHESTORE_OBJCP','CACHESTORE_PHDR')

    Any help with this guys?

    Thanks

  • November 15, 2012 at 8:49 am

    #1559409

    Guys

    Any help/ideas with this? Or pointers in the right direction?

    Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply