MSSQLSERVER_701

Article
06/12/2024

S’applique à : SQL Server

Détails

Attribut	Valeur
Nom du produit	SQL Server
ID de l’événement	701
Source de l’événement	MSSQLSERVER
Composant	SQLEngine
Nom symbolique	NOSYSMEM
Texte du message	Mémoire système insuffisante pour exécuter cette requête.

Remarque

Cet article est axé sur SQL Server. Pour plus d’informations sur la résolution des problèmes de mémoire insuffisante dans Azure SQL Database, consultez Résoudre les erreurs de mémoire insuffisante avec Azure SQL Database.

Explication

L’erreur 701 se produit lorsque 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. Par conséquent, si SQL 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. 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 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 sys.dm_os_memory_clerks, 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 des requêtes qui acquièrent un grand nombre de verrous et les optimisent à l’aide d’index, ou raccourcissent les transactions qui contiennent des verrous pendant de longues périodes, ou vérifient 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 sys.dm_os_memory_clerks et leurs descriptions.

Action utilisateur

Si l’erreur 701 s’affiche occasionnellement ou pendant une courte période, il peut y avoir un problème de mémoire de courte durée qui s’est résolu. Vous n’avez peut-être pas besoin de prendre des mesures dans ces cas. 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 des sorties périodiques de cette requête sur le serveur SQL Server concerné

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 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)) }
}
}
```
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 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 : 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 l’option Verrouiller les pages 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 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 de verrouillage en mémoire (API AWE), il est plus difficile d’identifier le problème, car l’analyseur de performances ne propose 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 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 processus : octets privés peuvent varier entre 300 Mo et 1 à 2 Go dans l’ensemble. 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 d’octets privés est de 5 à 4 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. 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 (SQL Server Moteur de base de données). Tous les fournisseurs OLEDB du serveur lié ne sont pas tous hors processus ; contactez le fabricant du produit pour plus d’informations.
Dans les rares cas où les 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 le contexte = 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 des compteurs de moniteur 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 commis à la mémoire CACHESTORE_SQLCP utiliserait 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, ou en utilisant sp_executesql, ou en utilisant le 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 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 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.

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 :

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é la mémoire maximale du serveur, en particulier avec les pages de verrouillage en mémoire, envisagez de définir une valeur particulière pour autoriser une certaine mémoire pour le système d’exploitation. Consultez l’option de configuration du serveur de verrouillage dans les pages de verrouillage.
Vérifiez 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.
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 plus d’informations sur la configuration de la mémoire pour les machines virtuelles, consultez ce blog Virtualization – Overcommitting memory and how to detect it within the VM et Troubleshooting ESX/ESXi virtual machine performance issues (memory overcommitment)
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.

Partage via