[SQL Server] Mémoire/DMV : les ring buffers, ou une autre façon de détecter les problèmes de mémoire

Ce billet présente brièvement les ring buffers (ou mémoire tampon en anneau).

N’hésitez pas à jeter un coup d’œil ici, si vous souhaitez comprendre l’architecture de la mémoire au sein de SQL Server.

Les ring buffers : késako ?

Les ring buffers sont des mécanismes permettant de capturer diverses informations sur l’état du fonctionnement interne de SQL Server (i.e., mémoire, exceptions, ordonnanceurs, connectivité,…). Il existe différents ring buffers, dont voici les plus utiles pour l’étude de la mémoire :

Ring buffer Description
RING_BUFFER_RESOURCE_MONITOR Permet d’observer tous les changements d’état de la mémoire.
RING_BUFFER_SINGLE_PAGE_ALLOCATOR Permet d’observer le comportement de l’allocateur de pages de mémoire simples (8 Ko) sous la pression de la mémoire interne.
RING_BUFFER_BUFFER_POOL Renvoie des informations sur les incidents au niveau du buffer pool.
RING_BUFFER_OOM Permet d’obtenir des informations sur les conditions de manque de mémoire (OOM signifie « Out Of Memory »).
RING_BUFFER_MEMORY_BROKER Renvoie des informations sur la distribution de la mémoire à travers chaque composant de mémoire (clercs,…) dans le besoin. Par ailleurs, RING_BUFFER_MEMORY_BROKER_CLERKS permet de récupérer des informations utiles sur les clercs de mémoire.
RING_BUFFER_SCHEDULER_MONITOR Permet d’obtenir des informations sur l’état général du serveur.
La volumétrie de chaque information fournie par un ring buffer est de l’ordre de 64 Ko.

Comment questionner les ring buffers pour l’audit de la mémoire ?

Pour questionner les ring buffers, la DMV suivante est d’une grande utilité : sys.dm_os_ring_buffers. Voici quelques exemples (indépendants) d’utilisation de certains ring buffers pour l’analyse de la mémoire :

  • RING_BUFFER_RESOURCE_MONITOR :
WITH RingBuffer
 AS ( SELECT CAST(rb.record AS XML) AS MemoryRecord ,
 rb.timestamp
 FROM sys.dm_os_ring_buffers AS rb
 WHERE rb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
 )
 SELECT mr.value('(ResourceMonitor/Notification)[1]', 'NVARCHAR(128)') AS Notification ,
 mr.value('(ResourceMonitor/IndicatorsProcess)[1]', 'SMALLINT') AS IndicatorsProcess ,
 mr.value('(ResourceMonitor/IndicatorsSystem)[1]', 'SMALLINT') AS IndicatorsSystem ,
 DATEADD(ms, -1 * si.ms_ticks - rb.timestamp, GETDATE()) AS DateTime
 FROM RingBuffer AS rb
 CROSS
APPLY rb.MemoryRecord.nodes('Record') record ( mr )
 CROSS
JOIN sys.dm_os_sys_info AS si
 ORDER BY DateTime DESC

GO

Les informations affichées dans chaque colonne sont toutes utiles.

  • Notification permet d’avoir une idée de l’état de la mémoire. Si l’information affichée est RESOURCE_MEMPHYSICAL_LOW alors il y a un manque de mémoire. Le cas échéant, RESOURCE_MEMPHYSICAL_HIGH sera affiché. La notification RESOURCE_MEM_STEADY, quant à elle, indique une stabilisation.
  • IndicatorsProcess combiné avec IndicatorsSystem permet de savoir si la pression de la mémoire vient du système ou non. Si la valeur d’IndicatorsProcess est à 0 et celle d’IndicatorsSystem retourne une valeur autre que 0, alors le système est incriminé. Pour information, une valeur égale à :
    • 1 signifie qu’il y a une quantité de mémoire élevée.
    • 2 signifie qu’il y a peu de mémoire.
    • 4 signifie que la mémoire virtuelle est basse.
  • DateTime permet de suivre les changements récents d’état de la mémoire au fil du temps.

Il est bien sûr possible de récupérer des informations supplémentaires avec RING_BUFFER_RESOURCE_MONITOR comme le pourcentage de la mémoire utilisée, la quantité – en KB – de pages de mémoire (simples ou multiples) utilisées, la quantité de pages de mémoire disponible, des informations sur le page file, sur la VAS,…

WITH RingBuffer
 AS ( SELECT CAST(rb.record AS XML) AS MemoryRecord ,
 rb.timestamp
 FROM sys.dm_os_ring_buffers AS rb
 WHERE rb.ring_buffer_type = 'RING_BUFFER_RESOURCE_MONITOR'
 )
 SELECT mr.value('(ResourceMonitor/Notification)[1]', 'NVARCHAR(128)') AS Notification ,
 mr.value('(ResourceMonitor/IndicatorsProcess)[1]', 'SMALLINT') AS IndicatorsProcess ,
 mr.value('(ResourceMonitor/IndicatorsSystem)[1]', 'SMALLINT') AS IndicatorsSystem ,
 mr.value('(//Record/MemoryRecord/MemoryUtilization)[1]', 'bigint') AS MemoryUtilizationPercent ,
 mr.value('(//Record/MemoryNode/@id)[1]', 'bigint') AS [Node Id] ,
 mr.value('(//Record/MemoryNode/ReservedMemory)[1]', 'bigint') AS SQL_ReservedMemory_KB ,
 mr.value('(//Record/MemoryNode/CommittedMemory)[1]', 'bigint') AS SQL_CommittedMemory_KB ,
 mr.value('(//Record/MemoryNode/AWEMemory)[1]', 'bigint') AS SQL_AWEMemory ,
 mr.value('(//Record/MemoryNode/SinglePagesMemory)[1]', 'bigint') AS SinglePagesMemory ,
 mr.value('(//Record/MemoryNode/MultiplePagesMemory)[1]', 'bigint') AS MultiplePagesMemory ,
 mr.value('(//Record/MemoryRecord/TotalPhysicalMemory)[1]',
 'bigint') AS TotalPhysicalMemory_KB ,
 mr.value('(//Record/MemoryRecord/AvailablePhysicalMemory)[1]',
 'bigint') AS AvailablePhysicalMemory_KB ,
 mr.value('(//Record/MemoryRecord/TotalPageFile)[1]', 'bigint') AS TotalPageFile_KB ,
 mr.value('(//Record/MemoryRecord/AvailablePageFile)[1]', 'bigint') AS AvailablePageFile_KB ,
 mr.value('(//Record/MemoryRecord/TotalVirtualAddressSpace)[1]',
 'bigint') AS TotalVirtualAddressSpace_KB ,
 mr.value('(//Record/MemoryRecord/AvailableVirtualAddressSpace)[1]',
 'bigint') AS AvailableVirtualAddressSpace_KB ,
 DATEADD(ms, -1 * si.ms_ticks - rb.timestamp, GETDATE()) AS DateTime
 FROM RingBuffer AS rb
 CROSS
APPLY rb.MemoryRecord.nodes('Record') record ( mr )
 CROSS
JOIN sys.dm_os_sys_info AS si
 ORDER BY DateTime DESC

GO
  • RING_BUFFER_MEMORY_BROKER_CLERKS :
WITH RingBuffer
 AS ( SELECT CAST(rb.record AS XML) AS MemoryRecord ,
 rb.timestamp
 FROM sys.dm_os_ring_buffers AS rb
 WHERE rb.ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER_CLERKS'
 )
 SELECT mr.value('(MemoryBrokerClerk/Name)[1]', 'NVARCHAR(128)') AS Notification ,
 mr.value('(MemoryBrokerClerk/TotalPages)[1]', 'INT') AS TotalPages ,
 DATEADD(ms, -1 * si.ms_ticks - rb.timestamp, GETDATE()) AS DateTime
 FROM RingBuffer AS rb
 CROSS
APPLY rb.MemoryRecord.nodes('Record') record ( mr )
 CROSS
JOIN sys.dm_os_sys_info AS si
 ORDER BY DateTime DESC

GO

Avec le ring buffer RESOURCE_MEMORY_BROKER_CLERKS, il est possible de connaître, en fonction du temps, le nombre total de pages de mémoire pour les clercs de mémoire. Pour rappel, les clercs de mémoire sont des interfaces utilisées par les allocateurs de pages pour « communiquer » avec le buffer pool.

  • RING_BUFFER_MEMORY_BROKER :
SELECT TOP 3
 timestamp ,
 record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_MEMORY_BROKER'
ORDER BY timestamp DESC

GO

Les valeurs obtenues sont soit GROW, soit SHRINK, soit STABLE. Si les notifications les plus récentes (supposons, les 3 dernières dans notre cas) sont à GROW, cela indique qu’il n’y a, à l’instant T, pas de problèmes de consommation de mémoire à partir du buffer pool.

Pour aller plus loin…

Vous pouvez taper la commande suivante pour avoir un maximum d’informations sur les différents ring buffers disponibles :

SELECT *
FROM sys.dm_os_ring_buffers
GO

Et vous pouvez également garder un oeil ici si vous souhaitez consulter les billets relatifs à la mémoire.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s