whats wrong with this memory status?
-
I am running a SQL server 2008 R2 Ent X64 with Lock pages enabled....AFAIK sys.dm_os_memory_clerks is supposed to return details all memory used by SQL server, but on few system this is not the behavior...just wanted to run by with experts if missing something or its some type of memory leak?
if u run below set of statements u will see an output, where I am trying to reconcile buffer manager usage (18911 MB) with memory clerk usage (1631 MB), its not even close.... Let me know what you think and whats missing in this calculation? I am looking to find what type of memory clerk is using how much memory out of 20GB allocated to SQL?
declare @db_buffer_pages_mb bigint, @total_buffer_mb bigint, @min_server_memory_mb bigint, @max_server_memory_mb bigint
SELECT @db_buffer_pages_mb = COUNT_BIG(*)/128 FROM sys.dm_os_buffer_descriptors
SELECT @total_buffer_mb = cntr_value/128 FROM sys.dm_os_performance_counters WHERE RTRIM([object_name]) LIKE '%Buffer Manager' AND counter_name = 'Total Pages';
select @min_server_memory_mb=convert(int,value_in_use) from sys.configurations where name in ('min server memory (MB)')
select @max_server_memory_mb=convert(int,value_in_use) from sys.configurations where name in ('max server memory (MB)')
select
Total_Memory_MB = sum(single_pages_kb + multi_pages_kb + virtual_memory_committed_kb) /1024
,Total_virtual_memory_reserved_MB = sum(virtual_memory_reserved_kb) /1024
,@db_buffer_pages_mb as db_buffer_pages_mb, @total_buffer_mb as total_buffer_mb , @min_server_memory_mb as min_server_memory_mb , @max_server_memory_mb as max_server_memory_mb
,SERVERPROPERTY ('ProductVersion') as SQL2008R2_Ent_Sp2_CU1_x64
from sys.dm_os_memory_clerks
select
phys_mem_mb = total_physical_memory_kb/(1024) ,
available_physical_memory_mb = available_physical_memory_kb/(1024) ,
sys_cache_mb = system_cache_kb/(1024),
kernel_pool_mb=(kernel_paged_pool_kb+kernel_nonpaged_pool_kb)/(1024) ,
total_virtual_memory_mb = total_page_file_kb/(1024),
available_virtual_memory_mb=available_page_file_kb/(1024),
system_memory_state_desc = system_memory_state_desc
from sys.dm_os_sys_memory
GO
Output:
attached as screen shot....
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA -
To add some more data on this post, I summarized same data from several other servers running same application as attached (based on client size resources differ), all are VM (W2008 R2 SP1) running SQL 2008 R2 SP2 CU1 X64
Shortened column names to fit in one screen
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA -
any thoughts what could be happening? or what else to look for?
I also checked awe is disabled on all systems, there is enough vas memory block available on all systems
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA -
"2008 R2 Ent X64 with Lock pages enabled" so it will use AWE
SELECT SUM(awe_allocated_kb)/1024.
FROM sys.dm_os_memory_clerks
WHERE type = 'MEMORYCLERK_SQLBUFFERPOOL'
And don't compare that with the sum from the buffer descriptors, as that is database pages only.
Compare it with Total Pages instead.
I've noticed that I never see figures from different sources totally agreeing, but they should be in the same ballpark.
-
Updated code with AWE as well...
I am trying to reconcile memory usage on my servers as some of em have memory issues...
Its been 2 days and going through numerous articles including Salvo/bobward, i created below code to see all aspects of memory in one snapshots...
Then collected this data for several other systems have similar configuration and same application running (not on db server)
Discrepancies:
1. Some of the systems shows AWE as ZERO, why?
2. Systems where AWE has values over Zero, (single pages+ Multi pages + virtual committed) values are 1/5th of total memory and less than buffer pages in DB
3. Systems where AWE is non Zero, why single pages are not same value as AWE (AWE represent lock pages in memory but then what single pages represents?)
4. syscache is pretty large on few servers, how to troubleshoot further?
5. How to reconcile how awe memory (locked pages) are split to diff component
6. if its not reconciling how to track memory leak? or is it possible to clear AWE without restarting sql service?
-- awe is disabled on all systems
-- all systems running windows 2008 R2 SP1 X64
-- Lock Pages In Memory is enabled on all systems
-- All data is in MB
declare @dbBufferPages bigint, @total_buffer bigint, @min_server_Mem bigint, @max_server_Mem bigint, @lockedPageAlloc bigint
SELECT @dbBufferPages = COUNT_BIG(*)/128 FROM sys.dm_os_buffer_descriptors
SELECT @total_buffer = cntr_value/128 FROM sys.dm_os_performance_counters WHERE RTRIM([object_name]) LIKE '%Buffer Manager' AND counter_name = 'Total Pages';
select @min_server_Mem=convert(int,value_in_use) from sys.configurations where name in ('min server memory (MB)')
select @max_server_Mem=convert(int,value_in_use) from sys.configurations where name in ('max server memory (MB)')
select@lockedPageAlloc= locked_page_allocations_kb/1024 from sys.dm_os_process_memory
--select convert(int,value_in_use) from sys.configurations where name in ('awe enabled')
;WITH src AS (select
TotMem = sum(single_pages_kb + multi_pages_kb + virtual_memory_committed_kb) /1024
,TotVirMemRes = sum(virtual_memory_reserved_kb) /1024
,TotAWEallocated= SUM(awe_allocated_kb)/1024
,@lockedPageAlloc as lockedPageAlloc
,@dbBufferPages as dbBufferPages, @total_buffer as TotBuffer , @min_server_Mem as MinMem , @max_server_Mem as MaxMem
,SERVERPROPERTY ('ProductVersion') as SQL2008R2
from sys.dm_os_memory_clerks)
select
src.*,
PhysMem = total_physical_memory_kb/(1024) ,
AvailPhysMem = available_physical_memory_kb/(1024) ,
SysCache = system_cache_kb/(1024),
KernelPool=(kernel_paged_pool_kb+kernel_nonpaged_pool_kb)/(1024) ,
TotVirMem = total_page_file_kb/(1024),
AvailVirtualMem=available_page_file_kb/(1024)
from sys.dm_os_sys_memory
cross join src
Output from servers for this query (right click and select view image to see if properly)...
Prakash Heda
Lead DBA Team - www.sqlfeatures.com
Video sessions on Performance Tuning and SQL 2012 HA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply