| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | ||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
sys.dm_os_memory_brokers (Transact-SQL) |
sys.dm_os_memory_brokers (Transact-SQL) |
rwestMSFT |
randolphwest |
02/27/2023 |
sql |
system-objects |
reference |
|
|
|
>=sql-server-2016||>=sql-server-linux-2017||>=aps-pdw-2016||=azure-sqldw-latest |
[!INCLUDE sql-asa-pdw]
Allocations that are internal to [!INCLUDEssNoVersion] use the [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] memory space.
Memory brokers fairly distribute memory allocations between various components within [!INCLUDEssNoVersion], 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 [!INCLUDEssazuresynapse-md] or [!INCLUDEssPDW], use the name sys.dm_pdw_nodes_os_memory_brokers. [!INCLUDEsynapse-analytics-od-unsupported-syntax]
| 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 [!INCLUDEssNoVersion], listed below with their descriptions. MEMORYBROKER_FOR_CACHE : Memory that is allocated for use by cached objects (Not Buffer Pool cache). MEMORYBROKER_FOR_STEAL : Memory that is stolen from the buffer pool. This memory is tracked by memory clerks and 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 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: [!INCLUDEssazuresynapse-md], [!INCLUDEssPDW] The identifier for the node that this distribution is on. |
On [!INCLUDEssNoVersion_md] and SQL Managed Instance, requires VIEW SERVER STATE permission.
On SQL Database Basic, S0, and S1 service objectives, and for databases in elastic pools, the server admin account, the Microsoft Entra admin account, or membership in the ##MS_ServerStateReader## server role is required. On all other SQL Database service objectives, either the VIEW DATABASE STATE permission on the database, or membership in the ##MS_ServerStateReader## server role is required.
Requires VIEW SERVER PERFORMANCE STATE permission on the server.
SQL Server Operating System Related Dynamic Management Views (Transact-SQL)