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
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;
-- 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
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
rivate 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 ?