مشاركة عبر

  • مقالة

sys.dm_os_memory_brokers (Transact-SQL)

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.

Column name

Data type

Description

memory_broker_type

nvarchar(60)

Type of memory broker. There are currently three types of memory brokers in SQL Server.

ValueDescription
MEMORYBROKER_FOR_CACHEMemory that is allocated for use by cached objects.
MEMORYBROKER_FOR_STEALMemory 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_RESERVEMemory 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_per_sec

int

Current rate of allocation that is attributed to this broker.

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.

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

pool_id

int

ID of the resource pool if it is associated with a Resource Governor pool.

allocations_kb_per_sec

bigint

Rate of memory allocations in kilobytes (KB) per second. This value can be negative for memory deallocations.

predicated_allocations_kb

bigint

Predicted amount of allocated memory by the broker. This is based on the memory usage pattern.

overall_limit_kb

bigint

Maximum amount of memory, in kilobytes (KB), that the the broker can allocate.

Permissions

Requires VIEW SERVER STATE permission on the server.