Partager via

Surveiller l’utilisation de la mémoire

  • Article

S’applique à : SQL Server

Surveillez périodiquement une instance de SQL Server pour vous assurer que l’utilisation de la mémoire reste dans des limites normales.

Configurer la mémoire maximale de SQL Server

Par défaut, une instance SQL Server peut, au fil du temps, consommer la majeure partie de la mémoire disponible du système d’exploitation Windows dans le serveur. Une fois la mémoire acquise, elle n’est libérée que si une sollicitation de la mémoire est détectée. Ce comportement est normal et n’est pas l’indication d’une fuite de mémoire dans le processus de SQL Server. Utilisez l’option max server memory pour limiter la quantité de mémoire que SQL Server est autorisé à acquérir pour la plupart de ses utilisations. Pour plus d’informations, consultez le guide d’architecture de gestion de la mémoire.

Dans SQL Server sur Linux, définissez la limite de la mémoire avec l’outil mssql-conf et le paramètre memory.memorylimitmb.

Surveiller la mémoire du système d’exploitation

Pour surveiller une condition de mémoire insuffisante, utilisez les compteurs de serveur Windows suivants. Utilisez les vues de gestion dynamique sys.dm_os_process_memory et sys.dm_os_sys_memory pour interroger de nombreux compteurs de mémoire de système d’exploitation.

  • Mémoire : Octets disponibles
    Ce compteur indique combien d’octets de mémoire sont actuellement disponibles pour les processus. Un compteur Octets disponibles avec des valeurs faibles peut indiquer une pénurie globale de la mémoire du système d’exploitation. Vous pouvez interroger cette valeur en T-SQL avec sys.dm_os_sys_memory.available_physical_memory_kb.

  • Mémoire : Pages/s
    Ce compteur indique le nombre de pages qui ont été extraites du disque en raison de défauts de page ou écrites sur le disque pour libérer de l’espace dans la plage de travail en raison de défauts de page. Une valeur élevée du compteur Pages/s peut indiquer une pagination excessive.

  • Mémoire : Défauts de page/s Ce compteur indique le taux de défauts de page pour tous les processus, y compris les processus système. Même si l’ordinateur a beaucoup de mémoire à sa disposition, il est normal d’avoir un taux de pagination sur disque faible, mais différent de zéro (d’où des défauts de page). Le gestionnaire de mémoire virtuelle de Microsoft Windows soustrait des pages à SQL Server et à d’autres processus quand il réduit la taille des parties actives de ces processus. Son activité a tendance à provoquer des défauts de page.

  • Processus : Défauts de page/s Ce compteur indique le taux de défauts de page pour un processus utilisateur donné. Surveillez Processus : Défauts de page/s pour déterminer si l’activité du disque est due à la pagination par SQL Server. Pour déterminer si SQL Server ou un autre processus provoque une pagination excessive, surveillez le compteur Processus : Défauts de page/s pour l’instance du processus SQL Server.

Pour plus d’informations sur la résolution d’une pagination excessive, consultez la documentation du système d’exploitation.

Isoler la mémoire utilisée par SQL Server

Pour surveiller l’utilisation de la mémoire de SQL Server, utilisez les compteurs d’objets SQL Server suivants. Utilisez les vues de gestion dynamique sys.dm_os_performance_counters et sys.dm_os_process_memory pour interroger de nombreux compteurs d’objets SQL Server.

Par défaut, SQL Server gère ses besoins de mémoire de façon dynamique en fonction des ressources système disponibles. Si SQL Server a besoin de plus de mémoire, il demande au système d’exploitation de déterminer si de la mémoire physique libre est disponible. Dans l’affirmative, il l’utilise. Si la mémoire disponible est insuffisante pour le système d’exploitation, SQL Server libère de la mémoire sur le système d’exploitation jusqu’à ce que la condition de mémoire insuffisante soit atténuée ou que SQL Server atteigne la limite min server memory. Vous pouvez cependant ignorer cette option pour utiliser dynamiquement de la mémoire au moyen des options de configuration du serveur suivantes : min server memory et max server memory. Pour plus d'informations, consultez Options de mémoire du serveur.

Pour surveiller la mémoire utilisée par SQL Server, examinez les compteurs de performances suivants :

  • SQL Server: Memory Manager: Total Server Memory (KB)
    Ce compteur indique la quantité de mémoire du système d’exploitation que le gestionnaire de mémoire SQL Server a actuellement alloué à SQL Server. Ce nombre doit augmenter en fonction des besoins de l’activité réelle et augmente après le démarrage de SQL Server. Interrogez ce compteur à l’aide de la vue de gestion dynamique sys.dm_os_sys_info et observez la colonne committed_kb.

  • SQL Server: Memory Manager: Target Server Memory (KB)
    Ce compteur indique la quantité de mémoire idéale que SQL Server peut consommer, en fonction de la charge de travail récente. Comparez-le à la Mémoire totale du serveur après une période de fonctionnement normal pour déterminer si la quantité de mémoire allouée à SQL Server convient. Après une période de fonctionnement normal, Mémoire totale du serveur et Mémoire du serveur cible doivent être similaires. Si la Mémoire totale du serveur est nettement inférieure à la Mémoire du serveur cible, l’instance de SQL Server peut faire l’objet d’une sollicitation de la mémoire. Pendant un certain temps après le démarrage de SQL Server, la Mémoire totale du serveur est censée être inférieure à la Mémoire du serveur cible, dans la mesure où la Mémoire totale du serveur augmente. Interrogez ce compteur à l’aide de la vue de gestion dynamique sys.dm_os_sys_info et observez la colonne committed_target_kb. Pour obtenir plus d’informations et les bonnes pratiques relatives à la configuration de la mémoire, consultez les options de configuration de la mémoire du serveur.

  • Process: Working Set
    Ce compteur indique la quantité de mémoire physique actuellement utilisée par un processus, selon le système d’exploitation. Observez l’instance sqlservr.exe de ce compteur. Interrogez ce compteur à l’aide de la vue de gestion dynamique sys.dm_os_process_memory et observez la colonne physical_memory_in_use_kb.

  • Processus : Octets privés
    Ce compteur indique la quantité de mémoire demandée par un processus au système d’exploitation pour son propre usage. Observez l’instance sqlservr.exe de ce compteur. Étant donné que ce compteur inclut toutes les allocations de mémoire demandées par sqlservr.exe, notamment celles non limitées par l’option max server memory, ce compteur peut signaler des valeurs supérieures à l’option max server memory.

  • SQL Server: Buffer Manager: Database Pages
    Ce compteur indique le nombre de pages dans le pool de mémoires tampons avec le contenu de la base de données. N’inclut pas la mémoire des pools sans tampon au sein du processus SQL Server. Utilisez la vue de gestion dynamique sys.dm_os_performance_counters pour interroger ce compteur.

  • SQL Server: Buffer Manager: Buffer Cache Hit Ratio
    Ce compteur est spécifique à SQL Server. Un ratio de 90 ou plus est souhaitable. Une valeur supérieure à 90 indique que plus de 90 % de toutes les requêtes ont été satisfaites à partir de la mémoire cache sans avoir à lire sur le disque. Pour plus d’informations sur l’objet SQL Server Buffer Manager, consultez SQL Server, objet Buffer Manager. Utilisez la vue de gestion dynamique sys.dm_os_performance_counters pour interroger ce compteur.

  • SQL Server: Buffer Manager: Page life expectancy
    Ce compteur mesure la durée en secondes pendant laquelle la page la plus ancienne reste dans le pool de mémoires tampons. Pour les systèmes qui utilisent une architecture NUMA, il s’agit de la moyenne sur tous les nœuds NUMA. Une valeur élevée et croissante est préférable. Une chute soudaine indique une attrition importante des données dans et hors du pool de mémoires tampons, ce qui signifie que la charge de travail n’a pas pu tirer pleinement parti des données déjà en mémoire. Chaque nœud NUMA a son propre nœud du pool de mémoires tampons. Sur les serveurs contenant plusieurs nœuds NUMA, vous pouvez voir l’espérance de vie d’une page de chaque nœud du pool de mémoires tampons avec SQL Server: Buffer Node: Page life expectancy. Utilisez la vue de gestion dynamique sys.dm_os_performance_counters pour interroger ce compteur.

Exemples

Déterminer l’allocation de mémoire actuelle

Les requêtes suivantes retournent des informations sur la mémoire actuellement allouée.

SELECT
(total_physical_memory_kb/1024) AS Total_OS_Memory_MB,
(available_physical_memory_kb/1024)  AS Available_OS_Memory_MB
FROM sys.dm_os_sys_memory;

SELECT  
(physical_memory_in_use_kb/1024) AS Memory_used_by_Sqlserver_MB,  
(locked_page_allocations_kb/1024) AS Locked_pages_used_by_Sqlserver_MB,  
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,
process_physical_memory_low,  
process_virtual_memory_low  
FROM sys.dm_os_process_memory;

Déterminer l’utilisation actuelle de la mémoire par SQL Server

La requête suivante retourne des informations sur l’utilisation actuelle de la mémoire par SQL Server.

SELECT
sqlserver_start_time,
(committed_kb/1024) AS Total_Server_Memory_MB,
(committed_target_kb/1024)  AS Target_Server_Memory_MB
FROM sys.dm_os_sys_info;

Déterminer l’espérance de vie d’une page

La requête suivante utilise sys.dm_os_performance_counters pour observer la valeur actuelle d’espérance de vie d’une page de l’instance SQL Server au niveau du gestionnaire de tampons global et au niveau de chaque nœud NUMA.

SELECT
CASE instance_name WHEN '' THEN 'Overall' ELSE instance_name END AS NUMA_Node, cntr_value AS PLE_s
FROM sys.dm_os_performance_counters    
WHERE counter_name = 'Page life expectancy';

Contenu connexe