sys.dm_os_memory_brokers – Day 26 – One DMV a Day

Hello Geeks and welcome to the Day 26 of the long series of One DMV a day. In this series of blogs I will be exploring and explaining the most useful DMVs in SQL Server. As we go ahead in this series we will also talk about the usage, linking between DMVs and some scenarios where these DMVs will be helpful while you are using SQL Server. For the complete list in the series please click here.

In the last blog I have talked about sys.dm_os_memory_cache_hash_tables. Today I am going to tal about one more memory related DMV, sys.dm_os_memory_brokers. This is not a cache DMV. Memory brokers track the allocations of memory internal to SQL Server. They distribute memory efficiently based on current and projected usage.

According to MSDN there are three types of memory brokers.

MEMORYBROKER_FOR_CACHE – Memory allocations used by cached objects
MEMORYBROKER_FOR_STEAL – Memory used for compilations. Stolen from buffer pool.
MEMORYBROKER_FOR_RESERVE – Memory used for query execution.

There are three other memory brokers which will show up in this DMV from 2012 through 2014. These are not covered in MSDN.

MEMORYBROKER_FOR_XTP – From 2012. Memory used for MEMORYCLERK_XTP
MEMORYBROKER_FOR_HASHED_DATA_PAGES – From 2014. Not much information on this. I will update as soon as I get some details
MEMORYBROKER_FOR_COMMITTED – From 2012. Memory used by all other caches except above

So let me run the below query to show the output from sys.dm_os_memory_brokers.

   
SELECT pool_id,
	memory_broker_type,
	allocations_kb,
	allocations_kb_per_sec,
	predicted_allocations_kb,
	target_allocations_kb,
	future_allocations_kb,
	overall_limit_kb,
	last_notification
FROM sys.dm_os_memory_brokers

sys.dm_os_memory_brokers

Each column from sys.dm_os_memory_brokers is self-explanatory. Most important columns to observe are

allocations_kb – to check total allocations.
predicted_allocations_kb – Prediction of allocation based on usage pattern.
target_allocations_kb – Recommended allocations based on memory setting. Broker will shrink or grow to this value.
last_notification – Indicate the last action like GROW, SHRINK, STABLE.

If the number of allocations is more than target allocations the broker will shrink and lead to internal memory pressure. There are some other similar analysis that can be done based on above columns. I leave them to your imagination.

So now you have learnt about various brokers available in SQL Server to track allocations using sys.dm_os_memory_brokers. Tomorrow I will be covering another memory related DMV. So, stay tuned. Till then

Happy Learning,
Manu

Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook

   

About Manohar Punna

Manohar Punna is a Microsoft Data Platform Consultant and a Data Platform MVP. In his day to day job he works on building database and BI systems, automation, building monitoring systems, helping customers to make value from their data & explore and learn. By passion Manohar is a blogger, speaker and Vice President of DataPlatformGeeks. He is a community enthusiast and believes strongly in the concept of giving back to the community. Manohar is a speaker at various Data Platform events from SQL Server Day, various user groups, SQLSaturdays, SQLBits, MS Ignite, SSGAS & DPS. His One DMV a Day series is the longest one day series on any topic related to SQL Server available so far.

View all posts by Manohar Punna →

Leave a Reply

Your email address will not be published.