SQL Server 环形缓冲区（Ring Buffer） -- RING_BUFFER_MEMORY_BROKER 诊断内部内存压力

封云亭

/*　　
RING_BUFFER_MEMORY_BROKER
　　
  Identity internal memory pressure.
　　
  Look at the 3 consumers of Memory
　　
  for SHRINK Notification
　　
  1.cache
　　
  2.query compilations
　　
  3.query executions
　　
  */
　　
select
　　
    CAST(record AS XML)
　　
    ,CAST(record AS XML).value('/Record[1]/@time[1]','bigint') as time
　　
    ,CAST(record as XML).value('/Record[1]/MemoryBroker[1]/Broker[1]','varchar(50)') as Broker
　　
    ,CAST(record as XML).value('/Record[1]/MemoryBroker[1]/Notification[1]','varchar(20)') as Notification
　　
    ,CAST(record as XML).value('/Record[1]/MemoryBroker[1]/MemoryRatio[1]','int') as MemoryRatio
　　
from
　　
    sys.dm_os_ring_buffers
　　
where
　　
    ring_buffer_type = 'RING_BUFFER_Memory_broker'
　　
order by
　　
    CAST(record as XML).value('/Record[1]/MemoryBroker[1]/Broker[1]','varchar(50)') asc,
　　
    CAST(record as XML).value('/Record[1]/MemoryBroker[1]/Notification[1]','varchar(20)') asc