sys.dm_os_memory_brokers (Transact-SQL)

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Allocations that are internal to SQL Server use the SQL Server memory manager. Tracking the difference between process memory counters from sys.dm_os_process_memory and internal counters can indicate memory use from external components in the SQL Server memory space.

Memory brokers fairly distribute memory allocations between various components within SQL Server, based on current and projected usage. Memory brokers do not perform allocations. They only track allocations for computing distribution.

The following table provides information about memory brokers.

Note

To call this from Azure SQL Data Warehouse or Parallel Data Warehouse, use the name sys.dm_pdw_nodes_os_memory_brokers.

Column name Data type Description
pool_id int ID of the resource pool if it is associated with a Resource Governor pool.
memory_broker_type nvarchar(60) Type of memory broker. There are currently three types of memory brokers in SQL Server, listed below with their descriptions.

MEMORYBROKER_FOR_CACHE : Memory that is allocated for use by cached objects.

MEMORYBROKER_FOR_STEAL : Memory that is stolen from the buffer pool. This memory is not available for reuse by other components until it is freed by the current owner.

MEMORYBROKER_FOR_RESERVE : Memory reserved for future use by currently executing requests.
allocations_kb bigint Amount of memory, in kilobytes (KB), that has been allocated to this type of broker.
allocations_kb_per_sec bigint Rate of memory allocations in kilobytes (KB) per second. This value can be negative for memory deallocations.
predicted_allocations_kb bigint Predicted amount of allocated memory by the broker. This is based on the memory usage pattern.
target_allocations_kb bigint Recommended amount of allocated memory, in kilobytes (KB), that is based on current settings and the memory usage pattern. This broker should grow to or shrink to this number.
future_allocations_kb bigint Projected number of allocations, in kilobytes (KB), that will be done in the next several seconds.
overall_limit_kb bigint Maximum amount of memory, in kilobytes (KB), that the the broker can allocate.
last_notification nvarchar(60) Memory usage recommendation that is based on the current settings and usage pattern. Valid values are as follows:

grow

shrink

stable
pdw_node_id int Applies to: Azure SQL Data Warehouse, Parallel Data Warehouse

The identifier for the node that this distribution is on.

Permissions

On SQL Server, requires VIEW SERVER STATE permission.
On SQL Database Premium Tiers, requires the VIEW DATABASE STATE permission in the database. On SQL Database Standard and Basic Tiers, requires the Server admin or an Azure Active Directory admin account. s

See Also

SQL Server Operating System Related Dynamic Management Views (Transact-SQL)