MSSQLSERVER_701

S’applique à :SQL Server

Détails

Explication

L’erreur 701 se produit quand SQL Server n’a pas pu allouer suffisamment de mémoire pour exécuter une requête. Une insuffisance de mémoire peut être due à plusieurs facteurs incluant des paramètres du système d’exploitation, la disponibilité de la mémoire physique, d’autres composants qui utilisent de la mémoire dans SQL Server ou les limites de mémoire sur la charge de travail actuelle. Dans la plupart des cas, la transaction qui a échoué n’est pas la cause de cette erreur. Globalement, les causes peuvent être regroupées en trois catégories :

Sollicitation de la mémoire par un composant externe ou par le 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 une des rares applications conçues pour répondre à la sollicitation de la mémoire par le système d’exploitation en réduisant sa propre 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 va répondre en réduisant sa propre utilisation de la mémoire. Très peu d’autres applications vont répondre, car elles ne sont pas conçues pour être à l’écoute de cette notification. Donc, si SQL commence à réduire son utilisation de la mémoire, son pool de mémoire est réduit et les éventuels composants ayant besoin de mémoire peuvent ne pas l’obtenir. Vous commencez alors à recevoir l’erreur 701 et d’autres erreurs liées à la mémoire. Pour plus d’informations, consultez Architecture mémoire de SQL Server

Sollicitation interne de la mémoire, ne provenant 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. Il existe des composants qui peuvent s’exécuter à l’intérieur du processus SQL Server, mais qui sont « externes » au moteur SQL Server. Il s’agit par exemple de DLL comme des serveurs liés, des composants SQLCLR, des procédures étendues (XP) 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é mettant en cache 20 millions lignes de données provenant d’une source externe dans la mémoire de 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 augmentation de la consommation de mémoire provenant par exemple d’une DLL de serveur lié a comme conséquence que SQL Server va commencer à limiter son utilisation de la mémoire (voir ci-dessus), ce qui va créer des conditions de mémoire insuffisante pour des composants à l’intérieur de SQL Server, provoquant des erreurs comme l’erreur 701.

Sollicitation interne de la mémoire, provenant d’un ou plusieurs composants SQL Server

Une sollicitation interne de la mémoire provenant de composants à l’intérieur du moteur SQL Server peut aussi provoquer l’erreur 701. Il existe des centaines de composants, suivis via des régisseurs de mémoire, qui allouent de la mémoire dans SQL Server. Vous devez identifier le ou les régisseurs de mémoire responsables des allocations de mémoire les plus importantes pour pouvoir résoudre ces problèmes. Par exemple, si vous constatez que le régisseur de mémoire OBJECTSTORE_LOCK_MANAGER effectue une allocation de mémoire importante, vous devez comprendre pourquoi le gestionnaire de verrous consomme autant de mémoire. Vous pouvez trouver que des requêtes acquièrent un grand nombre de verrous et les optimisent en utilisant des index, réduire la durée des transactions qui détiennent des verrous pendant de longues périodes 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.

Action requise

Si l’erreur 701 apparaît occasionnellement ou pendant une courte période, il peut y avoir un problème ponctuel de mémoire qui s’est résolu par lui-même. Vous n’aurez peut-être pas besoin d’agir dans ces cas-là. Cependant, si l’erreur se produit plusieurs fois sur plusieurs connexions et dure quelques secondes ou plus, suivez les étapes pour résoudre le problème.

La liste suivante décrit les procédures générales à suivre pour résoudre les erreurs de mémoire.

Outils de diagnostic et capture

Les outils de diagnostics qui vous permettront de collecter des données de résolution des problèmes sont l’Analyseur de performances, sys.dm_os_memory_clerks et DBCC MEMORYSTATUS .

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

• Mémoire : Mo 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)

Collecter les sorties périodiques de cette requête sur le serveur SQL Server impacté

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

Pssdiag ou SQL LogScout

Une autre méthode automatisée pour capturer ces points de données est d’utiliser des outils comme PSSDIAG ou SQL LogScout.

• Si vous utilisez Pssdiag, configurez-le pour capturer le collecteur Perfmon et le collecteur Diagnostics personnalisés\Erreur mémoire 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 interne ou externe de la mémoire.

Sollicitation externe : diagnostics et solutions

• Pour diagnostiquer les conditions de mémoire insuffisante sur le système en dehors du processus de SQL Server, collectez les compteurs de l’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 : Mo disponibles
  • Processus : Plage de travail
  • Processus : Octets privés

  Voici un exemple de collection 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)) }
  }
  }
  

• Examinez le journal des événements système et recherchez les erreurs liées à la mémoire (par exemple une 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 d’événements système et applicaiton 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 des applications en plus de SQL Server consomment des ressources, essayez d’arrêter ou de reprogrammer 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 : diagnostics et solutions

Pour diagnostiquer une sollicitation interne de la mémoire provoquée par des modules (DLL) à l’intérieur de SQL Server, utilisez l’approche suivante :

• Si SQL Server n’utilise pas* de pages verrouillées en mémoire (API AWE), la plus grande partie de sa mémoire est reflétée dans le compteur Processus : Octets privés (instance SQLServr) de l’Analyseur de performances. L’utilisation globale de la mémoire provenant de l’intérieur du moteur SQL Server est reflétée dans le compteur SQL Server : Gestionnaire de mémoire : Mémoire totale du serveur (Ko) . Si vous constatez une différence significative entre la valeur de Processus : Octets privés et celle de SQL Server : Gestionnaire de mémoire : Mémoire totale du serveur (Ko) , cette différence provient probablement d’une DLL (serveur lié, XP, SQLCLR, etc.). Par exemple, si Octets privés vaut 300 Go et que Mémoire totale du serveur vaut 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 des pages verrouillées en mémoire (API AWE), il est plus difficile d’identifier le problème, car l’Analyseur de performances n’offre pas de compteurs AWE qui effectuent le suivi de l’utilisation de la mémoire pour les processus individuels. L’utilisation globale de la mémoire provenant de l’intérieur du moteur SQL Server est reflétée dans le compteur SQL Server : Gestionnaire de mémoire : Mémoire totale du serveur (Ko) . Les valeurs standard de Processus : Octets privés peuvent varier de 300 Mo à 1-2 Go. Si vous constatez une utilisation significative de Processus : Octets privés au-delà de cette utilisation standard, la différence provient probablement d’une DLL (serveur lié, XP, SQLCLR, etc.). Par exemple, si le compteur Octets privés vaut 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 aussi utiliser cette requête pour examiner les modules (DLL) chargés et vérifier si quelque chose d’inattendu se trouve ici.

  SELECT * FROM sys.dm_os_loaded_modules
  

• Si vous pensez qu’un module Serveur lié provoque une consommation de mémoire importante, vous pouvez le configurer pour qu’il soit exécuté en dehors du processus en désactivant l’option Autoriser inprocess. Pour plus d’informations, consultez Créer des serveurs liés . Certains fournisseurs OLEDB de serveur lié ne peuvent pas s’exécuter hors processus : pour plus d’informations, contactez le fabricant du produit.

• Dans les rares cas où des objets OLE Automation sont utilisés (sp_OA*), vous pouvez configurer l’objet pour qu’il s’exécute dans un processus en dehors de SQL Server en définissant context = 4 (Serveur OLE local (.exe) uniquement.). Pour plus d’informations, consultez sp_OACreate.

Utilisation interne de la mémoire par le moteur de SQL Server : diagnostics et solutions

• Commencez à collecter les compteurs de l’analyseur de performances pour SQL Server:SQL Server:Buffer Manager, SQL Server: Memory Manager.

• 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 aussi observer la sortie plus détaillée de DBCC MEMORYSTATUS et comment elle change quand 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 régisseur de mémoire MEMORYCLERK_SQLQERESERVATIONS consomme de la mémoire, identifiez les requêtes qui utilisent des allocations de mémoire très importantes et optimisez-les via des index, réécrivez-les (par exemple, supprimez ORDER BY) ou appliquez des indicateurs de requête.
  • 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 soit en les convertissant en procédures stockées, soit à l’aide de sp_executesql, soit à l’aide du paramétrage FORCÉ.
  • Si le magasin de cache de plans d’objets CACHESTORE_OBJCP consomme beaucoup de mémoire, procédez comme suit : identifiez les procédures stockées, les fonctions ou les déclencheurs qui utilisent beaucoup de mémoire, et procédez à une éventuelle reconception de l’application. Cela peut généralement se produire en raison d’un grand nombre de bases de données ou de schémas, contenant chacun des centaines de procédures.
  • Si le régisseur de mémoire OBJECTSTORE_LOCK_MANAGER fait apparaître des allocations de mémoire très importantes, identifiez les requêtes qui appliquent beaucoup de verrous et optimisez-les en utilisant des index. Réduisez la durée des transactions qui empêchent la libération des verrous pendant de longues périodes dans certains niveaux d’isolation, ou vérifiez si l’escalade de verrous est désactivée.

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 :

• 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

    Identifiez les paramètres inhabituels. Si besoin est, corrigez-les. Prenez en compte l'augmentation de la mémoire requise. Les paramètres par défaut sont répertoriés dans la rubrique Options de configuration de a mémoire du serveur.

• Si vous n’avez pas configuré Mémoire maximum du serveur en particulier avec les pages verrouillées en mémoire, envisagez de la définir sur une valeur particulière autorisant l’attribution d’une certaine quantité de mémoire au système d’exploitation. Consultez l’option de configuration du serveur Pages verrouillées en mémoire.

• Vérifiez la charge de travail des requêtes : le nombre de sessions simultanées et les requêtes actuellement en cours d’exécution, et vérifiez si des applications moins critiques peuvent être arrêtées temporairement ou déplacées sur un autre serveur SQL Server.

• 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 ce blog Virtualisation – Surcommitting memory and how to detect it in the VM and Troubleshooting ESX/ESXi virtual machine performance issues (dépassement de mémoire)

• Vous pouvez exécuter les commandes DBCC suivantes pour libérer plusieurs caches de mémoire SQL Server.

  • DBCC FREESYSTEMCACHE

  • DBCC FREESESSIONCACHE

  • DBCC FREEPROCCACHE

• Si vous utilisez Resource Governor, nous vous recommandons de vérifier les paramètres du pool de ressources ou du groupe de charge de travail, et de déterminer s’ils ne limitent pas trop radicalement la mémoire.

• Si le problème persiste, vous devez poursuivre vos investigations et éventuellement augmenter les ressources mémoire du serveur.