MSSQLWIKI

Karthick P.K on SQL Server

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)?

clip_image002

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

8 Responses to “What does MemoryUtilization in sys.dm_os_ring_buffers and Memory_utilization_percentage in sys.dm_os_process_memory represents?”

  1. 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…

      • 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.

  2. […] What does MemoryUtilization in sys.dm_os_ring_buffers and Memory_utilization_percentage in sys.dm_os… […]

  3. 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.

  4. fitness said

    Thhis piece of writing will help the internet
    visitors for creating neա webpage or even a blog from start to end.

  5. 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!

  6. The iPhone 7 phones running on Intel do not support Ultra HD Voice, or Enhanced Voice Services, while the Qualcomm units do.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: