MEMORYBROKER_FOR_STEAL vs MEMORYBROKER_FOR_CACHE

Question

what doesnt these two memory brokers do ??
MEMORYBROKER_FOR_STEAL <<< steal from buffer pool , but steal for what? what's the destination of this steal ?

Answer 1

Hi @sakuraime ,

There are three types of memory brokers.

MEMORYBROKER_FOR_CACHE – Memory allocations used by cached objects((Not Buffer Pool cache).
MEMORYBROKER_FOR_STEAL – Memory used for compilations. Stolen from buffer pool.
MEMORYBROKER_FOR_RESERVE – Memory used for query execution.

Refer to this blog sys.dm_os_memory_brokers.

Best regards,
Cathy

===============================================

If the response helped, do "Accept Answer" and upvote it.

Answer 2

I focus on MEMORYBROKER_FOR_STEAL. This broker tracks memory which is taken from buffer pool for operations/queries which require sorting or hash operations. Old Support article describes stolen memory as

Stolen memory describes buffers that are in use for sorting or for hashing operations (query workspace memory), or for those buffers that are being used as a generic memory store for allocations to store internal data structures such as locks, transaction context, and connection information. The lazywriter process is not permitted to flush Stolen buffers out of the buffer pool.

In a simple term if your query uses sort operation or hash operation it may need stolen memory which it takes from buffer pool and later returns it.

OTOH the MEMORYBROKER_FOR_CACHE is basically tracking various caches

Please note Brokers are basically memory monitors and based on their analysis memory to various components are allocated via memory manager