Surveiller l’utilisation de la mémoire

  • Article

S’applique à :SQL Server

Surveillez régulièrement une instance de SQL Server pour vérifier que l’utilisation de la mémoire se trouve dans des plages classiques.

Configurer la mémoire maximale DE SQL Server

Par défaut, une instance SQL Server peut consommer la plupart de la mémoire du système d’exploitation Windows disponible sur 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. Cela est par conception et n’indique pas de fuite de mémoire dans le processus SQL Server. Utilisez l’option max server memory pour limiter la quantité de mémoire que SQL Server est autorisée à 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 : Erreurs de page/s Ce compteur indique le taux d’erreurs 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 Microsoft Windows (VMM) prend des pages de SQL Server et d’autres processus au fur et à mesure qu’il supprime les tailles de ces processus. Son activité a tendance à provoquer des défauts de page.

  • Processus : Erreurs de page/s Ce compteur indique le taux d’erreurs de page pour un processus utilisateur donné. Processus de surveillance : erreurs de page/s pour déterminer si l’activité de disque est provoquée par la pagination par SQL Server. Pour déterminer si SQL Server ou un autre processus est la cause d’une pagination excessive, surveillez le processus : erreurs de page/s compteur pour l’instance de 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 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 dynamiquement ses besoins en mémoire, en fonction des ressources système disponibles. Si SQL Server a besoin de plus de mémoire, il interroge le système d’exploitation pour déterminer si la mémoire physique libre est disponible et utilise la mémoire disponible. S’il existe une mémoire faible 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 faible soit réduite ou jusqu’à ce que SQL Server atteigne la limite minimale de mémoire du serveur. 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 quantité de 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 validée sur 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 : Gestionnaire de mémoire : Mémoire cible (Ko)
    Ce compteur indique une quantité idéale de mémoire que SQL Server peut consommer, en fonction de la charge de travail récente. Comparez à la mémoire totale du serveur après une période d’opération classique pour déterminer si SQL Server a une quantité souhaitée de mémoire allouée. 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 SQL Server peut subir une pression de mémoire. Pendant une période après le démarrage de SQL Server, la mémoire totale du serveur devrait être inférieure à la mémoire du serveur cible, car 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 , en observant la physical_memory_in_use_kb colonne.

  • 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, y compris celles qui ne sont pas limitées par l’option de mémoire maximale du serveur, 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 : Gestionnaire de mémoires tampons : Espérance de vie des pages
    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 avec plusieurs nœuds NUMA, affichez l’espérance de vie de chaque nœud de pool de mémoires tampons à l’aide de SQL Server : Nœud de mémoire tampon : Espérance de vie de page. 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 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 des pages

La requête suivante utilise sys.dm_os_performance_counters pour observer la valeur actuelle de l’espérance de vie de page de l’instance SQL Server au niveau global du gestionnaire de mémoires tampons et à chaque niveau de 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