Résoudre les problèmes de mémoire insuffisante ou de mémoire faible dans SQL Server

Résumé

Cet article explique comment résoudre les problèmes de mémoire SQL Server, notamment l’absence de mémoire et l’échec de l’allocation des erreurs de mémoire, des conditions de mémoire insuffisantes et des problèmes de performances connexes. Il explique les symptômes, les trois principales catégories de pression de la mémoire (externes, internes à partir de modules non moteurs et internes de SQL Server composants moteur), les outils de diagnostic que vous pouvez utiliser pour collecter des données et les étapes que vous pouvez prendre pour corriger ou soulager la pression de la mémoire sur une instance de SQL Server.

Symptômes des erreurs dues à une mémoire insuffisante

SQL Server utilise une architecture de mémoire complexe qui correspond à l’ensemble de fonctionnalités complexe et riche. En raison de la variété des besoins en mémoire, de nombreuses sources peuvent entraîner une consommation de mémoire et une sollicitation de la mémoire, ce qui peut entraîner des conditions de mémoire insuffisantes.

Les erreurs courantes indiquent une mémoire faible dans SQL Server. Voici quelques exemples de ces erreurs :

• 701 : Échec de l’allocation de mémoire suffisante pour exécuter une requête.
• 802: Impossible d’obtenir la mémoire nécessaire pour allouer des pages dans le pool de tampons (données ou pages d’indexation).
• 1204 : Échec de l’allocation de mémoire pour les verrous.
• 6322 : Échec de l’allocation de mémoire pour l’analyseur XML.
• 6513 : Échec de l’initialisation du CLR en raison d’une saturation de la mémoire.
• 6533 : AppDomain déchargé en raison d’une mémoire insuffisante.
• 8318 : Échec de chargement des compteurs de performances SQL en raison d’une mémoire insuffisante.
• 8356 ou 8359 : échec de l’exécution de la trace ETW ou SQL en raison d’une mémoire insuffisante.
• 8556 : Échec du chargement de MSDTC en raison d’une mémoire insuffisante.
• 8645 : Échec de l’exécution d’une requête faute de mémoire disponible pour les attributions de mémoire (tri et hachage).
• 8902 : Échec de l’allocation de mémoire pendant l’exécution de DBCC.
• 9695 ou 9696 : Échec de l’allocation de mémoire pour les opérations Service Broker.
• 17131 ou 17132 : Échec du démarrage du serveur en raison d’une mémoire insuffisante.
• 17890 : Échec de l’allocation de mémoire parce que la mémoire SQL a été paginée sur le disque par le système d’exploitation.
• 18053 : L’erreur est imprimée en mode terse, car une erreur s’est produite lors de la mise en forme. Le traçage, ETW et les notifications sont omis.
• 22986 ou 22987 : Échecs de capture de données modifiées en raison d’une mémoire insuffisante.
• 25601 : le moteur Xevent est hors mémoire.
• 26053 : les interfaces réseau SQL ne parviennent pas à s’initialiser en raison d’une mémoire insuffisante.
• 30085, 30086, 30094 : échec des opérations de texte intégral SQL en raison d’une mémoire insuffisante.

Cause de problèmes de mémoire faible

De nombreux facteurs peuvent entraîner une mémoire insuffisante. Ces facteurs incluent les paramètres du système d’exploitation, la disponibilité de la mémoire physique, les composants qui utilisent la mémoire à l’intérieur de SQL Server et les limites de mémoire sur la charge de travail actuelle. Dans la plupart des cas, la requête qui échoue avec une erreur hors mémoire n’est pas la cause de l’erreur. Vous pouvez regrouper les causes en trois catégories.

Pression de la mémoire externe ou du système d’exploitation

La pression externe fait référence à une utilisation élevée de la mémoire d’un composant en dehors du processus de SQL Server qui entraîne une mémoire insuffisante pour SQL Server. Vérifiez si d’autres applications sur le système consomment de la mémoire et contribuent à une faible disponibilité de la mémoire. SQL Server est l’une des rares applications conçues pour répondre à la pression de la mémoire du système d’exploitation en réduisant son utilisation de la mémoire. Si une application ou un pilote demande de la mémoire, le système d’exploitation envoie un signal à toutes les applications pour libérer de la mémoire et SQL Server répond en réduisant son propre utilisation de la mémoire. Peu d’autres applications répondent parce qu’elles ne sont pas conçues pour écouter cette notification. Lorsque SQL Server réduit son utilisation de la mémoire, son pool de mémoire diminue et les composants qui ont besoin de mémoire peuvent ne pas l’obtenir. Par conséquent, vous commencez à obtenir 701 ou d’autres erreurs liées à la mémoire. Pour plus d’informations sur la façon dont SQL Server alloue et libère de la mémoire dynamiquement, consultez SQL Server’architecture de mémoire. Pour obtenir des diagnostics et des solutions détaillés, consultez la pression de la mémoire externe dans cet article.

Trois grandes catégories de problèmes peuvent entraîner une sollicitation de la mémoire du système d’exploitation :

• Problèmes liés à l’application : une ou plusieurs applications épuisent la mémoire physique disponible. Le système d’exploitation répond aux nouvelles demandes d’application pour les ressources en essayant de libérer de la mémoire. Recherchez les applications qui épuisent la mémoire et effectuez des étapes pour équilibrer la mémoire entre elles sans épuiser la RAM.
• Problèmes de pilote de périphérique : les pilotes de périphérique peuvent provoquer la pagination de l’ensemble de travail de tous les processus si un pilote appelle incorrectement une fonction d’allocation de mémoire.
• Problèmes de produit du système d’exploitation.

Pour obtenir une explication détaillée et des étapes de résolution des problèmes, consultez MSSQLSERVER_17890.

Pression interne de la mémoire provenant de modules non moteurs

La sollicitation interne de la mémoire fait référence à une faible disponibilité de la mémoire causée par des facteurs au sein du processus SQL Server. Certains composants qui s’exécutent à l’intérieur du processus SQL Server sont externes au moteur SQL Server. Par exemple, les fournisseurs OLE DB (DLL) tels que les serveurs liés, les procédures ou fonctions SQLCLR, les procédures étendues (XPs) et OLE Automation (sp_OA*). D’autres incluent des programmes antivirus ou de sécurité qui injectent des DLL dans le processus à des fins de surveillance. Un problème ou une conception médiocre dans l’un de ces composants peut entraîner une grande consommation de mémoire. Par exemple, considérez un serveur lié qui met en cache 20 millions de lignes de données d’une source externe en mémoire SQL Server. Du point de vue du moteur, aucun gestionnaire de mémoire ne signale une utilisation élevée de la mémoire, mais la mémoire consommée au sein du processus SQL Server est élevée. L’augmentation de mémoire provoquée par une DLL de serveur lié conduit SQL Server à réduire son utilisation de la mémoire et crée une situation de mémoire insuffisante pour les composants du moteur, ce qui entraîne des erreurs de mémoire insuffisante. Pour obtenir des diagnostics et des solutions détaillés, consultez la pression de mémoire interne des modules autres que moteurs.

Pression interne de la mémoire provenant des composants du moteur SQL Server

La pression sur la mémoire exercée par des composants au sein du moteur SQL Server peut également entraîner des erreurs de mémoire insuffisante. Des centaines de composants suivis à l’aide de memory clerks allouent de la mémoire dans SQL Server. Identifiez les commis de mémoire responsables des allocations les plus importantes pour résoudre le problème. Par exemple, si le OBJECTSTORE_LOCK_MANAGER memory clerk indique une allocation importante, déterminez pourquoi le Gestionnaire de verrous consomme autant de mémoire. Vous pouvez trouver des requêtes qui acquièrent de nombreux verrous. Optimisez ces requêtes à l’aide d’index, en raccourcissant les transactions qui contiennent des verrous pendant une longue période ou en vérifiant si l’escalade de verrous est désactivée. Chaque gestionnaire ou composant mémoire a sa propre façon d’utiliser la mémoire. Pour plus d’informations, consultez sys.dm_os_memory_clerks et les descriptions de type de commis de mémoire. Pour obtenir des diagnostics et des solutions détaillés, consultez l’utilisation de la mémoire interne par le moteur de SQL Server.

Types de pression de mémoire

Le graphique suivant illustre les types de pression qui peuvent entraîner des conditions de mémoire insuffisante dans SQL Server :

Outils de diagnostic pour résoudre les problèmes de mémoire

Utilisez les outils de diagnostic suivants pour collecter des données de dépannage.

Analyseur de performances

Configurez et collectez les compteurs suivants à l’aide de Analyseur de performances :

• Mémoire: Mégaoctets disponibles
• Processus : Ensemble de travail
• Processus : octets privés
• SQL Server : Gestionnaire de mémoire : (tous les compteurs)
• SQL Server : Gestionnaire de mémoire tampon : (tous les compteurs)

DMVs et DBCC MEMORYSTATUS

Utilisez sys.dm_os_memory_clerks ou DBCC MEMORYSTATUS pour observer l’utilisation globale de la mémoire dans SQL Server. sys.dm_os_memory_clerks retourne une ligne par commis de mémoire et est le meilleur point de départ pour trouver les composants qui consomment le plus de mémoire. DBCC MEMORYSTATUS renvoie un instantané plus détaillé qui regroupe les informations par gestionnaire de mémoire, pool de tampons et clercs mémoire.

Rapport consommation de mémoire dans SSMS

Pour afficher l’utilisation de la mémoire dans SQL Server Management Studio (SSMS) :

1. Ouvrez SSMS et connectez-vous à un serveur.
2. Dans Explorateur d'objets, sélectionnez et maintenez la touche enfoncée (ou cliquez avec le bouton droit) sur le nom de l’instance SQL Server.
3. Dans le menu contextuel, sélectionnez Rapports>Rapports standard>Consommation de mémoire.

PSSDiag ou SQL LogScout

Pour capturer automatiquement ces points de données, utilisez un outil tel que PSSDiag ou SQL LogScout.

• Si vous utilisez PSSDiag, configurez-le pour capturer le collecteur Perfmon et le collecteur Custom Diagnostics\SQL Memory Error.
• Si vous utilisez SQL LogScout, configurez-le pour capturer le scénario de mémoire .

Secours rapide pour libérer de la mémoire

Les actions suivantes peuvent libérer de la mémoire et la rendre disponible pour SQL Server. Utilisez-les comme soulagement à court terme pendant que vous examinez la cause racine.

Modifier la configuration de la mémoire

Vérifiez les paramètres de configuration de la mémoire SQL Server suivants et envisagez si possible d’augmenter Mémoire maximum du serveur :

• Mémoire maximum du serveur
• Mémoire minimum du serveur

Si vous n’avez pas défini la mémoire maximale du serveur, en particulier lorsque les pages de verrouillage en mémoire sont activées, définissez-la sur une valeur spécifique pour laisser de la mémoire pour le système d’exploitation. Pour plus d’informations, consultez l’option de configuration du serveur Verrouiller les pages en mémoire (LPIM ).

Modifier ou déplacer la charge de travail

Passez en revue la charge de travail de requête, y compris le nombre de sessions simultanées et les requêtes en cours d’exécution. Vérifiez si vous pouvez arrêter temporairement les applications moins critiques ou les déplacer vers une autre instance de SQL Server.

Pour les charges de travail en lecture seule, envisagez de les déplacer vers un réplica secondaire en lecture seule dans un environnement Always On. Pour plus d’informations, consultez Décharger les charges de travail en lecture seule vers le réplica secondaire d’un groupe de disponibilité Always On et Configurer l’accès en lecture seule sur un réplica secondaire d’un groupe de disponibilité Always On.

Vérifier la configuration de la mémoire de la machine virtuelle

Si vous exécutez SQL Server sur une machine virtuelle, vérifiez que l'hôte ne dépasse pas la mémoire de la machine virtuelle. Pour obtenir des conseils sur le dimensionnement de la mémoire pour SQL Server sur les machines virtuelles Azure, consultez les meilleures pratiques en matière de mémoire pour SQL Server sur les machines virtuelles Azure. Pour les machines virtuelles hébergées par VMware, consultez la documentation de votre fournisseur d’hyperviseur sur la détection et l’évitement de dépassement de mémoire.

Libérer de la mémoire à l’intérieur de SQL Server

Exécutez une ou plusieurs des commandes DBCC suivantes pour libérer SQL Server caches de mémoire. Utilisez ces commandes avec prudence sur les systèmes de production, car elles effacent les caches qui doivent être remplis à nouveau :

• DBCC FREESYSTEMCACHE
• DBCC FREESESSIONCACHE
• DBCC FREEPROCCACHE

Redémarrez le service SQL Server

Si l'épuisement de la mémoire est critique et que SQL Server ne peut pas traiter les requêtes, vous pouvez redémarrer le service en dernier recours. Cette action supprime toutes les connexions actives et efface les caches. Utilisez-la uniquement lorsque d’autres options échouent.

Passer en revue les paramètres de Resource Governor

Si vous utilisez Resource Governor, vérifiez les paramètres du pool de ressources et du groupe de charge de travail pour vous assurer qu'ils ne limitent pas la mémoire trop considérablement. Pour plus d’informations, consultez Resource Governor.

Ajouter plus de RAM

Si le problème persiste après les étapes précédentes, examinez plus en détail et envisagez d’augmenter les ressources serveur (RAM) sur le serveur physique ou virtuel.

Diagnostiquer et corriger la pression de la mémoire

Si une erreur de mémoire insuffisante ne se produit qu’occasionnellement ou pendant une brève période, le problème peut être temporaire et se résorber de lui-même, et il se peut qu’aucune action ne soit nécessaire. Si l’erreur se produit plusieurs fois sur plusieurs connexions et persiste pendant des secondes ou plus, suivez les diagnostics et solutions dans les sections suivantes pour identifier et résoudre la cause racine.

Pression sur la mémoire externe

Pour diagnostiquer les conditions de mémoire faible sur le système en dehors du processus SQL Server, utilisez les méthodes suivantes :

• Collecter les compteurs du Moniteur de performances. Vérifiez si les applications ou services autres que SQL Server consomment de la mémoire sur ce serveur en examinant ces compteurs :

  • Mémoire: Mégaoctets disponibles
  • Processus : Ensemble de travail
  • Processus : octets privés

  Voici un exemple de collecte de journaux Perfmon à l’aide de PowerShell :

  clear
  $serverName = $env:COMPUTERNAME
  $Counters = @(
     ("\\$serverName" +"\Memory\Available MBytes"),
     ("\\$serverName" +"\Process(*)\Working Set"),
     ("\\$serverName" +"\Process(*)\Private Bytes")
  )
  
  Get-Counter -Counter $Counters -SampleInterval 2 -MaxSamples 1 | ForEach-Object  {
  $_.CounterSamples | ForEach-Object   {
     [pscustomobject]@{
        TimeStamp = $_.TimeStamp
        Path = $_.Path
        Value = ([Math]::Round($_.CookedValue, 3)) }
  }
  }
  

• Passez en revue le journal des événements système et recherchez des erreurs liées à la mémoire (par exemple, une mémoire virtuelle faible).

• Passez en revue le journal des événements d’application pour connaître les problèmes de mémoire liés à l’application.

  Voici un exemple de script PowerShell qui interroge les journaux des événements Système et Application pour le mot clé « memory ». Vous pouvez utiliser d’autres chaînes telles que « ressource » pour votre recherche :

  Get-EventLog System -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
  Get-EventLog Application -ComputerName "$env:COMPUTERNAME" -Message "*memory*"
  

• Corrigez tout problème de code ou de configuration pour les applications ou services moins critiques afin de réduire leur utilisation de la mémoire.

• Si les applications en plus de SQL Server consomment des ressources, essayez de les arrêter ou de les réécrire, ou exécutez-les sur un serveur distinct. Ces étapes suppriment la pression de la mémoire externe.

Pression interne de la mémoire provenant de modules non moteurs

Pour diagnostiquer la pression de mémoire interne causée par des modules (DLL) à l’intérieur de SQL Server, utilisez les méthodes suivantes :

• Si SQL Server n’utilise pas Lock Pages in Memory (API AWE), la majeure partie de son utilisation mémoire se retrouve dans le compteur Process:Private Bytes (instance sqlservr) du Moniteur de performances. Le compteur SQL Server :Memory Manager : Mémoire totale (Ko) affiche l’utilisation globale de la mémoire à partir du moteur SQL Server. Si vous trouvez une différence significative entre Process:Private Bytes et SQL Server:Gestionnaire de mémoire: Mémoire totale du serveur (Ko), cette différence est probablement due à une DLL (serveur lié, XP, SQLCLR, etc.). Par exemple, si les octets privés sont de 300 Go et que la mémoire totale du serveur est de 250 Go, environ 50 Go de mémoire globale dans le processus provient de l’extérieur du moteur SQL Server.

• Si SQL Server utilise les pages de verrouillage en mémoire (API AWE), il est plus difficile d'identifier le problème, car Analyseur de performances n'offre pas de compteurs AWE qui effectuent le suivi de l'utilisation de la mémoire pour des processus individuels. Le compteur SQL Server :Memory Manager : Mémoire totale (Ko) affiche l’utilisation globale de la mémoire dans le moteur SQL Server. Les valeurs processus :octets privés peuvent varier entre 300 Mo et 1 à 2 Go dans l’ensemble. Si Process :Private Bytes est beaucoup plus élevé que cette plage classique, la différence provient probablement d’une DLL (serveur lié, XP, SQLCLR, etc.). Par exemple, si les octets privés sont de 4 à 5 Go et que SQL Server utilise des pages de verrouillage en mémoire (AWE), une grande partie des octets privés peut provenir de l’extérieur du moteur SQL Server. Cette valeur est une approximation.

• Utilisez l’utilitaire tasklist pour identifier les DLL chargées dans le processus de SQL Server.

  tasklist /M /FI "IMAGENAME eq sqlservr.exe"
  

• Vous pouvez également utiliser la requête suivante pour examiner les modules chargés (DLL) et vérifier si tout élément inattendu est présent :

  SELECT * FROM sys.dm_os_loaded_modules
  

• Si vous soupçonnez qu’un module de serveur lié entraîne une consommation importante de mémoire, configurez-le pour qu’il s’exécute hors processus en désactivant l’option Autoriser dans le processus. Pour plus d’informations, consultez Créer des serveurs liés. Tous les fournisseurs OLE DB du serveur lié ne peuvent pas s’exécuter hors processus. Pour plus d’informations, contactez le fabricant du produit.

• Dans le cas rare où les objets OLE Automation (sp_OA*) sont utilisés, vous pouvez configurer l’objet pour qu’il s’exécute dans un processus en dehors de SQL Server en spécifiant une valeur de contexte de 4 (local (.exe) serveur OLE uniquement). Pour plus d’informations, consultez sp_OACreate.

Utilisation de la mémoire interne par le moteur de SQL Server

Pour diagnostiquer la pression interne de la mémoire des composants à l’intérieur du moteur SQL Server, utilisez les méthodes suivantes :

• Commencez à collecter des compteurs Analyseur de performances pour SQL Server : SQL Server :Buffer Manager et SQL Server :Memory Manager.

• Interrogez à plusieurs reprises la vue de gestion dynamique (DMV) des memory clerks de SQL Server afin de voir où se produit la consommation de mémoire la plus élevée au sein du moteur.

  SELECT pages_kb, type, name, virtual_memory_committed_kb, awe_allocated_kb
  FROM sys.dm_os_memory_clerks
  ORDER BY pages_kb DESC
  

• Vous pouvez également observer la sortie plus détaillée DBCC MEMORYSTATUS et comment elle change quand ces messages d’erreur s’affichent.

  DBCC MEMORYSTATUS
  

• Si vous identifiez un délinquant clair parmi les commis de mémoire, concentrez-vous sur les spécificités de la consommation de mémoire pour ce composant. Voici quelques exemples :

  • Si le commis de mémoire consomme de la MEMORYCLERK_SQLQERESERVATIONS mémoire, identifiez les requêtes qui utilisent des allocations de mémoire volumineuses et optimisez-les à l’aide d’index, en les réécritant (par exemple, en les supprimant ORDER BY), ou en appliquant des indicateurs de requête d’allocation de mémoire (MIN_GRANT_PERCENT et MAX_GRANT_PERCENT). Pour plus d’informations, consultez indicateurs de requête. Vous pouvez également créer un pool de ressources pour contrôler l’utilisation de l’allocation de mémoire. Pour plus d’informations sur les allocations de mémoire, consultez Résoudre les problèmes de performances lentes ou de mémoire faible causées par les allocations de mémoire dans SQL Server.
  • Si de nombreux plans de requête ad hoc sont mis en cache, le greffier de mémoire CACHESTORE_SQLCP consomme de grandes quantités de mémoire. Identifiez les requêtes non paramétrées dont les plans d’exécution ne peuvent pas être réutilisés et paramétrez-les en les convertissant en procédures stockées, à l’aide de sp_executesql ou du paramétrage FORCED. Si l’indicateur de trace 174 est activé, vous pouvez le désactiver pour voir si cela résout le problème.
  • Si le magasin CACHESTORE_OBJCP de cache du plan d’objets consomme trop de mémoire, identifiez les procédures stockées, les fonctions ou les déclencheurs qui utilisent de grandes quantités de mémoire et envisagez de redéfinir l’application. Cela se produit généralement avec de nombreuses bases de données ou schémas qui contiennent chacune des centaines de procédures.
  • Si le OBJECTSTORE_LOCK_MANAGER greffier de mémoire affiche des allocations importantes, identifiez les requêtes qui prennent de nombreux verrous et optimisez-les à l’aide d’index. Raccourcissez les transactions qui maintiennent des verrous pendant de longues périodes avec certains niveaux d’isolation, ou vérifiez si l’escalade des verrous est désactivée.
  • Si vous observez un très grand TokenAndPermUserStore (SELECT type, name, pages_kb FROM sys.dm_os_memory_clerks WHERE name = 'TokenAndPermUserStore'), vous pouvez utiliser l’indicateur de trace 4618 pour limiter la taille du cache.
  • Si vous observez des problèmes de mémoire avec In-Memory OLTP à partir du gestionnaire de mémoire MEMORYCLERK_XTP, consultez Surveiller et résoudre les problèmes liés à l’utilisation de la mémoire pour In-Memory OLTP et Erreurs de mémoire insuffisante liées aux métadonnées tempdb optimisées en mémoire (HkTempDB).

Questions fréquemment posées

Pourquoi SQL Server-t-il utiliser presque toutes les RAM sur le serveur ?

Par conception, le pool de mémoire tampon de SQL Server s’agrandit afin de mettre en cache les pages de données et de réduire les E/S physiques, de sorte que l’utilisation de la mémoire en régime permanent se rapproche généralement du paramètre max server memory. Ce comportement est attendu et n’est pas une fuite. Pour limiter la consommation de mémoire et laisser une marge pour le système d’exploitation et les autres processus, configurez max server memory. Pour plus d’informations, consultez les options de configuration de la mémoire du serveur.

Quelle est la différence entre la mémoire maximale du serveur et la mémoire validée affichée dans le Gestionnaire des tâches ?

Le paramètre mémoire serveur max plafonne la mémoire que le pool de tampons SQL Server et la plupart des greffiers de mémoire au sein du moteur peuvent allouer. Le Gestionnaire des tâches affiche la mémoire engagée pour l’ensemble du processus sqlservr.exe. Cette vue inclut les allocations que les composants effectuent en dehors du pool de mémoires tampons, tels que le CLR, les fournisseurs de serveurs liés, les procédures stockées étendues et les mémoires tampons de sauvegarde. Par conséquent, la mémoire totale du processus peut dépasser la mémoire maximale du serveur. Pour plus d’informations, consultez le guide d’architecture de gestion de la mémoire.

Quand dois-je activer le verrouillage des pages en mémoire (LPIM) ?

Activez Verrouiller les pages en mémoire lorsque le système d’exploitation réduit le jeu de travail de SQL Server. Ce problème se présente sous la forme d’une erreur 17890 ou d’une baisse soudaine de la mémoire totale du serveur. Associez LPIM avec une valeur de mémoire maximale de serveur explicite pour laisser la RAM pour le système d’exploitation et d’autres processus. N’activez pas LPIM par défaut sur chaque instance. Utilisez-la pour résoudre un problème de pagination confirmé.

Que m’indique sys.dm_os_memory_clerks ?

sys.dm_os_memory_clerks retourne une ligne pour chaque gestionnaire de mémoire actif dans le moteur SQL Server, avec la quantité de mémoire validée par ce gestionnaire. Utilisez-la pour rechercher le composant (par exemple, le pool de mémoires tampons, le cache de plan, le gestionnaire de verrous ou les allocations de mémoire de requête) qui consomme le plus de mémoire et pour diriger votre effort de paramétrage.