Options de configuration de la mémoire du serveur

  • Article

S’applique à :SQL Server

L’utilisation de la mémoire pour le moteur de base de données SQL Server est limitée par une paire de paramètres de configuration, la mémoire minimale du serveur (Mo) et la mémoire maximale du serveur (Mo). Au fil du temps et dans des circonstances normales, SQL Server tente de revendiquer la mémoire jusqu’à la limite définie par la mémoire maximale du serveur (Mo).

Notes

Les index Columnstore et les objets OLTP en mémoire ont leurs propres régisseurs de mémoire, ce qui facilite le monitoring de leur utilisation des pools de mémoires tampons. Pour plus d’informations, consultez sys.dm_os_memory_clerks.

Dans les versions antérieures de SQL Server, l’utilisation de la mémoire n’a pas été mise à jour, indiquant à SQL Server que toutes les mémoires système étaient disponibles. Il est recommandé dans toutes les versions de SQL Server de configurer une limite supérieure pour l’utilisation de la mémoire SQL Server en configurant la mémoire maximale du serveur (Mo).

  • Depuis SQL Server 2019 (15.x), le programme d’installation SQL dans les serveurs Windows fournit une recommandation pour la mémoire maximale du serveur (Mo) pour une instance SQL autonome en fonction d’un pourcentage de mémoire système disponible au moment de l’installation.
  • À tout moment, vous pouvez reconfigurer les limites de la mémoire (en mégaoctets) pour un processus SQL Server utilisé par une instance de SQL Server via les options de configuration de la mémoire minimale du serveur (Mo) et de la mémoire maximale du serveur (Mo).

Notes

Ce guide fait référence à l’instance SQL sur Windows. Pour plus d’informations sur la configuration de la mémoire dans Linux, consultez les meilleures pratiques en matière de performances et les instructions de configuration pour SQL Server sur Linux et le paramètre memory.memorylimitmb.

Recommandations

Les paramètres par défaut et les valeurs minimales autorisées pour ces options sont les suivants :

Option Default Minimum autorisé Recommandé
mémoire minimale du serveur (Mo) 0 0 0
mémoire maximale du serveur (Mo) 2,147,483,647 mégaoctets (Mo) 128 Mo 75 % de la mémoire système disponible non consommée par d’autres processus, y compris les autres instances. Pour obtenir des suggestions, consultez mémoire minimale du serveur.

Dans le cadre de ces limites, SQL Server peut modifier dynamiquement sa configuration mémoire en fonction des ressources système disponibles. Pour plus d’informations, consultez Gestion de la mémoire dynamique.

  • Si vous affectez à mémoire minimale du serveur (Mo) une valeur trop élevée, une instance unique de SQL Server peut être contrainte de rivaliser avec d’autres instances SQL hébergées sur le même hôte pour obtenir de la mémoire.
  • Toutefois, la définition de la mémoire maximale du serveur (Mo) trop élevée peut entraîner la perte du niveau de performance et des problèmes de pression et de performances de mémoire dans l’instance SQL.
  • Si vous affectez à mémoire minimale du serveur (Mo) la valeur minimale, vous risquez d’empêcher SQL Server de démarrer. Si vous ne pouvez plus démarrer SQL Server après avoir changé cette option, démarrez-le au moyen de l'option de démarrage -f et restaurez la valeur antérieure de la mémoire minimale du serveur (Mo). Pour plus d’informations, consultez Options de démarrage du service moteur de base de données.
  • Il n’est pas recommandé de définir la mémoire maximale du serveur (Mo) et la mémoire minimale du serveur (Mo) sur la même valeur ou des valeurs proches de la même valeur.

Notes

L’option de mémoire maximale du serveur limite uniquement la taille du pool de mémoires tampons SQL Server. L’option de mémoire maximale du serveur ne limite pas une zone de mémoire non réservée restante que SQL Server laisse pour les allocations d’autres composants tels que les procédures stockées étendues, les objets COM, les DLL non partagées et les fichiers EXE.

SQL Server peut utiliser la mémoire dynamiquement. Toutefois, vous pouvez configurer manuellement les options de mémoire et limiter la quantité de mémoire accessible à SQL Server. Avant de définir la mémoire allouée à SQL Server, déterminez la valeur adaptée pour la mémoire. Pour cela, vous devez soustraire de la mémoire physique totale, la mémoire exigée par le système d’exploitation, les allocations de mémoire non contrôlées par le paramètre mémoire maximale du serveur (Mo) et par toute autre instance de SQL Server (ainsi que par d’autres systèmes si le serveur abrite d’autres applications qui consomment de la mémoire, notamment d’autres instances de SQL Server). Cette différence représente la mémoire maximale que vous pouvez allouer à l’instance actuelle de SQL Server.

La mémoire peut être configurée jusqu’à la limite de l’espace d’adressage virtuel utilisé par le processus dans toutes les éditions de SQL Server. Pour plus d’informations, consultez Limites de mémoire pour les versions de Windows et de Windows Server.

mémoire minimale du serveur

Utilisez l'option mémoire minimale du serveur pour garantir une quantité minimale de mémoire disponible pour le Gestionnaire de mémoire SQL Server.

  • SQL Server n'alloue pas immédiatement la mémoire spécifiée dans mémoire minimale du serveur (Mo) au démarrage. Néanmoins, lorsque l’utilisation de la mémoire atteint cette valeur en raison de la charge client, SQL Server ne peut libérer de la mémoire à moins que la valeur mémoire minimale du serveur (Mo) ne soit réduite. Par exemple, lorsque plusieurs instances de SQL Server sont installées simultanément sur le même serveur, envisagez de définir le paramètre mémoire minimale du serveur (Mo) pour réserver de la mémoire pour une instance.

  • La définition d’une valeur mémoire minimale du serveur (Mo) est essentielle dans un environnement virtualisé. Elle permet en effet de garantir que la sollicitation de la mémoire de l’hôte sous-jacent ne tente pas de libérer, dans le pool de tampons d’une machine virtuelle invitée, une quantité de mémoire supérieure à celle nécessaire pour obtenir un niveau de performance acceptable. Dans l’idéal, les instances de SQL Server dans une machine virtuelle n’ont pas à concurrencer les processus de désallocation de mémoire proactive de l’hôte virtuel.

  • Il n’est pas garanti que SQL Server alloue la mémoire spécifiée dans mémoire minimale du serveur (Mo). Si la charge sur le serveur ne nécessite jamais d’allouer la mémoire spécifiée dans mémoire minimale du serveur (Mo), SQL Server utilise alors moins de mémoire.

mémoire maximale du serveur

Utilisez la mémoire maximale du serveur (Mo) pour garantir que la sollicitation de la mémoire provenant de SQL Server ne nuit pas au système d’exploitation ni à d’autres applications.

  • Avant de définir la configuration de la mémoire maximale du serveur (Mo) surveillez la consommation générale de mémoire par le serveur hébergeant l’instance SQL, pendant l’opération normale, pour déterminer la disponibilité et les exigences en mémoire. Pour une configuration initiale ou lorsqu’il n’était pas possible de collecter l’utilisation de la mémoire de processus SQL Server au fil du temps, utilisez l’approche des meilleures pratiques généralisée suivante pour configurer la mémoire maximale du serveur (Mo) sur une seule instance :
    • À partir du total de la mémoire du système d’exploitation, soustrayez l’équivalent des allocations de mémoire de thread SQL Server potentielles en dehors du contrôle de la mémoire maximale du serveur (Mo), ce qui correspond à la taille de la pile1 multipliée par les threads de travail maximum calculés2.
    • Soustrayez ensuite 25 % pour les autres allocations de mémoire en dehors du contrôle mémoire serveur maximale (Mo), comme les tampons de sauvegarde, les DLL de procédures stockées étendues, les objets créés au moyen de procédures Automation (appels sp_OA) et les allocations à partir de fournisseurs de serveur lié. Il s’agit d’une approximation générique. Le kilométrage peut varier.
    • Il doit rester le paramètre mémoire maximale du serveur (Mo) pour l’installation d’une instance unique.

1 Pour plus d’informations sur les tailles de piles de threads par architecture, consultez le guide d’architecture de gestion de la mémoire.

2 Pour plus d’informations sur les threads de worker par défaut calculés pour un nombre donné d’UC avec affinité dans l’hôte actif, consultez la page Configurer l’option de configuration du serveur max worker threads dans la documentation.

Définir manuellement les options

Vous pouvez définir les options de serveur mémoire minimale du serveur (Mo) et mémoire maximale du serveur (Mo) pour couvrir une plage de valeurs de la mémoire. Cette méthode est utile pour les administrateurs système ou de bases de données qui souhaitent configurer une instance de SQL Server en accord avec la mémoire exigée par d’autres applications ou d’autres instances de SQL Server qui s’exécutent sur le même hôte.

Utiliser Transact-SQL

La mémoire minimale du serveur (Mo) et la mémoire maximale du serveur (Mo) sont des options avancées. Si vous utilisez la procédure stockée système sp_configure pour changer ces paramètres, vous ne pouvez les modifier que si l’option afficher les options avancées a la valeur 1. Ces paramètres entrent immédiatement en vigueur, sans redémarrage du serveur. Pour plus d’informations, consultez sp_configure.

L’exemple suivant définit l’option mémoire maximale du serveur (Mo) sur 12 288 Mo ou 12 Go. Bien que sp_configure spécifie le nom de l’option comme max server memory (MB), vous pouvez omettre le (MB).

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 12288;
GO
RECONFIGURE;
GO

La requête suivante retourne des informations sur les valeurs actuellement configurées et la valeur en cours d’utilisation. Cette requête retourne des résultats, que l’option sp_configure « afficher les options avancées » soit activée ou non.

SELECT [name], [value], [value_in_use]
FROM sys.configurations
WHERE [name] = 'max server memory (MB)' OR [name] = 'min server memory (MB)';

Utilisez SQL Server Management Studio.

Utilisez la mémoire minimale du serveur (Mo) et la mémoire maximale du serveur (Mo) pour reconfigurer la quantité de mémoire (en mégaoctets) gérée par le Gestionnaire de mémoire de SQL Server pour une instance de SQL Server.

  1. Dans l’Explorateur d’objets, cliquez avec le bouton droit sur un serveur et sélectionnez Propriétés.

  2. Sélectionnez la page Mémoire de la fenêtre Propriétés du serveur. Les valeurs actuelles de Mémoire minimale du serveur et Mémoire maximale du serveur sont affichées.

  3. Dans Options de mémoire du serveur, entrez les nombres souhaités pour Mémoire minimale du serveur et Mémoire maximale du serveur. Pour obtenir des suggestions, consultez mémoire minimale du serveur (Mo) et mémoire maximale du serveur (Mo) dans cet article.

La capture d’écran suivante illustre les trois étapes :

Screenshot of the memory configuration options in SSMS.

Verrouiller les pages en mémoire (LPIM)

Les applications Windows peuvent utiliser les API Windows Address Windowing Extensions (AWE) pour allouer et mapper la mémoire physique dans l’espace d’adressage du processus. La stratégie Windows LPIM détermine quels comptes peuvent accéder à l’API pour conserver les données dans la mémoire physique et éviter leur pagination en mémoire virtuelle sur le disque. La mémoire allouée à l’aide d’AWE est verrouillée jusqu’à ce que l’application la libère explicitement ou se ferme. L’utilisation des API AWE pour la gestion de la mémoire dans SQL Server 64 bits est également fréquemment appelée pages verrouillées. Le verrouillage des pages en mémoire peut permettre de conserver sa réactivité au serveur lors de la pagination de la mémoire sur disque. L’option Verrouiller les pages en mémoire est activée dans les instances de l’édition standard SQL Server et éditions supérieures quand le compte avec les privilèges nécessaires pour exécuter sqlservr.exe dispose du droit d’utilisateur Windows Verrouiller les pages en mémoire (LPIM).

Pour désactiver l’option Verrouiller les pages en mémoire pour SQL Server, supprimez le droit d’utilisateur Verrouiller les pages en mémoire pour le compte disposant de privilèges pour exécuter le compte de démarrage sqlservr.exe (compte de démarrage de SQL Server).

L’utilisation de l’option LPIM n’affecte pas la gestion de la mémoire dynamique de SQL Server. Elle peut donc être augmentée ou réduite à la demande d’autres régisseurs de mémoire. Quand vous utilisez le droit d’utilisateur Verrouiller les pages en mémoire, il est vivement recommandé de définir une limite supérieure pour lamémoire maximale du serveur (Mo). Pour plus d’informations, consultez mémoire maximale du serveur (Mo).

L’option LPIM doit être utilisée quand il y a des raisons de penser que le processus sqlservr est hors page. Dans ce cas, l’erreur 17890 est signalée dans le journal des erreurs, comme dans l’exemple ci-dessous :

A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: #### seconds. Working set (KB): ####, committed (KB): ####, memory utilization: ##%.

L’utilisation de LPIM avec un paramètre de mémoire maximale du serveur (Mo) mal configuré qui ne prend pas en compte les autres consommateurs de mémoire dans le système, peut provoquer une instabilité, en fonction de la quantité de mémoire requise par d’autres processus ou de la mémoire SQL Server nécessaire hors de l’étendue de la mémoire maximale du serveur (Mo). Pour plus d’informations, consultez Mémoire maximale du serveur. Si le privilège Verrouiller des pages en mémoire (LPIM) est accordé (sur les systèmes 32 bits ou 64 bits), nous vous recommandons vivement de définir la mémoire maximale du serveur (Mo) sur une valeur spécifique, plutôt que de laisser la valeur par défaut de 2 147 483 647 mégaoctets (Mo).

Notes

À compter de SQL Server 2012 (11.x), l’indicateur de trace 845 n’est pas nécessaire dans l’édition Standard pour utiliser Verrouiller les pages.

Activer Verrouiller des pages en mémoire

Après avoir examiné les informations précédentes, pour activer l’option Verrouiller les pages en mémoire en accordant le privilège au compte de service pour l’instance de SQL Server, consultez Activer l’option Verrouiller les pages en mémoire (Windows).

Pour déterminer le compte de service de l’instance de SQL Server, reportez-vous au Gestionnaire de configuration SQL Server ou interrogez service_account de sys.dm_server_services. Pour plus d’informations, consultez sys.dm_os_wait_stats (Transact-SQL).

Afficher l’état de Verrouiller les pages en mémoire

Pour déterminer si le privilège Verrouiller les pages en mémoire est accordé au compte de service pour l’instance de SQL Server, utilisez la requête suivante. Cette requête est prise en charge dans SQL Server 2016 (13.x) SP1 et versions ultérieures.

SELECT sql_memory_model_desc FROM sys.dm_os_sys_info;

Les valeurs suivantes de sql_memory_model_desc indiquent l’état de LPIM :

  • CONVENTIONAL. Le privilège Verrouiller les pages en mémoire n’est pas accordé.
  • LOCK_PAGES. Le privilège Verrouiller les pages en mémoire est accordé.
  • LARGE_PAGES. Le privilège Verrouiller les pages en mémoire est accordé en mode Entreprise avec l’indicateur de trace 834 activé. Il s’agit d’une configuration avancée et non recommandée pour la plupart des environnements. Pour plus d’informations et des mises en garde importantes, consultez Indicateur de trace 834.

Utilisez les méthodes suivantes pour déterminer si l’instance de SQL Server utilise des pages verrouillées :

  • La sortie de la requête Transact-SQL suivante indique des valeurs non nulles pour locked_page_allocations_kb :

    SELECT osn.node_id, osn.memory_node_id, osn.node_state_desc, omn.locked_page_allocations_kb 
FROM sys.dm_os_memory_nodes omn 
INNER JOIN sys.dm_os_nodes osn ON (omn.memory_node_id = osn.memory_node_id) 
WHERE osn.node_state_desc <> 'ONLINE DAC';

  • Le journal des erreurs SQL Server actuel signale le message, Using locked pages in the memory manager au démarrage du serveur.

  • La section Gestionnaire de mémoire de la sortie DBCC MEMORYSTATUS affiche une valeur non nulle pour l’élément AWE Allocated.

Instances multiples de SQL Server

Lorsque vous exécutez plusieurs instances du Moteur de base de données, plusieurs approches s’offrent à vous pour gérer la mémoire :

  • Utiliser la mémoire maximale du serveur (Mo) dans chaque instance, pour contrôler l’utilisation de la mémoire, comme indiqué ci-dessus. Définir les valeurs maximales pour chaque instance, en veillant à ce que le total alloué ne soit pas supérieur à la mémoire physique totale de votre ordinateur. Il se peut que vous souhaitiez attribuer à chaque instance une mémoire proportionnelle à la charge ou à la taille de base de données prévue. Cette solution présente l'avantage qu'au démarrage des nouveaux processus ou des nouvelles instances, ils pourront accéder immédiatement à la mémoire libre. En revanche, cette solution présente l'inconvénient que, si toutes les instances ne sont pas utilisées, aucune instance en cours d’exécution ne pourra utiliser la mémoire libre restante.

  • Utiliser la mémoire minimale du serveur (Mo) dans chaque instance, pour contrôler l’utilisation de la mémoire, comme indiqué ci-dessus. Définissez les valeurs minimales pour chaque instance, de telle sorte que leur somme soit inférieure de 1 à 2 Go à la mémoire physique totale de votre machine. Une fois encore, vous pouvez établir ces valeurs minimales de façon proportionnelle à la charge prévue de cette instance. Cette solution présente l'avantage que si toutes les instances ne sont pas en cours d'exécution au même instant, celles qui le sont peuvent utiliser la mémoire libre restante. Cette approche est également utile quand un autre processus gourmand en mémoire est présent sur l’ordinateur, car elle garantit que SQL Server bénéficie au moins d’une quantité de mémoire acceptable. Le désagrément est que, lorsqu'une nouvelle instance (ou un autre processus) démarre, il se peut que les instances en cours d'exécution mettent un certain temps à libérer de la mémoire, notamment si elles doivent à cette fin réécrire les pages modifiées sur leurs bases de données.

  • Utilisez la mémoire maximale du serveur (Mo) et la mémoire minimale du serveur (Mo) dans chaque instance pour contrôler l’utilisation de la mémoire, observer et régler l’utilisation maximale de chaque instance et la protection minimale de la mémoire dans un large éventail de niveaux d’utilisation potentiels de la mémoire.

  • Ne rien faire (déconseillé). Les premières instances présentées avec une charge de travail tendent à allouer la totalité de la mémoire. Les instances inactives ou les instances ayant démarré ultérieurement peuvent finir par ne disposer que d'une quantité de mémoire minime. SQL Server n'effectue aucune tentative pour équilibrer l'utilisation de la mémoire entre les instances. Cependant, toutes les instances répondent aux signaux de Windows Notification Memory pour ajuster la taille de leur occupation mémoire. Windows n'équilibre pas la mémoire entre les applications avec l'API de notification de mémoire. Il fournit simplement un commentaire global quant à la disponibilité de la mémoire sur le système.

Comme vous pouvez modifier ces paramètres sans redémarrer les instances, vous pouvez sans peine procéder à des essais pour trouver les valeurs qui conviennent le mieux à votre modèle d'utilisation.

Exemples

R. Définir l’option de mémoire maximale du serveur sur 4 Go

L’exemple suivant définit l’option mémoire maximale du serveur (Mo) sur 4 096 Mo ou 4 Go. Bien que sp_configure spécifie le nom de l’option comme max server memory (MB), vous pouvez omettre le (MB).

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 4096;
GO
RECONFIGURE;
GO

Cette opération génère une instruction similaire à Configuration option 'max server memory (MB)' changed from 2147483647 to 4096. Run the RECONFIGURE statement to install. La nouvelle limite de mémoire prend effet immédiatement lors de l’exécution de RECONFIGURE. Pour plus d’informations, consultez sp_configure.

B. Déterminer l’allocation de mémoire actuelle

La requête suivante retourne des informations sur la mémoire allouée actuellement.

SELECT
  physical_memory_in_use_kb/1024 AS sql_physical_memory_in_use_MB,
   large_page_allocations_kb/1024 AS sql_large_page_allocations_MB,
   locked_page_allocations_kb/1024 AS sql_locked_page_allocations_MB,
   virtual_address_space_reserved_kb/1024 AS sql_VAS_reserved_MB,
   virtual_address_space_committed_kb/1024 AS sql_VAS_committed_MB,
   virtual_address_space_available_kb/1024 AS sql_VAS_available_MB,
   page_fault_count AS sql_page_fault_count,
   memory_utilization_percentage AS sql_memory_utilization_percentage,
   process_physical_memory_low AS sql_process_physical_memory_low,
   process_virtual_memory_low AS sql_process_virtual_memory_low
FROM sys.dm_os_process_memory;

C. Affichez la valeur sous max server memory (MB)

La requête suivante retourne des informations sur la valeur actuellement configurée et la valeur en cours d’utilisation. Cette requête retourne des résultats, que l’option sp_configure « afficher les options avancées » soit activée ou non.

SELECT [value], [value_in_use]
FROM sys.configurations WHERE [name] = 'max server memory (MB)';

Étapes suivantes