Post reply

Calculating Memory usage

  • September 27, 2013 at 10:15 pm

    #280299

    Hi,

    I wanted to calculate the Total SQL Server Memory usage using DMVs.

    From sql server 2008, I have below direct query. What I am trying is to get the same value using

    sys.dm_os_memory_clerks. For memory troubleshooting we normally use DBCC MEMORYSTATUS and sys.dm_os_memory_clerks.

    I am personally comfortable with tsql and wanted to use sys.dm_os_memory_clerks.

    Infact, I use sys.dm_os_memory_clerks to see where sql server memory has gone or in other words

    which component is taking up more memory within sql server.

    But here, i am interested in only calculating total sql server memory usage

    which i am not able to get using below query.

    Notes: I am working on SQL 2008 Developer Edition , 10.0.5500 x64, OS 64bit i.e. win 7 sp1 and LPM is enabled as service account is Local System.

    -- sql 2008 direct query to find out sql server overall memory usage

    select

    virtual_address_space_committed_kb/(1024) as [Total Overall SQL Server Memory usage(MB)]--RAM+pagefile. Does this include AWE and MTL memory as well when we say commit;

    from sys.dm_os_process_memory

    go

    --output

    --249 mb

    -- Reference Link:http://blogs.msdn.com/b/sqljourney/archive/2013/03/16/an-in-depth-look-at-sql-server-memory-part-3.aspx

    I am trying get the same value using sys.dm_os_memory_clerks. Why am I not getting the same value.

    Am I missing anything to sum up? Basically, I wanted to use this query for sql 2005 instances.

    select SUM(single_pages_kb+multi_pages_kb+awe_allocated_kb+shared_memory_committed_kb+virtual_memory_committed_kb)/1024 from sys.dm_os_memory_clerks

    --output

    --144 mb

    I am getting 144 as my output, above sql 2008 query returns 249 value. Why there is a difference?

    I calculated using perfmon counters i.e Process:Private Bytes(Sqlservr) + SQL Buffer Manager: Total Pages which I am getting value close to 245 which is acceptable.

    Reference Link :http://blogs.msdn.com/b/joesack/archive/2009/01/08/find-non-buffer-pool-memory-memtoleave-in-private-bytes.aspx

    But why using sys.dm_os_memory_clerks I am getting a low value ?

    Please help.

    Thank you.

  • September 30, 2013 at 11:37 am

    #1654664

    There you go ...

    SET TRAN ISOLATION LEVEL READ UNCOMMITTED

    SELECT

    ISNULL(DB_NAME(database_id), 'ResourceDb') AS DatabaseName

    , CAST(COUNT(row_count) * 8.0 / (1024.0) AS DECIMAL(28,2))

    AS [Size (MB)]

    FROM sys.dm_os_buffer_descriptors

    GROUP BY database_id

    ORDER BY DatabaseName

    That gives you memory usage per database. If you play a bit more and use aggregates, you can get a total per instance.

    Credits: The T-SQL query is taken from Ian W. Stirk's book, "SQL Server DMVs in Action". Fantastic book on Dynamic Management Views.

  • September 30, 2013 at 1:11 pm

    #1654692

    NM... I made a mistake in looking at that code.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • September 30, 2013 at 1:17 pm

    #1654696

    Hi sql-lover,

    Thanks for the query. However, this script would show only the buffer distribution. ITs doesn't include the memory allocations done outside buffer pool(i.e. memToleave or non-buffer or memory allocations > 8k) .

  • September 30, 2013 at 1:48 pm

    #1654705

    Oracle_91 (9/30/2013)

    Hi sql-lover,

    Thanks for the query. However, this script would show only the buffer distribution. ITs doesn't include the memory allocations done outside buffer pool(i.e. memToleave or non-buffer or memory allocations > 8k) .

    Correct.

    The T-SQL query I gave you is for finding how much memory databases are using from SQL server buffer pool, I think. But that's usually close enough to give you an idea of how much your server is using. It's by far, the largest consumer of RAM on SQL server.

    If you want something more specific, I'm afraid you will have to use SQL perfom monitor counters for that.

    Here's another way to get that, via DMV

    SELECT object_name, counter_name, cntr_value AS 'Total Server Memory (KB)'

    FROM sys.dm_os_performance_counters

    WHERE counter_name = 'Total Server Memory (KB)'

  • September 30, 2013 at 4:56 pm

    #1654735

    Oracle_91 (9/30/2013)

    Hi sql-lover,

    Thanks for the query. However, this script would show only the buffer distribution. ITs doesn't include the memory allocations done outside buffer pool(i.e. memToleave or non-buffer or memory allocations > 8k) .

    Do you have one that does?

    Apologies. I didn't realize that you were the original-poster.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

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