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