SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Calculating Memory usage


Calculating Memory usage

Author
Message
Oracle_91
Oracle_91
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3082 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
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4301 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 Guru
SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206709 Visits: 41960
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Oracle_91
Oracle_91
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3082 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
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4301 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 Guru
SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)SSC Guru (206K reputation)

Group: General Forum Members
Points: 206709 Visits: 41960
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

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