SQL Server Ring Buffer--Ring_buffer_memory_broker diagnosing internal memory pressure

SQL Server Ring buffer--Ring_buffer_memory_broker diagnosing internal memory pressure
Memory Broker

The responsibility of memory broker is to allocate memory between large memory consumers based on their requirements. Memory Broker is a sqlos component, but tightly coupled with a buffer pool. In addition, memory broker will only take into account the memory that is controlled by the memory manager of the buffer pool. Memory Broker monitors the memory requirements of the buffer pool and the memory consumed by the large memory consumer. Based on the information collected, it estimates the "optimal" memory distribution for each consumer and broadcasts this information to consumers. Each consumer will use this information accordingly to accommodate its memory usage.

SQL Server 2005 has 4 main memory consumers: the buffer pool's data cache, the query execution engine (sometimes referred to as workbench Memory, Workspace memories), the cache, and the compilation engine. In SQL Server 2005, only the latter 3 are monitored by the memory broker.

The user can monitor the current, expected, and recommended memory distribution of memory broker by querying the ring buffer of memory broker.

SELECT * from sys.dm_os_ring_bufferwherering_buffer_type = ' Ring_buffer_memory_broker '

Note that the ring buffer for memory broker is only updated when the memory broker wants the behavior of a particular component to change, such as shrinking, growing, or remaining stable.

The user can use DBCC MEMORYSTATUS to find the last memory broker notification.

Internal Memory Pressure

When internal memory pressure is detected, a low memory notification that allocates memory for the component in the cache pool is opened. Turning on low memory notifications allows pages to be recycled from caches and other components that use the cache pool.

Internal memory pressure can be triggered by adjusting the max server memory option or when the stolen page is more than 80% proportional to the cache pool.

Internal memory Pressure notification (' Shrink ') can be found by querying ring buffer's call using the following code.

Selectx.value (' (//record/@time) [1] ', ' bigint ') as [Time Stamp],x.value (' (//notification) [1] ', ' varchar ') ' as [ Last Notification]from (select cast, record as XML) from sys.dm_os_ring_bufferswhere ring_buffer_type = ' Ring_buffer_ Memory_broker ') as R (x) Order by[time Stamp] desc

650) this.width=650; "title=" clip_image001 "style=" Border-top:0px;border-right:0px;border-bottom:0px;border-left : 0px; "alt=" clip_image001 "src=" http://s3.51cto.com/wyfs02/M01/54/1E/wKiom1R4QbSzOEEVAAN3QhNCT80971.jpg "height=" 215 "border=" 0 "/>

The following are common scripts:

/*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 ')  as Broker           , CAST (Record as xml). Value ('/record[1]/memorybroker[1]/notification[1] ', ' varchar ')  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 ')  ASC ,             cast (Record as XML). Value (' /record[1]/memorybroker[1]/notification[1] ', ' varchar ')  ASC

This article is from the "Dripping Stone Wear" blog, please be sure to keep this source http://ultrasql.blog.51cto.com/9591438/1584257

SQL Server Ring Buffer--Ring_buffer_memory_broker diagnosing internal memory pressure