Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculating Memory usage


Calculating Memory usage

Author
Message
Oracle_91
Oracle_91
Right there with Babe
Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)

Group: General Forum Members
Points: 774 Visits: 1742
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 ProcessTonguerivate 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.
sql-lover
sql-lover
SSChasing Mays
SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)

Group: General Forum Members
Points: 651 Visits: 1930
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.
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45147 Visits: 39923
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Oracle_91
Oracle_91
Right there with Babe
Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)

Group: General Forum Members
Points: 774 Visits: 1742
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) .
sql-lover
sql-lover
SSChasing Mays
SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)SSChasing Mays (651 reputation)

Group: General Forum Members
Points: 651 Visits: 1930
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)'


Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45147 Visits: 39923
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search