[SQL Server] Configuration élémentaire d’une instance OLTP SQL Server 2008 (R2)

Ce billet traite de quelques éléments de configuration usuels d’une instance OLTP (OnLine Transactional Processing, ou Database Engine) SQL Server 2008 ou SQL Server 2008 (R2).

Paramétrage de la mémoire

Activation d’AWE (SQL Server 32 bits seulement)

L’activation de /3GB et/ou de /USERVA et/ou de /PAE ne permettant pas à SQL Server de profiter pleinement de la RAM supplémentaire, c’est là qu’intervient toute l’utilité d’AWE (Address Windowing Extensions).

AWE est une fonctionnalité qui permet à des systèmes, basés sur une architecture 32 bits, d’accéder à de grandes quantités de mémoire.

Dans le cas de SQL Server, AWE n’est utile que si la mémoire physique disponible est supérieure à l’espace d’adressage virtuel en mode utilisateur, et que plus de 4 Go de RAM est demandé par l’instance SQL Server.

Pour activer AWE (qui ne nécessitera aucun redémarrage de l’instance SQL Server et/ou de l’OS), suivez les étapes suivantes :

  • Ouvrez SSMS (SQL Server Management Studio), comme sur la figure ci-dessous :


  • Après connexion à l’instance SQL Server via SSMS, double-cliquez sur le nom de ladite instance, tout en haut de l’explorateur d’objets pour accéder à ses propriétés :


  • Dans la boîte de dialogue qui s’ouvrira, allez dans l’onglet Mémoire :


  • Et activez AWE.

Comme vous pouvez le souligner, AWE est, par défaut, désactivé. Il vous faut donc l’activer :


Remarquez qu’il est possible d’effectuer cette opération en T-SQL, au sein de l’analyseur de requêtes de SSMS. Pour ce faire :

  • Ouvrez une fenêtre d’analyse de requêtes via Nouvelle requête (juste au-dessus de l’explorateur d’objets):


  • Exécutez le script suivant :
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'awe enabled', 1
RECONFIGURE WITH OVERRIDE
GO

Pour savoir si AWE a bien été activé, 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_readerrorlog
GO

Puis rechercher, au sein du résultat de la requête (ou au sein de l’ERRORLOG), la ligne suivante :

Address Windowing Extension enabled.
Il faut noter que la quantité de mémoire AWE allouée à une instance SQL Server ne peut être libérée que si celui-ci est arrêté ou redémarré. De plus, AWE n’est d’aucune utilité sur pour les instances SQL Server 64 bits.
De plus, 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, voir plus loin) et de procéder au verrouillage des pages en mémoire (voir billet [SQL Server] Performance tuning : optimisation de la mémoire).


Paramétrage de Min server memory et de Max server memory

Vu que souvent, il devient nécessaire de laisser un peu de ressources mémoire à quelques petites applications systèmes, il faut idéalement donner une valeur de Maximum server memory de sorte qu’au moins 2 Go soient laissés à l’OS.

Pour ce qui est de la valeur de Min server memory, il est souhaitable d’allouer une taille d’au moins 1024 Mo (ou 1 Go). En effet, cela permettrait de s’assurer que le buffer pool (allocateur permettant de fournir de la mémoire aux pages de données) dispose toujours d’au moins 1 Go de mémoire (qui ne seront pas laissés à d’autres systèmes).

La taille minimale ne doit, tout de même, pas être supérieure à la taille maximale allouée à l’instance SQL Server.

Pour paramétrer le Min server memory et le Max server memory de l’instance SQL Server, suivez les étapes suivantes :

  • Suivez les mêmes étapes que précédemment concernant l’accès aux propriétés du serveur sous SSMS.

Au sein de la section Memory, vous noterez que par défaut, la taille maximale de la mémoire consommable par le buffer pool de SQL Server est paramétré à 2 To. Autrement dit, SQL Server est configuré, par défaut, de sorte qu’il puisse consommer toute la RAM de l’OS, ce qui n’est pas une bonne idée du point de vue des performances.

  • Paramétrez la taille minimale et maximale de la mémoire de SQL Server.

L’OS ayant 10 Go de RAM, nous pouvons nous permettre de lui laisser 2 Go, affectant ainsi 8 Go maximum à SQL Server (et 1 Go minimum), ce qui est correct. Et du côté de la mémoire minimale, on procède à l’affection de 1 Go de RAM à SQL Server de sorte que le buffer pool dispose toujours d’au moins 1024 Mo de pages de mémoire non-utilisables par d’autres ressources :


Vous pouvez aller ici pour avoir une idée d’une bonne approche de paramétrage de la mémoire de SQL Server.

Notez bien que les configurations faites via le Min server memory et le Max server memory (qui ne nécessitent aucun redémarrage de l’instance SQL Server ou de l’OS) ne permettent que d’allouer (ou réduire) de la mémoire au buffer pool et non à toute l’instance SQL Server. Cela signifie que même si 6 Go de RAM sont affectés à (et consommés par) SQL Server, il est possible qu’il y ait un excédent de quelques Mo supplémentaires à cause de la possible utilisation de pages hors-normes (i.e., pages multiples de plus de 8 ko et/ou pages larges de 16 ko), sachant que le buffer pool ne fournit que des pages « simples » (i.e., de taille normalisée de l’ordre de 8 Ko). Les pages hors-normes sont puisées au sein de l’OS via le virtual memory allocator d’un nœud de mémoire SQL Server qui va utiliser l’API Windows VirtualAlloc.

Il est possible d’effectuer le paramétrage de la mémoire via T-SQL. Pour ce faire :

  • Lancez une fenêtre d’analyse de requêtes, sous SSMS (si ce n’est déjà fait).
  • Exécutez le script T-SQL suivant :
sp_configure 'show advanced options', '1'
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'min server memory', '1024'
sp_configure 'max server memory', '8000'
GO
RECONFIGURE WITH OVERRIDE
GO

 

Paramétrage du fill factor

Pour paramétrer le fill factor par défaut de l’instance SQL Server pour toutes les bases de données (et indexes), suivez les étapes suivantes :

  • Dans les propriétés de l’instance, allez dans la section Paramètres de base de données puis modifiez la valeur du fill factor:

Comme vous pouvez le noter sur la figure ci-dessous, la valeur 85 a été choisie, ce qui est un bon compromis pour les indexes fréquemment accédés en écriture (mises-à-jour) et ceux fréquemment en accédés en lecture.


  • Cliquez sur OK pour valider la modification effectuée.

Via T-SQL, vous pouvez exécuter le script suivant au sein d’une fenêtre d’analyse de requête sous SSMS :

sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'fill factor', 85
GO
RECONFIGURE WITH OVERRIDE
GO

Spécification du chemin d’accès par défaut des fichiers de bases de données

Lors de la création d’une base de données ou d’un filegroup, le moteur SQL choisit, par défaut, le disque où ont été installés les fichiers d’installation de SQL Server. Afin de mieux respecter la répartition des fichiers de données et de logs à chaque création d’une base de données, il faut donc définir le chemin d’accès par défaut desdits fichiers.

Pour ce faire, il vous suffira simplement d’accéder à Paramètres de base de données, au sein des propriétés du serveur, puis de modifier l’emplacement de la base de données par défaut :


Paramétrage réseau

N’hésitez pas à jeter un coup d’oeil ici.

Protocoles et accès distants

Par défaut, le protocole Shared Memory (Mémoire partagée) est activé, ainsi que le protocole TCP/IP et cela, contrairement au protocole Named Pipes (Canaux nommés).

Vous pouvez y jeter un coup d’œil au sein de SQL Server Configuration Manager et l’activer comme suit


Cette modification nécessitera un redémarrage de l’instance SQL Server (soit via services.msc, soit via l’onglet Services SQL Server de SQL Server Configuration Manager, soit via clic-droit sur le nom de l’instance au sein de l’explorateur d’objets de SSMS, puis sélection de Redémarrer).

Du côté des accès distants, il est nécessaire de s’assurer que l’option Allow Remote Access, au sein de l’instance SQL Server, est activée, et l’activer le cas échéant.

Comment s’y prendre ? Deux approches possibles :

  • Via SQL Server Configuration Manager :
    • Accédez aux propriétés de l’instance SQL Server (via clic-droit sur le nom de l’instance, tout en haut de l’explorateur d’objets de SSMS, comme vu dans les sections précédentes).
    • Allez dans Connexions
      puis activez Autoriser les accès distants à ce serveur.


  • Via T-SQL :
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'remote access', 1
GO
RECONFIGURE WITH OVERRIDE
GO
N’activez pas le protocole VIA (ou Virtual Interface Adapter, qui permet de travailler avec du matériel VIA) qui est désormais obsolète et voué à disparaître, tout comme l’option Nécessite des transactions distribuées pour la communication de serveur à serveursauf avis contraire, qui disparaîtra au-delà de SQL Server 2012.

Par ailleurs, si vous installez BizTalk Server, désactivez le protocole Shared Memory, pour des raisons de performances.

Notez que si vous souhaitez que l’instance puisse travailler avec des requêtes distribuées (via OPENROWSET ou OPENDATASOURCE), il faut également activer la fonctionnalité Ad Hoc Distributed Queries. Cela peut se faire comme suit :

sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Pour des raisons de sécurité, n’activez Ad Hoc Distributed Queries que si vous comptez utiliser OPENROWSET ou OPENDATASOURCE au lieu d’un linked server (avec OPENQUERY).


SQL Server Browser et configuration de ports d’écoute

Par défaut, le moteur SQL écoute sur le port statique 1433. Et de manière générale, sauf cas particuliers, il n’est pas nécessaire de modifier les configurations par défaut des ports d’écoute de SQL Server.

Toutefois, si vous privilégiez l’utilisation de ports dynamiques, et que vous disposez de plusieurs moteurs SQL au sein du même serveur, il est recommandé d’activer le SQL Server Browser afin qu’il puisse fournir les bons ports aux bonnes connexions. Ce dernier (qui utilise le port UDP 1434) permet de récolter les informations de connexion d’une instance (notamment son port d’écoute) pour ensuite les fournir à toute requête de connexion.

S’il est généralement requis dans le cas d’instances utilisant des ports TCP/IP dynamiques c’est simplement parce qu’il se charge, lui-même, de résoudre le numéro de port de chaque instance concernée par une connexion, ce qui peut être utile si, entre-temps, ledit port a changé de numéro (suite l’utilisation de l’ancien numéro de port par un autre processus après un redémarrage, par exemple).

Du fait donc de la sensibilité de son travail, il est plus recommandé qu’en cas d’activation, un compte de service avec des droits minimaux lui soit attribué. Et cela, pour des raisons de sécurité.

Notez que le SQL Server Browser est relativement inutile dans le cas où, au sein d’un serveur, il n’y a pas plusieurs moteurs SQL d’installé (comme c’est notre cas ici).

Bien que cela puisse décharger l’utilisateur de toute nécessité de spécifier manuellement un port à chaque moteur SQL à installer, il est recommandé, dans le cas d’un cluster, d’opter pour un port statique (choix par défaut de SQL Server), et non dynamique.

Sur le plan pratique, pour :

  • Activer le SQL Server Browser :

En ligne de commande vous pouvez simplement taper net start sqlbrowser. Ou bien, au sein de SQL Server Configuration Manager, allez dans Services SQL Server puis double-cliquez sur SQL Server Browser pour le démarrer :


Assurez-vous, au préalable, qu’il ne soit pas désactivé, sinon activez-le en accédant à ses propriétés (via clic-droit) :


  • Spécifier un numéro et type de port (dynamique ou statique) :

Allez dans le SQL Server Configuration Manager, puis dans la section Configuration du réseau SQL Server\Protocoles pour MSSQLSERVER (nom de l’instance par défaut), puis double-cliquez sur TCP/IP pour accéder à ses propriétés :


Cela aura pour effet de lancer une boîte de dialogue au sein de laquelle, il faut aller dans l’onglet Adresses IP :


L’instance pouvant disposer de plusieurs IPs (et ports), vous pouvez spécifier pour chaque IP le numéro de port et son type (statique ou dynamique). Pour utiliser un port dynamique, spécifiez la valeur 0 pour Ports TCP dynamiques. Si vous souhaitez juste utiliser un port statique, spécifiez la valeur adéquate pour Port TCP au sein de chaque IP concernée, et assurez-vous que Ports TCP dynamiques soit vide.

Notez que vous pouvez directement effacer toute éventuelle valeur des deux types de ports pour chaque IP (IP1, IP2, IP3,…, IPn), puis, ensuite, spécifier la valeur souhaitée pour le type de port choisi au sein d’IPAll :


Paramétrage du MAXDOP (Maximum degree of parallelism)

Pour configurer le MAXDOP de SQL Server (on suppose que l’on possède 8 CPUs, ce qui reviendrait donc à affecter seulement 4 à SQL Server), vous pouvez le faire :

  • Soit en accédant dans les propriétés de l’instance SQL Server via SSMS, et dans l’onglet Avancé pour (dé)cocher les processeurs que l’on souhaite utiliser :


  • Soit en exécutant la commande suivante au sein de l’analyse de requêtes de SSMS :
EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure N'max degree of parallelism', N'4'
GO
RECONFIGURE WITH OVERRIDE
GO
Notons qu’il s’agit seulement d’instructions dites « générales ». Il peut, en effet, arriver de fournir exceptionnellement un nombre maximum de processeurs à l’instance SQL Server si elle est régulièrement emmenée à traiter des opérations impliquant un très grand nombre de ressources. Et c’est le cas, par exemple, des traitements analytiques.
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