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

Symptômes

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, il peut y avoir de nombreuses sources de consommation de mémoire et de pression de mémoire, provoquant finalement des conditions de mémoire insuffisantes.

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

• 701 : Échec de l’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 (données ou pages d’index).
• 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 de la pression 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 en raison d’aucune mémoire pour les allocations de mémoire (tri et hachage) Pour plus d’informations, consultez Comment résoudre les problèmes d’erreur SQL Server 8645.
• 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 en raison d’une mise en page de la mémoire SQL 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, le traçage d'événements pour Windows, les notifications, etc.
• 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 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 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 sollicitation externe fait référence à une utilisation élevée de la mémoire provenant d’un composant en dehors du processus qui provoque une insuffisance de mémoire 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 pression 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é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. 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 à obtenir 701 ou d’autres erreurs liées à la mémoire. Pour plus d’informations sur la façon dont SQL alloue et libère de la mémoire, consultez Architecture de mémoire SQL Server. Pour obtenir des diagnostics et des solutions plus détaillés pour le problème, consultez la pression 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 à 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. L’approche courante consiste à trouver 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 ram.
• Problèmes de pilote de périphérique : les pilotes de périphérique peuvent entraîner la pagination de tous les processus en cas d’appel incorrect du pilote à 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 : Pression interne de la mémoire, qui ne provient pas de SQL Server

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 peuvent s’exécuter à 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*). Les programmes antivirus ou d’autres programmes de sécurité qui injectent des DLL à l’intérieur d’un processus à des fins de supervision sont d’autres exemples. Un problème ou une mauvaise conception dans un de ces composants peut entraîner une consommation de mémoire importante. 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. Pour ce qui concerne SQL Server, aucun régisseur de mémoire ne va signaler une utilisation élevée de la mémoire, mais la mémoire consommée dans le processus SQL Server sera importante. Cette croissance de la mémoire à partir d’une DLL de serveur lié, par exemple, entraînerait la réduction de l’utilisation de la mémoire (voir ci-dessus) et créera des conditions de mémoire faibles pour les composants à l’intérieur de SQL Server, ce qui entraîne des erreurs de mémoire insuffisantes. Pour obtenir des diagnostics et des solutions plus détaillés sur le problème, consultez la pression de la mémoire interne, qui ne provient pas de SQL Server.

Cause 3 : Pression interne de la mémoire provenant des composants SQL Server

La sollicitation de la mémoire interne provenant de composants à l’intérieur du moteur 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. Vous devez identifier les commis de mémoire responsables des allocations de mémoire les plus importantes pour résoudre ce problème. 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 verrous consomme tant de mémoire. Vous trouverez peut-être des requêtes qui acquièrent de nombreux verrous. Vous pouvez optimiser ces requêtes à l’aide d’index, raccourcir les transactions qui contiennent des verrous pendant longtemps ou vérifier si l’escalade de verrous est désactivée. Chaque régisseur de mémoire ou chaque composant a une manière spécifique d’accéder à la mémoire et de l’utiliser. Pour plus d’informations, consultez types de régisseurs de mémoire et leurs descriptions. Pour obtenir des diagnostics et des solutions plus détaillés sur le problème, consultez Utilisation de la mémoire interne par le moteur SQL Server.

Représentation visuelle des 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 collecter des données de dépannage

Vous pouvez utiliser les outils de diagnostic suivants pour collecter des données de dépannage :

Analyseur de performances

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

• Mémoire :octets Moctets disponibles
• Processus : Plage 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)

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 l’Explorateur d’objets, cliquez avec le bouton droit sur le nom de l’instance SQL Server.
3. Dans le menu contextuel, sélectionnez Rapports standard consommation de mémoire des rapports>>.

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 SQL personnalisés.

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

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

Méthodologie de résolution des erreurs

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’aurez peut-être pas besoin d’agir dans ces cas-là. 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 dans les sections suivantes pour résoudre les erreurs de mémoire plus loin.

Sollicitation de 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 :

• Collectez les compteurs Analyseur de performances. Recherchez si des applications ou des services autres que SQL Server consomment de la mémoire sur ce serveur en examinant les compteurs suivants :

  • Mémoire :octets Moctets disponibles
  • Processus : Plage 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)) }
  }
  }
  

• Examiner le journal des événements système et rechercher des erreurs liées à la mémoire (par exemple, mémoire virtuelle insuffisante).

• Recherchez des problèmes de mémoire liés à des applications dans le journal des événements d’application.

  Voici un exemple de script PowerShell pour interroger les journaux des événements système et d’application pour le mot clé « memory ». N’hésitez pas à 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*"
  

• Résolvez les éventuels 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 les applications en plus de SQL Server consomment des ressources, essayez d’arrêter ou de réécrire ces applications, ou envisagez de les exécuter sur un serveur distinct. Cette étape permet de supprimer la pression sur la mémoire externe.

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

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 les pages verrouillées en mémoire (API AWE), la plupart de sa mémoire est reflétée dans le compteur Process :Private Bytes (SQLServrinstance) dans Analyseur de performances. L’utilisation globale de la mémoire provenant du moteur 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 and 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 mémoire globale dans le processus provient de l’extérieur du moteur SQL Server.

• Si SQL Server utilise des pages verrouillées en mémoire (API AWE), il est plus difficile d’identifier le problème, car le Analyseur de performances n’offre pas de compteurs AWE qui effectuent le suivi de l’utilisation de la mémoire pour des processus individuels. L’utilisation globale de la mémoire dans le moteur SQL Server est reflétée dans le compteur SQL Server :Memory Manager : Total Server Memory (Ko). Les valeurs standard de Processus : Octets privés peuvent varier de 300 Mo à 1-2 Go. Si vous trouvez une utilisation significative des octets Process :Private au-delà de cette utilisation classique, la différence provient probablement d’une DLL (serveur lié, XP, SQLCLR, etc.). Par exemple, si le compteur d’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. C’est une technique approximative.

• Utilisez l’utilitaire Tasklist pour identifier les DLL qui sont chargées dans l’espace 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 voir si quelque chose d’inattendu existe.

  SELECT * FROM sys.dm_os_loaded_modules
  

• Si vous soupçonnez qu’un module serveur lié provoque une consommation de mémoire importante, vous pouvez le configurer pour qu’il s’exécute en désactivant l’option Autoriser le traitement . Pour plus d’informations, consultez Créer des serveurs liés . Les fournisseurs OLE DB du serveur lié ne peuvent pas tous manquer de processus. Pour plus d’informations, contactez le fabricant du produit.

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

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

Pour diagnostiquer la pression de mémoire interne provenant de 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 : Gestionnaire de mémoire.

• Interrogez la vue de gestion dynamique Régisseurs des mémoire SQL Server à plusieurs reprises 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 déterminez qu’un des régisseurs de mémoire est en clairement responsable, concentrez-vous sur les spécificités de la consommation de mémoire pour ce composant. Voici quelques exemples :

  • Si le commis de MEMORYCLERK_SQLQERESERVATIONS 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éécritez-les (supprimez ORDER by, par exemple) ou appliquez 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 faible mémoire 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 à la 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étrés en les convertissant en procédures stockées, en utilisant sp_executesqlou en utilisant FORCED le 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 remaniez 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 chacun d’eux.
  • 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 entraînent la publication de verrous pendant de longues périodes dans certains niveaux d’isolation ou vérifiez si l’escalade de verrous est désactivée.
  • Si vous observez 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 OLTP en mémoire provenant du MEMORYCLERK_XTP commis de mémoire, vous pouvez faire référence à Surveiller et résoudre les problèmes d’utilisation de la mémoire pour les métadonnées tempdb en mémoire optimisée (HkTempDB) en mémoire.

Solutions rapides qui peut rendre de 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 si possible d’augmenter Mémoire maximum du serveur :

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

Si vous n’avez pas configuré la mémoire maximale du serveur, en particulier avec les pages verrouillées en mémoire, envisagez de la définir sur une valeur particulière pour autoriser une certaine mémoire pour le système d’exploitation. Consultez l’option de configuration des pages verrouillées dans le serveur 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, en cours d’exécution de requêtes et vérifiez s’il existe des applications moins critiques qui peuvent être arrêtées temporairement ou déplacées vers un autre serveur 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 vers 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 trop sollicitée. Pour obtenir des idées sur la configuration de 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 (memory overcommitment) (overcommitment) de la machine virtuelle.

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

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

• DBCC FREESYSTEMCACHE

• DBCC FREESESSIONCACHE

• DBCC FREEPROCCACHE

Redémarrez le service SQL Server

Dans certains cas, si vous devez gérer 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.

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

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

Ajouter davantage 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 serveur (RAM).