Options de configuration de la mémoire du serveur

Utilisez les deux options de mémoire du serveur, mémoire minimale du serveur et mémoire maximale du serveur, pour reconfigurer la quantité de mémoire (en mégaoctets) gérée par le Gestionnaire de mémoire SQL Server pour un processus de SQL Server utilisé par un instance de SQL Server.

Le paramètre par défaut pour la mémoire minimale du serveur est 0, et le paramètre par défaut pour la mémoire maximale du serveur est 2147483647 Mo. Par défaut, SQL Server peut modifier ses besoins en mémoire dynamiquement en fonction des ressources système disponibles.

Lorsque SQL Server utilise dynamiquement la mémoire, il interroge régulièrement le système afin de déterminer la mémoire physique disponible. La conservation de cette mémoire libre empêche le système d'exploitation de paginer. S'il y a moins de mémoire, SQL Server en libère pour le système d'exploitation. S’il y a plus de mémoire disponible, SQL Server peut allouer davantage de mémoire. SQL Server n’ajoute de la mémoire que lorsque sa charge de travail en requiert davantage. Un serveur au repos n’augmente pas la taille de son espace d’adressage virtuel.

Pour une requête qui retourne la mémoire utilisée actuellement, consultez l'exemple B. max server memory contrôle l’allocation de mémoire SQL Server, y compris le pool de mémoires tampons, la mémoire de compilation, tous les caches, les allocations de mémoire qe, la mémoire du gestionnaire de verrouillage et la mémoire clr (essentiellement tout commis de mémoire trouvé dans sys.dm_os_memory_clerks). La mémoire des piles de threads, des tas de mémoire, des fournisseurs de serveurs liés autres que SQL Server et toute mémoire allouée par une DLL non SQL Server ne sont pas contrôlées par la mémoire maximale du serveur.

SQL Server utilise l’API de notification de mémoire QueryMemoryResourceNotification pour déterminer à quel moment le Gestionnaire de mémoire de SQL Server peut allouer et libérer de la mémoire.

Il est recommandé d’autoriser SQL Server à utiliser la mémoire de manière dynamique. Toutefois, vous pouvez définir les options de mémoire manuellement et limiter la quantité de mémoire à laquelle SQL Server pouvez accéder. Avant de définir la quantité de mémoire pour SQL Server, déterminez le paramètre de mémoire approprié en soustrayant, de la mémoire physique totale, la mémoire requise pour le système d’exploitation et toutes les autres instances de SQL Server (et d’autres systèmes utilisent, si l’ordinateur n’est pas entièrement dédié à SQL Server). Cette différence correspond à la quantité maximale de mémoire que vous pouvez affecter à SQL Server.

Paramétrage manuel des options de mémoire

Vous pouvez définir les options de serveur min server memory et max server memory pour couvrir une plage de valeurs de 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.

Utilisez min_server_memory pour garantir une quantité minimale de mémoire accessible au Gestionnaire de mémoire de SQL Server pour une instance de SQL Server. SQL Server n'alloue pas immédiatement la mémoire spécifiée dans min server memory 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 min server memory ne soit réduite. Par exemple, si plusieurs instances de SQL Server peuvent exister simultanément sur le même ordinateur hôte, définissez min_server_memory plutôt que max_server_memory afin de réserver de la mémoire pour une instance. La définition d’une valeur min_server_memory 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 SQL Server invitée, une quantité de mémoire supérieure à celle nécessaire pour obtenir des performances acceptables.

Utilisez max_server_memory pour que le système d’exploitation ne fasse pas l’objet d’une sollicitation de la mémoire préjudiciable. Pour définir la configuration de la mémoire maximale du serveur, surveillez la consommation globale du processus SQL Server afin de déterminer les besoins en mémoire. Pour obtenir des résultats plus précis pour une instance unique :

• Réservez 1 à 4 Go de la mémoire totale du système d’exploitation au système d’exploitation lui-même.
• Soustrayez ensuite l’équivalent des allocations de mémoire SQL Server potentielles en dehors du contrôle mémoire maximale du serveur, qui est constitué de la taille de pile ¹ * nombre maximal de threads de travail ^{calculés 2} + -g paramètre ^{de démarrage 3} (ou 256 Mo par défaut si -g n’est pas défini). Il doit rester le paramètre max_server_memory pour une installation d’instance unique.

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

² 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.

³ Pour plus d’informations sur le paramètre de démarrage -g, consultez la page Options de démarrage du service moteur de base de données dans la documentation. Utilisable uniquement pour les SQL Server 32 bits (SQL Server 2005 à SQL Server 2014).

Comment configurer les options de mémoire à l'aide de SQL Server Management Studio

Utilisez les deux options de mémoire du serveur, min server memory et max server memory, 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. Par défaut, SQL Server peut modifier dynamiquement sa configuration mémoire en fonction des ressources système disponibles.

Procédure de configuration d'une quantité de mémoire fixe

Pour définir une quantité fixe de mémoire :

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

2. Cliquez sur le nœud Mémoire .

3. Sous Options mémoire du serveur, entrez les quantités souhaitées pour Mémoire minimale du serveur et Mémoire maximale du serveur.

   Utilisez les paramètres par défaut pour autoriser SQL Server à modifier ses besoins de mémoire de façon dynamique en fonction des ressources système disponibles. Le paramètre par défaut pour la mémoire minimale du serveur est 0, et le paramètre par défaut pour la mémoire maximale du serveur est 2147483647 mégaoctets (Mo).

Obtenir le débit de données maximal pour les applications réseau

Pour optimiser l’utilisation de la mémoire système pour SQL Server, vous devez limiter la quantité de mémoire utilisée par le système pour la mise en cache des fichiers. Pour limiter le cache du système de fichiers, veillez à désactiver Maximiser le débit des données pour le partage de fichiers . Vous pouvez spécifier le plus petit cache du système de fichiers en sélectionnant Minimiser la mémoire utilisée ou Équilibrer.

Pour connaître la configuration actuelle de votre système d'exploitation

1. Cliquez sur Démarrer, sur Panneau de configuration, double-cliquez sur Connexions réseau, puis sur Connexion au réseau local.

2. Sous l'onglet Général , cliquez sur Propriétés. Sélectionnez Partage de fichiers et d'imprimantes pour les réseaux Microsoftet cliquez sur Propriétés.

3. Si l'option Maximiser le débit des données pour les applications réseau est sélectionnée, choisissez une autre option et cliquez sur OK. Fermez ensuite les boîtes de dialogue restantes.

Verrouiller les pages en mémoire

Cette stratégie Windows détermine quels comptes peuvent utiliser un processus destiné à conserver les données en mémoire physique pour éviter leur pagination en mémoire virtuelle sur le disque. 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 SQL Server Verrouiller les pages en mémoire est définie sur ACTIVÉ dans les instances 32 bits et 64 bits de SQL Server édition Standard 2014 et supérieure lorsque le compte disposant des privilèges d’exécution sqlservr.exe a reçu le droit d’utilisateur Windows « Pages verrouillées en mémoire » (LPIM). Dans les versions antérieures de SQL Server, la définition de l'option de verrouillage des pages pour une instance 32 bits de SQL Server exige que le compte avec les privilèges nécessaires pour exécuter sqlservr.exe ait le droit d'utilisateur LPIM et que l'option de configuration « awe_enabled » ait la valeur ON.

Pour désactiver l’option Verrouiller les pages en mémoire pour SQL Server, supprimez le droit d’utilisateur « Pages verrouillées en mémoire » pour le compte de démarrage SQL Server.

Pour désactiver Verrouiller les pages en mémoire

Pour désactiver l’option Verrouiller les pages en mémoire :

1. Dans le menu Démarrer , cliquez sur Exécuter. Dans la zone Ouvrir , tapez gpedit.msc.

   La boîte de dialogue Stratégie de groupe s'affiche.

2. Sur la console Stratégie de groupe , développez Configuration de l'ordinateur, puis Paramètres Windows.

3. Développez Paramètres de sécurité, puis Stratégies locales.

4. Sélectionnez le dossier Attribution des droits utilisateur .

   Les stratégies s'affichent dans le volet Détails.

5. Dans le volet, double-cliquez sur Verrouiller les pages en mémoire.

6. Dans la boîte de dialogue Paramètre de stratégie de sécurité locale , sélectionnez le compte avec les privilèges nécessaires pour exécuter sqlservr.exe et cliquez sur Supprimer.

Gestionnaire de mémoire virtuelle

Les systèmes d'exploitation 32 bits permettent d'accéder à un espace d'adressage virtuel de 4 Go. 2 Go de mémoire virtuelle sont alloués en privé à chaque processus et disponibles pour les applications. 2 Go sont réservés au système d'exploitation. Toutes les éditions des systèmes d'exploitation comportent un commutateur qui permet aux applications d'accéder à 3 Go d'espace d'adressage virtuel, ce qui limite la mémoire disponible pour le système d'exploitation à 1 Go. Pour plus d'informations sur l'utilisation de la configuration de la mémoire avec le commutateur, consultez la documentation Windows sur la technologie 4GT (4-gigabyte tuning). Lorsque le SQL Server 32 bits s’exécute sur le système d’exploitation 64 bits, son espace d’adressage virtuel disponible par l’utilisateur est de 4 Go.

Les zones d'espace d'adressage validées sont mappées à la mémoire physique disponible par le Gestionnaire de mémoire virtuelle Windows (VMM).

Pour plus d'informations sur la taille de mémoire physique prise en charge par les divers systèmes d'exploitation, consultez la documentation Windows « Limites de la mémoire selon les versions de Windows ».

Les systèmes de mémoire virtuelle autorisent le surengagement de la mémoire physique, de sorte que le rapport entre la mémoire virtuelle et la mémoire physique peut être supérieur à 1:1. Il est alors possible d'exécuter des programmes plus volumineux sur des ordinateurs offrant diverses configurations de la mémoire physique. Toutefois, dans la plupart des cas, l'utilisation d'une quantité de mémoire virtuelle nettement plus importante que les plages de travail moyennes combinées de tous les processus peut entraîner une détérioration des performances.

min server memory et max server memory 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 show advanced options a la valeur 1. Ces paramètres entrent immédiatement en vigueur, sans redémarrage du serveur.

Exécution de plusieurs instances SQL Server

Lorsque vous exécutez plusieurs instances de Moteur de base de données, vous avez le choix entre trois approches pour gérer la mémoire :

• Choisir max server memory pour contrôler l'utilisation de la mémoire. 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 en cours d'exécution, aucune d'entre elles ne pourra utiliser la mémoire libre restante.

• Choisir min server memory pour contrôler l'utilisation de la mémoire. 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. Elle 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.

• 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 Memory Notification. 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.

Apport de la quantité maximale de mémoire à SQL Server

*/3gb est un paramètre de démarrage du système d’exploitation. Pour plus d’informations, consultez MSDN Library.

**WOW64 (Windows sur Windows 64) est un mode dans lequel SQL Server 32 bits s’exécute sur un système d’exploitation 64 bits. Pour plus d’informations, consultez MSDN Library.

Exemples

Exemple A

L'exemple suivant affecte la valeur 4 Go à l'option max server memory .

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

Exemple B. Détermination de 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 Memory_usedby_Sqlserver_MB,  
(locked_page_allocations_kb/1024) AS Locked_pages_used_Sqlserver_MB,  
(total_virtual_address_space_kb/1024) AS Total_VAS_in_MB,  
process_physical_memory_low,  
process_virtual_memory_low  
FROM sys.dm_os_process_memory;  

Voir aussi

Surveillance et réglage des performances
RECONFIGURE (Transact-SQL)
Options de configuration du serveur (SQL Server)
sp_configure (Transact-SQL)