locked
SQL Server 2008 R2 Ent Memory Problem RRS feed

  • Question

  • the problem we are having is our SQL Server's memory usage is very strange. What i mean if i use the resource mon it only shows my SQL instance gaining memory in the Commit(7GB of Memory) where the Private(700MB) and Working(700MB). Are these not supposed to be equal? Here is a little history about our server:

    Starting Setup:

    Intel Server Board
    24GB Memory
    SSD for OS Drive
    Dual Xeon 5620 CPU's

    With this setup the memory usage was normal but with the below setup is where it gets strange

    Current Setup

    Intel Server Board
    64GB Memory
    SSD for OS Drive
    Dual Xeon 5620 CPU's

    The only change we made was adding in more memory and after this the SQL Server started using memory completely different than before. Any light that can be shared on this would be outstanding. To help you guys see what i am seeing I will post a few screenshots for you..

    We have done the locked pages group policy change as well.

    http://i.imgur.com/io8lcVk.jpg - Resource Mon

    http://i.imgur.com/gLauQRo.jpg - VMMAP


    • Edited by nYGHTWING Tuesday, April 16, 2013 1:50 PM
    Tuesday, April 16, 2013 1:49 PM

Answers

  • There's a couple of ways of monitoring the memory usage (Task Manager is not one of them) such as the perfmon counter SQL Server: Memory Manager > Total Server Memory (KB), you may need to replace SQL Server with the instance name if it's a named instance, or via the DBCC MEMORYSTATUS command or the DMV sys.dm_os_memory_clerks.

    These will give you an idea of how much and where the memory is being used, assuming the server is configured correctly (such as Lock Pages in Memory being set for the service account and the max server memory setting is high enough).


    ajmer dhariwal || eraofdata.com

    • Proposed as answer by Fanny Liu Tuesday, April 23, 2013 9:05 AM
    • Marked as answer by Fanny Liu Friday, April 26, 2013 1:35 AM
    Tuesday, April 16, 2013 7:41 PM
    Answerer
  • No they will not be equal. 

    Ignore resource monitor as it does not really tell you what you want to know. The VMMap tool gives you a better view of the memory consumption, alternatively look at the sql dmvs or perfmon counters listed at very bottom of the quoted text. But as you can see with VMMap your total usage is going to be in the 8GB region based on your linked data.

    http://support.microsoft.com/kb/918483

    After you assign the Lock pages in memory user right and you restart the SQL Server service, the buffer pool of the SQL Server process still responds to memory resource notification events, and it dynamically increases or decreases in response to these events. However, you cannot see memory allocations for the buffer pool that are locked in memory in the following performance counters:

    • The Private Bytes counter and the Working Set counter in Performance Monitor
    • The Mem Usage column on the Processes tab in Task Manager

    After these pages are locked, these performance counters represent the memory allocations inside the SQL Server process when those allocations do not use the buffer pool. The Total Server Memory(KB) counter of the SQL Server:Memory Manager performance object accurately represents the memory that is allocated for the buffer pool.



    Jon


    • Edited by Jon Gurgul Tuesday, April 16, 2013 8:04 PM
    • Proposed as answer by Fanny Liu Tuesday, April 23, 2013 9:05 AM
    • Marked as answer by Fanny Liu Friday, April 26, 2013 1:35 AM
    Tuesday, April 16, 2013 8:04 PM

All replies

  • Hello!

    To know how much memory your databases are consuming look the perfmon counter called SQL Server: Buffer Manager: Database Pages, multiply the average value per eight and divide this per 1024 to know the value in MBytes.

    To investigate some problem related to memory you can try the article below:

    http://blogs.msdn.com/b/karthick_pk/archive/2012/06/15/troubleshooting-sql-server-memory.aspx

    Regards,


    André CR / Helped? If the answer is yes mark! If the answer is no, wait a little bit because i'll back! Visit my blog! sqlmagu.blogspot.com.br

    Tuesday, April 16, 2013 4:09 PM
  • There's a couple of ways of monitoring the memory usage (Task Manager is not one of them) such as the perfmon counter SQL Server: Memory Manager > Total Server Memory (KB), you may need to replace SQL Server with the instance name if it's a named instance, or via the DBCC MEMORYSTATUS command or the DMV sys.dm_os_memory_clerks.

    These will give you an idea of how much and where the memory is being used, assuming the server is configured correctly (such as Lock Pages in Memory being set for the service account and the max server memory setting is high enough).


    ajmer dhariwal || eraofdata.com

    • Proposed as answer by Fanny Liu Tuesday, April 23, 2013 9:05 AM
    • Marked as answer by Fanny Liu Friday, April 26, 2013 1:35 AM
    Tuesday, April 16, 2013 7:41 PM
    Answerer
  • No they will not be equal. 

    Ignore resource monitor as it does not really tell you what you want to know. The VMMap tool gives you a better view of the memory consumption, alternatively look at the sql dmvs or perfmon counters listed at very bottom of the quoted text. But as you can see with VMMap your total usage is going to be in the 8GB region based on your linked data.

    http://support.microsoft.com/kb/918483

    After you assign the Lock pages in memory user right and you restart the SQL Server service, the buffer pool of the SQL Server process still responds to memory resource notification events, and it dynamically increases or decreases in response to these events. However, you cannot see memory allocations for the buffer pool that are locked in memory in the following performance counters:

    • The Private Bytes counter and the Working Set counter in Performance Monitor
    • The Mem Usage column on the Processes tab in Task Manager

    After these pages are locked, these performance counters represent the memory allocations inside the SQL Server process when those allocations do not use the buffer pool. The Total Server Memory(KB) counter of the SQL Server:Memory Manager performance object accurately represents the memory that is allocated for the buffer pool.



    Jon


    • Edited by Jon Gurgul Tuesday, April 16, 2013 8:04 PM
    • Proposed as answer by Fanny Liu Tuesday, April 23, 2013 9:05 AM
    • Marked as answer by Fanny Liu Friday, April 26, 2013 1:35 AM
    Tuesday, April 16, 2013 8:04 PM