What does MemoryUtilization in sys.dm_os_ring_buffers and Memory_utilization_percentage in sys.dm_os_process_memory represents?
Posted by Karthick P.K on June 2, 2013
Few days back someone asked me an interesting question. Why memory_utilization_percentage (working set ) is 100 % when Virtual_address_space_committed_Kb ( committed ) is around 10 GB and Physical_memory_in_use_kb is just 1.7 GB (refer below image)?
Physical_memory_in_use_kb is Memory allocated by the SQL Server process which is currently in RAM. (This includes AWE and Large pages allocation).
Virtual_address_space_committed_Kb is total memory that is allocated by process (allocated bytes can be in RAM, page file, mapped or in not used state)
Memory_utilization_percentage is ratio between Physical_memory_in_use_kb and Memory allocated by SQL Server using SQL Server memory manager(derived from dm_os_memory_nodes). If the Memory_utilization_percentage is greater than 100% then it is capped to 100% . Memory used by external components in the SQL Server address space is not considered while SQL Server derive the memory utilization percentage.
Memory_utilization_percentage = (Physical_memory_in_use_kb/Memory allocated by SQL Server using SQL Server memory manager) * 100
To reproduce the above behavior download VirtualallocLeak.dll from THIS link and copy to ‘C:\EXE\’ folder.
Execute the below script and then query the sys.dm_os_process_memory DMV
select * from sys.dm_os_process_memory exec sp_addextendedproc 'VirtualallocLeak','C:\exe\VirtualallocLeak.dll' exec VirtualallocLeak –This allocates 1048576 bytes per execution go 300 select * from sys.dm_os_process_memory
Review the memory_utilization_percentage , Virtual_address_space_committed_Kb and Physical_memory_in_use_Kb
If you liked this post, do like us on Facebook at https://www.facebook.com/mssqlwiki and join our Facebook group
Thank you,
Karthick P.K |My Facebook Page |My Site| Blog space| Twitter
Disclaimer:
The views expressed on this website/blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights
Eng.Motafa Elmasry said
Reblogged this on Database (SQL Server , Oracle) World.
velmani said
When I query sys.dm_os_process_memory in one of the 2008 sql servers(Lock page setting is enabled in this server), locked_page_allocations_kb is 600 MB whereas max sql server memory is set as 9 GB . Will the 2008 sql server not lock all the memory allocated(max sql server memory) to it if the lock page setting is enabled?
SInce in sql server 2005(64 bit) if we enable the lock page then “AWE ALLOCATED’ is equal to max sql server memory if we enable the lock page setting…
Karthick P.K said
In Lpim memory model SQLServer will commit bPool only on need.Once memory is committed it doesn’t scale down unless there is lowmemorynotification or Highmememorynotification is revoked+No reads for 10 seconds. Your 2008 is not using all the memory because there is no load. Create a large table (Around 8 GB data) and do a select on it, it will scale up.
Top SQL Server blogs from MSSQLWIKI « MSSQLWIKI said
[…] What does MemoryUtilization in sys.dm_os_ring_buffers and Memory_utilization_percentage in sys.dm_os… […]
goput.it said
Forr newest news you have to visit workd wide web and on the web I found this webswite as
a best web page for mosst up-to-date updates.
fitness said
Thhis piece of writing will help the internet
visitors for creating neա webpage or even a blog from start to end.
music said
TҺanks for fіnally writing abߋut >Ԝhat dօeѕ MemoryUtilization іn sys.dm_oѕ_ring_buffers and Memory_utilization_percentage іn sys.Ԁm_os_process_memory
represents? MSSQLWIKI <Liked it!
Lady Gaga iPhone 6 Cases said
The iPhone 7 phones running on Intel do not support Ultra HD Voice, or Enhanced Voice Services, while the Qualcomm units do.