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
MSSQLWIKI 