Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Calculating Memory usage Expand / Collapse
Author
Message
Posted Friday, September 27, 2013 10:15 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:42 AM
Points: 774, Visits: 1,735
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.


Post #1499634
Posted Monday, September 30, 2013 11:37 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 8:53 AM
Points: 411, Visits: 1,310
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.
Post #1500132
Posted Monday, September 30, 2013 1:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1500168
Posted Monday, September 30, 2013 1:17 PM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, October 16, 2014 2:42 AM
Points: 774, Visits: 1,735
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) .

Post #1500172
Posted Monday, September 30, 2013 1:48 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 8:53 AM
Points: 411, Visits: 1,310
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)'

Post #1500183
Posted Monday, September 30, 2013 4:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1500218
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse