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

Symptômes

SQL Server utilise une architecture de mémoire complexe qui correspond à l’ensemble de fonctionnalités complexes et riches. En raison de la variété des besoins en mémoire, il peut y avoir de nombreuses sources de consommation de mémoire et de sollicitation de la mémoire, ce qui entraîne finalement des conditions de mémoire insuffisante.

Il existe des erreurs courantes qui indiquent une mémoire insuffisante dans SQL Server. Voici quelques exemples d’erreurs :

• 701 : Échec d’allocation de mémoire suffisante pour exécuter une requête.
• 802 : Échec de l’obtention de la mémoire pour allouer des pages dans le pool de mémoires tampons (pages de données ou d’index).
• 1204 : Échec de l’allocation de mémoire pour les verrous.
• 6322 : Échec d’allocation de mémoire pour l’analyseur XML.
• 6513 : Échec de l’initialisation du CLR en raison de la sollicitation de la mémoire.
• 6533 : AppDomain déchargé en raison d’une mémoire insuffisante.
• 8318 : Échec du 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 en raison de l’absence de mémoire pour les allocations de mémoire (tri et hachage) Pour plus d’informations, consultez Comment résoudre les problèmes SQL Server’erreur 8645.
• 8902 : Échec d’allocation de mémoire pendant l’exécution de DBCC.
• 9695 ou 9696 : Échec d’allocation de mémoire pour les opérations Service Broker.
• 17131 ou 17132 : Échec de démarrage du serveur en raison d’une mémoire insuffisante.
• 17890 : Échec d’allocation de mémoire en raison du paginé de la mémoire SQL par le système d’exploitation.
• 22986 ou 22987 : Échecs de capture de données modifiées en raison d’une mémoire insuffisante.
• 25601 : le moteur Xevent est en mémoire insuffisante.
• 26053 : Échec de l’initialisation des interfaces réseau SQL en raison d’une mémoire insuffisante.
• 30085, 30086, 30094 : Les opérations de texte intégral SQL échouent en raison d’une mémoire insuffisante.

Cause

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 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 de mémoire insuffisante n’est pas la cause de cette erreur. Dans l’ensemble, les causes peuvent être regroupées en trois catégories :

Cause 1 : Sollicitation 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 provenant d’un composant en dehors du processus, ce qui entraîne une mémoire insuffisante pour SQL Server. Vous devez déterminer 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 sollicitation de la mémoire du système d’exploitation en réduisant son utilisation de la mémoire. Cela signifie que 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épondra en réduisant son propre utilisation de la mémoire. Peu d’autres applications répondent, car elles ne sont pas conçues pour écouter cette notification. Par conséquent, si SQL Server commence à réduire son utilisation de la mémoire, son pool de mémoire est réduit et les composants qui ont besoin de mémoire peuvent ne pas l’obtenir. Par conséquent, vous commencez à recevoir des erreurs 701 ou d’autres erreurs liées à la mémoire. Pour plus d’informations sur la façon dont SQL alloue et libère dynamiquement de la mémoire, consultez architecture de mémoire SQL Server. Pour obtenir des diagnostics et des solutions plus détaillées pour le problème, consultez Sollicitation de la mémoire externe dans cet article.

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

• Problèmes liés aux applications : une ou plusieurs applications épuisent la mémoire physique disponible. Le système d’exploitation répond aux nouvelles demandes de ressources de l’application en essayant de libérer de la mémoire. L’approche courante consiste à rechercher les applications qui épuisent la mémoire et à prendre les mesures nécessaires pour équilibrer la mémoire entre elles sans entraîner d’épuisement de la RAM.
• Problèmes liés aux pilotes de périphérique : les pilotes de périphérique peuvent entraîner la pagination de tous les processus si le 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 de ces étapes et de la résolution des problèmes, reportez-vous à MSSQLSERVER_17890.

Cause 2 : sollicitation interne de la mémoire, qui ne provient pas de SQL Server

La sollicitation de la mémoire interne fait référence à la faible disponibilité de la mémoire causée par des facteurs à l’intérieur du processus de SQL Server. Certains composants qui peuvent s’exécuter à l’intérieur du processus SQL Server sont « externes » au moteur SQL Server. Par exemple, les fournisseurs OLE DB (DLL) comme les serveurs liés, les procédures ou fonctions SQLCLR, les procédures étendues (XPs) et OLE Automation (sp_OA*). D’autres incluent des antivirus ou d’autres programmes de sécurité qui injectent des DLL à l’intérieur d’un processus à des fins de surveillance. Un problème ou une conception médiocre dans l’un de ces composants peut entraîner une consommation de mémoire importante. Par exemple, prenons l’exemple d’un serveur lié qui met en cache 20 millions de lignes de données à partir d’une source externe dans SQL Server mémoire. En ce qui concerne SQL Server, aucun commis de mémoire ne signale une utilisation élevée de la mémoire, mais la mémoire consommée à l’intérieur du processus de SQL Server sera élevée. Cette croissance de la mémoire à partir d’une DLL de serveur lié, par exemple, entraînerait SQL Server commencer à réduire son utilisation de la mémoire (voir ci-dessus) et créerait des conditions de mémoire insuffisante pour les composants à l’intérieur de SQL Server, provoquant des erreurs de mémoire insuffisante. Pour obtenir des diagnostics et des solutions plus détaillées sur le problème, consultez Pression interne de la mémoire, qui ne provient pas de SQL Server.

Cause 3 : Pression interne de la mémoire provenant de SQL Server composant(s)

La sollicitation de la mémoire interne provenant des composants à l’intérieur du moteur de SQL Server peut également entraîner des erreurs de mémoire insuffisante. Il existe des centaines de composants suivis via des commis de mémoire qui allouent de la mémoire dans SQL Server. Pour résoudre ce problème, vous devez identifier les commis de mémoire responsables des allocations de mémoire les plus importantes. Par exemple, si vous constatez que le OBJECTSTORE_LOCK_MANAGER commis de mémoire affiche une allocation de mémoire importante, vous devez comprendre pourquoi le Gestionnaire de verrouillage consomme tant de mémoire. Vous pouvez constater qu’il existe des requêtes qui acquièrent de nombreux verrous. Vous pouvez optimiser ces requêtes en utilisant des index, en raccourcissant toutes 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 commis de mémoire ou composant a un moyen unique d’accéder à la mémoire et de l’utiliser. Pour plus d’informations, consultez Types de commis de mémoire et leurs descriptions. Pour obtenir des diagnostics et des solutions plus détaillées sur le problème, consultez Utilisation interne de la mémoire par SQL Server moteur.

Représentation visuelle des types de pression de la 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 collecter des données de résolution des problèmes

Vous pouvez utiliser les outils de diagnostic suivants pour collecter des données de résolution des problèmes :

Analyseur de performances

Configurez et collectez les compteurs suivants avec Analyseur de performances :

• Mémoire :Octets disponibles
• Process :Working Set
• Processus :Octets privés
• SQL Server :Memory Manager : (tous les compteurs)
• SQL Server :Buffer Manager : (tous les compteurs)

DMV ou DBCC MEMORYSTATUS

Vous pouvez utiliser sys.dm_os_memory_clerks ou DBCC MEMORYSTATUS pour observer l’utilisation globale de la mémoire dans SQL Server.

Rapport standard de consommation de mémoire dans SSMS

Afficher l’utilisation de la mémoire dans SQL Server Management Studio :

1. Lancez SQL Server Management Studio et connectez-vous à un serveur.
2. Dans Explorateur d'objets, cliquez avec le bouton droit sur le nom SQL Server instance.
3. Dans le menu contextuel, sélectionnez Rapports>Standard Reports> StandardMemory Consumption.

PSSDiag ou SQL LogScout

Une autre méthode automatisée pour capturer ces points de données consiste à utiliser des outils tels que PSSDiag ou SQL LogScout.

• Si vous utilisez PSSDiag, configurez-le pour capturer le collecteur Perfmon et le collecteur d’erreurs de mémoire De diagnostics personnalisés\SQL .

• Si vous utilisez SQL LogScout, configurez-le pour capturer le scénario mémoire .

Les sections suivantes décrivent des étapes plus détaillées pour chaque scénario (sollicitation de la mémoire externe ou interne).

Méthodologie de résolution des problèmes

Si une erreur de mémoire insuffisante apparaît occasionnellement ou pendant une courte période, il peut y avoir un problème de mémoire de courte durée qui se résout lui-même. Vous n’avez peut-être pas besoin d’agir dans ces cas. Toutefois, si l’erreur se produit plusieurs fois sur plusieurs connexions et persiste pendant des périodes de secondes ou plus, suivez les diagnostics et solutions des sections suivantes pour résoudre les erreurs de mémoire.

Sollicitation de la mémoire externe

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

• Collecter les compteurs Analyseur de performances. Déterminez si des applications ou des services autres que SQL Server consomment de la mémoire sur ce serveur en examinant ces compteurs :

  • Mémoire :Octets disponibles
  • Process :Working Set
  • 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 les erreurs liées à la mémoire (par exemple, une mémoire virtuelle insuffisante).

• 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 pour interroger les journaux des événements système et d’application pour la mot clé « mémoire ». N’hésitez pas à utiliser d’autres chaînes comme « ressource » pour votre recherche :

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

• Résolvez les problèmes de code ou de configuration pour les applications ou services moins critiques afin de réduire leur utilisation de la mémoire.

• Si des applications en plus de SQL Server consomment des ressources, essayez d’arrêter ou de rééchelonner ces applications, ou envisagez de les exécuter sur un serveur distinct. Ces étapes suppriment la sollicitation de la mémoire externe.

Sollicitation de la mémoire interne, qui ne provient pas de SQL Server

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

• Si SQL Server n’utilise pas pages verrouillées en mémoire (API AWE), la majeure partie de sa mémoire est reflétée dans le compteur Process :Private Bytes (SQLServr instance) dans Analyseur de performances. L’utilisation globale de la mémoire provenant du moteur de SQL Server est reflétée dans le compteur SQL Server :Memory Manager : Total Server Memory (Ko). Si vous trouvez une différence significative entre la valeur Process :Private Bytes et SQL Server :Memory Manager : Total Server Memory (Ko), cette différence provient probablement d’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 la mémoire globale du processus proviennent de l’extérieur du moteur SQL Server.

• Si SQL Server utilise l’API AWE (Pages verrouillées en mémoire), il est plus difficile d’identifier le problème, car le Analyseur de performances n’offre pas de compteurs AWE qui suivent l’utilisation de la mémoire pour des processus individuels. L’utilisation globale de la mémoire dans le moteur de SQL Server est reflétée dans le compteur SQL Server :Memory Manager : Total Server Memory (Ko). Processus : Les valeurs d’octets privés peuvent varier entre 300 Mo et 1 à 2 Go au total. Si vous constatez une utilisation significative de Process :Private Bytes au-delà de cette utilisation classique, la différence provient probablement d’une DLL (serveur lié, XP, SQLCLR, etc.). Par exemple, si le compteur Octets privés est de 4 à 5 Go et que SQL Server utilise des pages verrouillées en mémoire (AWE), une grande partie des octets privés peut provenir de l’extérieur du moteur SQL Server. Il s’agit d’une technique d’approximation.

• Utilisez l’utilitaire Tasklist pour identifier toutes les DLL chargées à l’intérieur de SQL Server’espace :

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

• Vous pouvez également utiliser la requête suivante pour examiner les modules chargés (DLL) et voir s’il y a quelque chose d’inattendu.

  SELECT * FROM sys.dm_os_loaded_modules
  

• Si vous pensez qu’un module serveur lié est à l’origine d’une consommation de mémoire importante, vous pouvez le configurer pour qu’il soit hors processus en désactivant l’option Autoriser inprocess . Pour plus d’informations , consultez Créer des serveurs liés . Les fournisseurs OLE DB des serveurs liés ne sont pas tous à court de processus. Pour plus d’informations, contactez le fabricant du produit.

• Dans les rares cas où des objets Automation OLE (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 (serveur OLE local (.exe) uniquement). Pour plus d’informations, consultez sp_OACreate.

Utilisation de la mémoire interne par SQL Server moteur

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

• Commencez à collecter les compteurs de Analyseur de performances pour SQL Server : SQL Server :Buffer Manager et SQL Server : Gestionnaire de mémoire.

• Interrogez plusieurs fois le SQL Server la DMV des commis de mémoire pour voir où la consommation de mémoire la plus élevée se produit à l’intérieur 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 la façon dont elle change lorsque vous voyez ces messages d’erreur.

  DBCC MEMORYSTATUS
  

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

  • Si le commis MEMORYCLERK_SQLQERESERVATIONS de mémoire consomme de la mémoire, identifiez les requêtes qui utilisent des allocations de mémoire énormes et optimisez-les via des index, réécrire (supprimer ORDER by, par exemple) ou appliquer des indicateurs de requête d’allocation de mémoire (voir min_grant_percent et max_grant_percent indicateurs ). Vous pouvez également créer un pool Resource Governor pour contrôler l’utilisation de la mémoire d’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 insuffisante causés par les allocations de mémoire dans SQL Server.
  • Si un grand nombre de plans de requête ad hoc sont mis en cache, le CACHESTORE_SQLCP commis de mémoire utilise de grandes quantités de mémoire. Identifiez les requêtes non paramétrables dont les plans de requête ne peuvent pas être réutilisés et paramétrez-les en les convertissant en procédures stockées, à l’aide sp_executesqlde ou à l’aide FORCED du paramétrage. Si vous avez activé l’indicateur de trace 174, 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, fonctions ou déclencheurs qui utilisent de grandes quantités de mémoire et éventuellement reconcevoir l’application. En règle générale, cela peut se produire en raison de grandes quantités de bases de données ou de schémas avec des centaines de procédures dans chacune.
  • Si le OBJECTSTORE_LOCK_MANAGER commis de mémoire affiche des allocations de mémoire volumineuses, identifiez les requêtes qui appliquent de nombreux verrous et optimisez-les à l’aide d’index. Raccourcissez les transactions qui empêchent la libération de verrous pendant de longues périodes dans certains niveaux d’isolation ou case activée si l’escalade de verrous est désactivée.
  • Si vous observez une taille très grande 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 constatez des problèmes de mémoire avec In-Memory OLTP provenant du MEMORYCLERK_XTP commis de mémoire, vous pouvez vous reporter à Surveiller et résoudre les problèmes d’utilisation de la mémoire pour In-Memory oltp et les métadonnées tempdb à mémoire optimisée (HkTempDB) en mémoire insuffisante.

Soulagement rapide qui peut rendre la mémoire disponible

Les actions suivantes peuvent libérer de la mémoire et la rendre disponible pour SQL Server :

Modifier les paramètres de configuration de la mémoire

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

• mémoire maximale du serveur
• min server memory

Si vous n’avez pas configuré la mémoire maximale du serveur, en particulier avec Pages verrouillées en mémoire, envisagez de lui affecter une valeur particulière pour autoriser une certaine mémoire pour le système d’exploitation. Consultez l’option de configuration du serveur Pages verrouillées en mémoire .

Modifier ou déplacer la charge de travail hors du système

Examinez la charge de travail de requête : nombre de sessions simultanées, requêtes en cours d’exécution et vérifiez s’il existe des applications moins critiques qui peuvent être arrêtées temporairement ou déplacées vers un autre 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 la charge de travail en lecture seule sur le réplica secondaire d’un groupe de disponibilité Always On et Configurer l’accès en lecture seule à un réplica secondaire d’un groupe de disponibilité Always On.

Garantir une configuration de mémoire appropriée pour les machines virtuelles

Si vous exécutez SQL Server sur une machine virtuelle, vérifiez que la mémoire de la machine virtuelle n’est pas surémise. Pour obtenir des idées sur la façon de configurer la mémoire pour les machines virtuelles, consultez Virtualisation - Surcommitting memory and how to detect it in the vm and Troubleshooting ESX/ESXi virtual machine performance issues (dépassement de mémoire).

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

Vous pouvez exécuter une ou plusieurs des commandes DBCC suivantes pour libérer plusieurs caches de mémoire SQL Server :

• DBCC FREESYSTEMCACHE

• DBCC FREESESSIONCACHE

• DBCC FREEPROCCACHE

Redémarrer SQL Server service

Dans certains cas, si vous avez besoin de faire face à l’épuisement critique de la mémoire et que SQL Server n’est pas en mesure de traiter les requêtes, vous pouvez envisager de redémarrer le service.

Envisager d’utiliser Resource Governor pour des scénarios spécifiques

Si vous utilisez Resource Governor, nous vous recommandons d’case activée les paramètres du pool de ressources et du groupe de charge de travail pour voir s’ils ne limitent pas trop la mémoire.

Ajouter plus de RAM sur le serveur physique ou virtuel

Si le problème persiste, vous devez examiner plus en détail et éventuellement augmenter les ressources du serveur (RAM).