[SQL Server] Performance tuning : optimisation de la mémoire au niveau Windows

Ce billet s’intéresse au performance tuning de la mémoire au niveau de l’OS avant ou après toute installation d’une instance SQL Server. Il fait un focus sur Windows Server 2003 et Windows Server 2008, mais peut aussi concerner Windows Server 2012.

Extension de la mémoire avec /3GB, /USERVA et /PAE (Windows 32 bits seulement)

La quantité de mémoire maximale que peut avoir toute application sur un système Windows 32 bits étant de 4 Go de RAM, il est donc souhaitable de configurer les commutateurs /PAE et /3GB qui permettent d’outrepasser ces limites. Le commutateur /3GB assure la réservation, dans l’espace d’adresse virtuelle (VAS), de plus de places pour les applications de sorte que le partage de la mémoire, sur 4 Go, se fasse de la façon suivante : 1 Go pour le kernel de Windows et 3 Go pour les applications (c’est là que prend sens tout le terme « /3GB »).

Dans le cas de SQL Server, ce dernier peut donc bénéficier de 3 Go, soit 1 Go supplémentaire et significatif. Et cela est d’autant plus significatif que sans l’activation du commutateur /3GB, le partage des 4 Go se fait de façon équitable (2 Go pour le kernel et 2 Go pour les applications, dont SQL Server).

Le commutateur /PAE (Physical Address Extenson), quant à lui, permet de dépasser les limitations de /3GB et de /USERVA en poussant l’extension de la mémoire physique de l’OS au-delà de 3 Go jusqu’à 64 Go.

Le tableau ci-dessous résume bien les situations adaptées à l’activation simultanée ou non des commutateurs /3GB et /PAE pour les applications 32 bits :

RAM (en Go) /3GB utile ? /PAE utile ?
0 à 4 Oui Non
4 à 16 Oui Oui
16 à 64 Non Oui

L’utilisation du commutateur /USERVA permet de spécifier la quantité de mémoire délivrée par /3GB à allouer aux processus. Ainsi, quand /3GB est activé sans /USERVA, c’est 3072 Mo qui sont alloués, mais en activant /USERVA et en lui attribuant une valeur de l’ordre de 3030 Mo (supposons…), c’est donc 3030 Mo qui seront laissés aux processus, le kernel économisant ainsi 42 Mo.

Pour étendre l’espace d’adressage des applications 32 bits sous Windows Server 2003 (R2), il suffit simplement d’ouvrir le fichier boot.ini (présent et caché à la racine de C:\), puis d’effectuer les mêmes modifications que celles montrées en rouge ci-dessous, au sein dudit fichier (/USERVA peut être éventuellement rajouté avec les bonnes valeurs en Mo à allouer aux processus):

[boot loader]

timeout=30

default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS

[operating systems]

multi(0)disk(0)rdisk(0)partition(1)\WINDOWS= »Windows Server 2003, Enterprise » /noexecute=optout /fastdetect /3GB /PAE

Néanmoins, le fichier boot.ini, et donc la possibilité de configurer /3GB, n’existant plus à partir de Windows Server 2008, il faut donc utiliser BCDEDIT. Pour ce faire :

  • Ouvrez Powershell (ou l’invite de commande Windows) en mode administrateur comme sur la figure ci-dessous qui sert d’exemple basique :

  • Tapez la ligne de commande suivante :
    • Pour /USERVA :
BCDEDIT /Set IncreaseUserVa 3072
  • Pour /PAE :
BCDEDIT /Set PAE forceenable
  • Exécutez les lignes de commande puis redémarrez la machine.
Notez que l’activation des extensions de mémoire est inutile pour Windows 64 bits (y compris s’il s’agit d’y installer SQL Server 32 bits dessus).

De même notez que seule l’édition Enterprise de Windows Server 2003 (R2) 32 bits et 2008 (R2) 32 bits permet de consommer plus de 4 Go de RAM (la Standard, ainsi que les autres éditions, étant limitées à 4 Go de RAM, que les commutateurs de mémoire soient activés ou non). C’est pour cette raison qu’il faut généralement opter pour un OS 64 bits.

Optimisation du page file (swap)

Le but de cette section est de fournir des éléments d’optimisation de la mémoire virtuelle afin de se prémunir contre tout risque de crash de l’OS en cas de saturation de la RAM. Gardez tout de même en tête que l’utilisation de la RAM est plus préférable à celle de la mémoire virtuelle pour des raisons de performances (l’accès à la mémoire virtuelle étant généralement plus lent). Et c’est donc pour cette raison qu’il faut toujours s’assurer qu’il y ait suffisamment de RAM de sorte que l’OS n’ait pas à utiliser le fichier de pagination.

          Tuning de la mémoire virtuelle

Windows gère automatiquement la taille de la mémoire virtuelle de l’OS en la redimensionnant à la hausse en cas de besoin, ce qui peut conduire à une perte de temps étant donné le coût de l’opération. Pour éliminer ce temps perdu, il suffit de fixer une fois pour toute la taille de la mémoire virtuelle initiale et maximale du page file (dont le fichier dédié pagefile.sys se trouve, par défaut, à la racine de C:\).

En règle générale, une bonne pratique consiste à attribuer 1,5 fois la taille de la RAM de l’OS lors du reparamétrage manuel du page file. Et pour aller plus loin dans l’optimisation des performances, il est possible de considérer la possibilité de placer le page file sur un disque dédié, afin de réduire les risques de fragmentation (voir plus loin). 

Pour paramétrer le page file (dans notre exemple, la machine possède 10 Go de RAM), suivez les étapes suivantes :

  • Tapez sysdm.cpl dans la barre de recherche du menu Démarrer ou dans Exécuter, comme ci-dessous :

  • Accès aux paramètres de performances.

Dans l’onglet Paramètres système avancés, cliquez sur le bouton Paramètres…
de la section Performances :


Cela va afficher la boîte de dialogue d’options de performances. Il vous faudra alors accéder à l’onglet Avancé
pour pouvoir accéder aux paramètres de la mémoire virtuelle.

  • Accès aux paramètres de la mémoire virtuelle :


Cliquez sur Modifier… ce qui va ouvrir la boîte de dialogue de configuration de la mémoire virtuelle.

  • Configuration de la taille minimale et maximale du page file :


Après avoir désactivé Gestion automatique du fichier d’échange pour les lecteurs, spécifiez les valeurs de la taille maximale et minimale du page file comme suit :

    • Taille minimale = RAM (ici, 10 Go, soit 10 000 Mo) x 1,5 (soit, ici, 15 000 Mo).
    • Taille maximale = RAM x 3 (soit, ici, 30 000 Mo).
  • Cliquez sur Définir.
  • Validez les modifications en pressant sur OK.
  • Redémarrez la machine pour que les modifications puissent être prises en compte.
Il est vivement déconseillé de désactiver son utilisation (clic sur Aucun fichier de pagination) ou de supprimer le fichier pagefile.sys. En effet, en cas de saturation de la RAM, l’OS n’aura pas la capacité d’utiliser la mémoire virtuelle ce qui pourrait le conduire à un crash.

          Déplacement de pagefile.sys sur un disque dédié

Pour des raisons de performances, et afin de ne pas fragmenter fortement le disque où se trouvent les autres fichiers systèmes, il est recommandé de placer le fichier pagefile.sys (qui, rappellons-le, se trouve, par défaut, à la racine de C:\) sur un disque dédié.

Pour déplacer le fichier pagefile.sys proprement, il faut respecter scrupuleusement les étapes suivantes :

  • Accédez aux paramètres système avancé comme vu dans la section précédente sur le paramétrage de page file.
  • Dans la boîte de dialogue Mémoire virtuelle, cliquez sur la partition au sein de laquelle vous souhaitez placer le fichier pagefile.sys.
  • Spécifiez la taille minimale et celle maximale, comme vu dans la section précédente.
  • Cliquez sur Définir.
  • Cliquez sur la future ancienne partition où se trouve le fichier pagefile.sys (par défaut, C).
  • Cliquez sur Aucun fichier d’échange.
  • Cliquez sur Définir.
  • Cliquez sur OK pour valider les modifications.
  • Redémarrez pour que l’OS prenne ces modifications en compte.
Notez qu’il n’est pas recommandé de créer plusieurs fichiers pagefile.sys au sein d’un même disque ou d’une même partition. Et par ailleurs, privilégiez un disque favorisant les écritures rapides.

Verrouillage des pages en mémoire

Pour des raisons de performances, il est nécessaire de bien paramétrer la mémoire de SQL Server (via les paramètres Max server memory et Min server memory) en cas de verrouillage des pages en mémoire. Cela sera abordé dans un futur billet.


          Pour SQL Server 32 bits et SQL Server 64 bits Enterprise Edition

Afin de permettre à l’instance SQL Server d’avoir toujours accès directement à ses pages de données en mémoire, l’activation du Lock pages in memory (ou LPIM, pour les intimes), au sein du Security Policy, peut s’avérer utile. Cela apporte un gain de performances non-négligeable dans la mesure où en évitant la pagination des pages par l’OS, le moteur SQL s’évite la désagréable obligation de passer par ce dernier pour accéder à ses pages, ce qui peut impacter la vitesse de traitement des activités transactionnelles.

Pour verrouiller les pages en mémoire, suivez les étapes suivantes :

  • Accès au Security Policy (SecPol) via Executer ou le menu Démarrer comme ci-dessous:


  • Accès aux options de verrouillage des pages en mémoire au sein de la section Stratégies locales\Attribution des droits utilisateur :


  • Ajout du compte de service d’exécution de SQL Server pour le verrouillage des pages en mémoire.


Après l’ajout du compte de service SQL Server, cliquez sur OK pour valider la configuration effectuée (aucun redémarrage de l’OS n’est requis).

Pour savoir si les modifications ont été prises en compte, vous pouvez soit jeter un coup d’œil dans l’ERRORLOG, soit, au sein de l’analyseur de requêtes de SSMS, taper la commande suivante :

EXEC sp_readerrorlogGO

Puis rechercher, au sein du résultat de la requête, la ligne suivante :

Using locked pages for buffer pool.


          Pour les éditions non-Enterprise de SQL Server 64 bits

Là où les éditions Enterprise de SQL Server, à l’instar de toutes les éditions de SQL Server 32 bits, supportent déjà le verrouillage de pages en mémoire, ce qui fait que la seule chose à faire, pour ces éditions, serait juste de réaliser les configurations expliquées plus loin, ce n’est pas le cas de toutes les éditions non-Enterprise de SQL Server 2005 ou SQL Server 2008 64 bits.

En effet, pour les éditions 64 bits Standard de SQL Server 2005 ou SQL Server 2008 il faut au minimum télécharger le cumulative update package 4 du SP3 si SQL Server 2005 est utilisé, ou le SP2 (ou cumulative update package 2 du SP1) si SQL Server 2008, puis placer la trace 845 dans les paramètres de démarrage de l’instance SQL Server après configuration du SecPol. Plus de détails ici : http://support.microsoft.com/kb/970070.

Notez également que dans le cas de SQL Server 2008 R2 (non-Enterprise Edition), l’activation de la trace 845 est toujours nécessaire, sauf à partir de SQL Server 2012.

Pour incorporer la trace 845 dans les paramètres de démarrage de SQL Server, suivez les étapes suivantes :

  • Ouvrez SQL Server Configuration Manager (gestionnaire de configuration de SQL Server trouvable dans Démarrer>Tous les programmes>Microsoft SQL Server 2008 (R2) > Outils de configuration).
  • Faites un double-clic ou un clic-droit sur l’instance à traiter (dans notre cas MSSQLSERVER, qui correspond à l’instance par défaut qui a été installée) pour y accéder à ses propriétés :


  • Dans l’onglet Avancé, au sein de la boîte de dialogue qui s’ouvrira, placez ;-T845 (le point-virgule est important) à la fin de la section Paramètres de démarrage :


    Concrètement la valeur de Paramètres de démarrage pourrait ressembler à cela :

Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\SQL Server\DATA\master.mdf;-eC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\SQL Server\Log\ERRORLOG;-lC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\SQL Server\DATA\mastlog.ldf;-T845
  • Redémarrez l’instance.

    Cette opération peut se faire directement au sein du SQL Server Configuration Manager, en effectuant un clic-droit sur l’instance, dans Services SQL Server, et en sélectionnant Redémarrer :


Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s