Memory Brokers In SQL Server


Memory Brokers:-

Because memory is needed by so many components in SQL Server and to make sure each component uses memory efficiently, SQL Server uses a Memory Broker, whose job is to analyze
the behavior of SQL Server with respect to memory consumption and to improve dynamic
memory distribution.Memory brokers distribute memory allocation between various components within SQL Server, but do not perform any allocations, which are handled by the Memory Manager.
The purpose of Memory Broker is to provide a centralized mechanism to distribute memory or control the allocations made by each component in SQL Server. Memory Broker monitors the demand consumption of memory by each component and then based on the information collected, it calculates and optimal value of memory for each of these components. This information is then broadcast to each of the components, which grow or shrink their usage as required. Memory broker is exposed through the DMV “sys.dm_os_ring_buffers” where the Ring_buffer_type is “RING_BUFFER_MEMORY_BROKER”. The Memory Broker is a centralized mechanism that dynamically distributes memory between the buffer pool, the query executor, the Query Optimizer, and all the various caches, and it attempts to adapt its distribution algorithm for different types of workloads. You can think of the Memory Broker as a control mechanism with a feedback loop. It monitors memory demand and consumption by component, and it uses the information that it gathers to calculate the optimal memory distribution across all components. It can broadcast this information to the component, which then uses the information to adapt its memory usage.


You can monitor Memory Broker behavior through below query as follows:

SELECT * FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type =
'RING_BUFFER_MEMORY_BROKER';

 

Output:-


The ring buffer for the Memory Broker is updated only when the Memory Broker wants the
behavior of a given component to change—that is, to grow, shrink, or remain stable.

 

Leave a Reply

Your email address will not be published. Required fields are marked *