Post reply

SQL Server Memory Usage - sys.dm_os_process_memory total not matched when summing sys.dm_os_memory_clerks. Where is the missing memory?

  • September 5, 2015 at 7:55 am

    #320286

    Hello

    I am trying to track SQL Server memory usage.

    Querying the sys.dm_os_process_memory gives me physical_memory_in_use_kb

    When i then query sys.dm_os_memory_clerks and sum over pages_kb I am missing about 2GB.

    Where is the missing memory?

    SELECT

    physical_memory_in_use_kb/1024.0 [PHYMEM_IN_USE_MB]

    ,(select SUM(pages_kb)/1024.0 [PAGES_MB] from sys.dm_os_memory_clerks) [OS_MEM_CLERKS_MB]

    ,(

    physical_memory_in_use_kb - (select SUM(pages_kb) from sys.dm_os_memory_clerks)

    )/1024.0 [DIFFERENCE_MB]

    FROM sys.dm_os_process_memory

    Note: Lock pages in memory is enabled

    Thanks

  • September 5, 2015 at 11:02 pm

    #1824845

    If my guess is right, the reason could be this.

    physical_memory_in_use_kb shows the total memory usage of SQLServer process which includes Buffer pool + Memory to Leave (MTL) area.

    Sum of memory clerks dmv shows only for memory pool ( buffer pool + memory used by CLRs) and doesn't contain info about MTL. That could be the reason for the difference.

    Reference read : http://mssqlwiki.com/2012/10/21/sql-server-2012-memory-2/

    Regards,
    Raj

    http://Strictlysql.blogspot.com

  • September 6, 2015 at 3:48 am

    #1824849

    Thanks for the reply.

    Unfortunately not, I can track memtoleave with Process Private Bytes when LPIM enabled.

    This does not account for the missing memory, there is still over 1.5GB unaccounted for.

    I have also tried including the SQLAgent, SQLWriter and DISTRIB and cmd.exes (for replication) and still can make the figures add up.

  • September 6, 2015 at 4:38 am

    #1824851

    Yes, I agree that MTL wouldn't totally account for all the missing memory. Just wanted to point out that, it is not covered in os_memory_clerks.

    Regards,
    Raj

    http://Strictlysql.blogspot.com

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

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