SQL Ring Buffers

• SQLSACT

  SSC-Insane

  Points: 21838

  More actions

  March 14, 2013 at 2:24 am

  #291052

  Hi All

  When analyzing SQL's ring buffers using the below

  SET QUOTED_IDENTIFIER ON

  SELECT

  EventTime,

  n.value('(Pool)[1]', 'int') AS [Pool],

  n.value('(Broker)[1]', 'varchar(40)') AS [Broker],

  n.value('(Notification)[1]', 'varchar(40)') AS [Notification],

  n.value('(MemoryRatio)[1]', 'int') AS [MemoryRatio],

  n.value('(NewTarget)[1]', 'int') AS [NewTarget],

  n.value('(Overall)[1]', 'int') AS [Overall],

  n.value('(Rate)[1]', 'int') AS [Rate],

  n.value('(CurrentlyPredicted)[1]', 'int') AS [CurrentlyPredicted],

  n.value('(CurrentlyAllocated)[1]', 'int') AS [CurrentlyAllocated]

  FROM (

  SELECT

  DATEADD (ss, (-1 * ((cpu_ticks / CONVERT (float, ( cpu_ticks / ms_ticks ))) - [timestamp])/1000), GETDATE()) AS EventTime,

  CONVERT (xml, record) AS record

  FROM sys.dm_os_ring_buffers

  CROSS JOIN sys.dm_os_sys_info

  WHERE ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER') AS t

  CROSS APPLY record.nodes('/Record/MemoryBroker') AS x(n)

  ORDER BY EventTime DESC;

  Which broker would indicate pressure in the data cache portion of the buffer pool?

  I think that the MEMORYBROKER_FOR_CACHE reports on the plan cache portion of the buffer pool, is this right?

  Thanks