Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How can I get the memory usage of SQL Server 2012

Answered How can I get the memory usage of SQL Server 2012

  • Friday, March 22, 2013 9:27 AM
     
     
    Sign In to Vote
    0

    Hi Guys,

                I hope to get overall memory consumed by SQL Server 2012(installed in a is 64 bit windows 2008 R2),but when I looked at the result from dbcc memorystatus,  I'm a little confused: VM Committed is only 32812KB, AWE allocated(locked pages for 64 bit OS): 1009736KB, Current Committed :1042024 KB.

                How can I get the overall memory used by SQL Server from below result?  Thanks

    Process/System Counts                    Value
    ---------------------------------------- --------------------
    Available Physical Memory                635297792
    Available Virtual Memory                 2729140224
    Available Paging File                    2634694656
    Working Set                              79593472
    Percent of Committed Memory in WS        100
    Page Faults                              4053553
    System physical memory high              1
    System physical memory low               0
    Process physical memory low              0
    Process virtual memory low               0

    (10 row(s) affected)

    Memory Manager                           KB
    ---------------------------------------- -----------
    VM Reserved                              1197596
    VM Committed                             32620
    AWE Allocated                            1009400
    Large Pages Allocated                    0
    Emergency Memory                         1024
    Emergency Memory In Use                  16
    Target Committed                         1048576
    Current Committed                        1042024
    Pages Allocated                          673192
    Pages Reserved                           0
    Pages Free                               327008
    Pages In Use                             211112
    Page Alloc Potential                     785040
    NUMA Growth Phase                        0
    Last OOM Factor                          0
    Last OS Error                            0

    (16 row(s) affected)

    When I removed a database with 399M file size, the result(coming after several minutes) is shown as below: all of VM committed, AWE Allocated, and Current Committed are increased?  Why they are increased?

    Process/System Counts                    Value
    ---------------------------------------- --------------------
    Available Physical Memory                590487552
    Available Virtual Memory                 2731499520
    Available Paging File                    2601496576
    Working Set                              79998976
    Percent of Committed Memory in WS        100
    Page Faults                              4056092
    System physical memory high              1
    System physical memory low               0
    Process physical memory low              0
    Process virtual memory low               0

    (10 row(s) affected)

    Memory Manager                           KB
    ---------------------------------------- -----------
    VM Reserved                              1197596
    VM Committed                             32812
    AWE Allocated                            1009736
    Large Pages Allocated                    0
    Emergency Memory                         1024
    Emergency Memory In Use                  16
    Target Committed                         1048576
    Current Committed                        1042552
    Pages Allocated                          356344
    Pages Reserved                           0
    Pages Free                               644192
    Pages In Use                             246616
    Page Alloc Potential                     749536
    NUMA Growth Phase                        0
    Last OOM Factor                          0
    Last OS Error                            0

    (16 row(s) affected)

    Could someone share how you read this result? you are really appreciated.

    • Edited by Denny Song Friday, March 22, 2013 9:28 AM
    • Edited by Denny Song Friday, March 22, 2013 9:29 AM
    • Edited by Denny Song Friday, March 22, 2013 9:33 AM
    •  
     

All Replies

  • Friday, March 22, 2013 11:08 AM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Hello,

    Please use the query provided by Paul Randal to determine memory being used by database:

    SELECT  

      (CASE WHEN ([is_modified] = 1) THEN 'Dirty'

    ELSE 'Clean' END) AS 'Page State',

      (CASE WHEN ([database_id] = 32767) THEN 'Resource Database'

    ELSE DB_NAME (database_id) END) AS 'Database Name',

       COUNT (*) AS 'Page Count'FROM sys.dm_os_buffer_descriptors

    GROUP BY [database_id], [is_modified]

       ORDER BY [database_id], [is_modified];

    GO

    Each page = 8 KB

    This script was found in the following article:

    http://technet.microsoft.com/en-us/magazine/2009.10.sqlqa.aspx

    Hope this helps.

    Regards,

    Alberto Morillo
    SQLCoffee.com