Optimisation des bases de données MS SQL Server

Peut-être n'avez vous jamais remarqué ce fichier de nom pagefile.sys (3) situé en racine de votre disque C (4). C'est lui qui assure la mémoire virtuelle de l'OS. En fait lorsqu'une application n'a pas assez de mémoire pour s'exécuter, ou encore pour charger toutes les données qu'elle doit utiliser en RAM, le fichier pagefile.sys sert de réservoir à ce trop-plein. Devinez quel algorithme est utilisé pour décharger dans ce réservoir certaines parties de la RAM ? Notre fameux LRU (5) ! Autrement dit les pages de RAM les plus anciennement scrutées se déversent dans le fichier de mémoire virtuelle afin de faire profiter d'un espace RAM libre au nouvel arrivant.

Mais cette gestion à travers le disque a un coût assez élevé : il faut écrire et lire ce fichier sans arrêt et l'on sait dorénavant que le temps de réponse d'un disque est incommensurablement plus long qu'une lecture en mémoire vive ! L'idée est alors de remplacer ce fichier par de la RAM… Puisque le processeur n'est pas capable d'adresser plus de 4 Go de RAM, faisons « glisser » la RAM supplémentaire vers le processeur pour que ces nouvelles parties de mémoire lui soient accessibles. Imaginez un jardinier, un tuyau et un quelques bacs à fleurs. Le bac à fleurs le plus éloigné est situé à 30 mètres du point d'eau. Mais son tuyau ne fait que 20 mètres. Comment lui sera-t-il possible d'arroser ce maudit bac à fleurs ? Tout simplement en amenant ledit bac à portée du tuyau puis en le replaçant à son origine.

En informatique cette technique a pour nom la translation d'adresse.

En bref, Windows Server (2000 comme 2003) permet de faire ce que Microsoft appelle AWE pour Adress Windowing Extension, c'est-à-dire de translater des pans entiers de mémoire un peu à la manière des fenêtres coulissantes d'une baie vitrée !

Certes Windows 32 bits n'est toujours pas capable de dépasser la limite de 4 Go, et il faudra toujours consacrer du temps à la gestion de cette pagination, mais c'est sans commune mesure en matière de temps de réponse avec les exécrables délais que l'on obtient pour accéder à un disque. Et certaines éditions de Windows permettent de paginer en RAM jusqu'à 64 Go de mémoire vive.

Attention cependant : dépasser la limite de 4 Go n'est possible que pour certaines versions de l'OS Windows Server en combinaison avec certaines versions de SQL Server. Sachant qu'une réinstallation de l'OS et du SGBDR est la seule solution si l'on s'est trompé, il est donc nécessaire de bien anticiper son coup.

Le 64 bits permet d'aller encore beaucoup plus loin dans l'adressage RAM. En fait, en théorie, il permet d'adresser 18 446 744 073 709 551 999 octets (6)… En l'occurrence Windows édition 64 bits est encore limité à 512 Go de RAM, mais nous savons qu'en informatique, la réalité rattrape parfois rapidement la fiction !.

Règle n°1 : adaptez la RAM à la fenêtre de données. Prévoyez la RAM en quantité suffisante pour que les données les plus utilisées soient toujours en mémoire.

En fait des SGBDR comme SQL Server sont capables de rechercher et réserver sur le disque les meilleurs emplacements de stockage d'un fichier afin de minimiser les temps d'accès, pour peu qu'on leur demande.

Par défaut, lorsque vous créez une base de données, SQL Server créé deux petits fichiers : l'un contient les données, l'autre le journal. Ces deux fichiers ont une taille minimale et chaque fois qu'il sera nécessaire de l'augmenter pour y stocker des informations, alors une opération de croissance de fichier sera entreprise.

Dans ce cas, si l'on observe ce qui se passe durant la vie de la base, on se trouve confronté à un phénomène qui va diminuer sensiblement les performances d'accès. Ce phénomène, c'est la fragmentation physique. Et il n'est pas possible d'y remédier après coup si la base de données doit être exploitée en continu !

Observons concrètement ce qui se passe dans le cas d'un serveur mono disque pour une base de données que l'on va créer avec les options par défaut.

Voici comment petit à petit, le disque se structure :

• En rouge figurent les fichiers de l'OS. Ils se trouvent en tête du disque parce qu'ils ont été installés en premier. En bleu figure « l'exécutable » SQL Server : en fait une collection de DLL et de fichier contenant divers programmes s'interfaçant avec les services SQL Server.
• En jaune figure le début de notre fichier journal de la base, et en vert, le fichier des données.
• Enfin, en gris figure un fichier quelconque, par exemple une sauvegarde.

On constate qu'au fur et à mesure du remplissage de la base de données, le fichier du journal, comme celui de la base de données se fragmente. Plus la base va vivre et plus la fragmentation sera prononcée.

Cette fragmentation impose des lectures du fichier des données qui vont, en termes de temps de réponse, du médiocre à l'exécrable du fait des nombreux sauts que la tête de lecture doit subir pour lire une continuité de données.

Or MS SQL Server est doté d'un algorithme, qui, lorsque vous demandez, à la création de la base, des fichiers de taille fixe, recherche le meilleur emplacement sur le disque afin de minimiser le trajet des têtes de lecture…

Peu de gens le savent, et c'est particulièrement dommage.

Autrement dit, lors de la création de la base, vous pouvez déjà optimiser la lecture et l'écriture physique des disques. Il suffit pour cela de créer les fichiers de la base, sur un disque vierge ou préalablement formaté, avec une taille suffisante pour y prévoir 3 à 5 années d'exploitation, c'est-à-dire la durée de vie moyenne d'un serveur…

Si vous faites cela, et en particulier sur un disque neuf ou dédié, vous constaterez deux phénomènes :

• le premier est que ce sont les pistes les plus proches du bord externe du disque qui seront utilisées ;
• le second est que si le disque est multiplateau (et ils le sont presque tous) alors SQL Server choisira ses granules (8) sur tous les plateaux.

Un jour, créant ainsi une base de données pour un des plus gros sites web d'e-commerce avec cette technique, le DBA, peu au fait de la chose, me montra dans l'outil d'analyse de fragmentation de Windows combien j'avais tort ! Il voyait que le fichier créé était coupé en quatre parties égales et me lança « vous voyez bien que ça marche pas vot'truc ! Le fichier est fragmenté ! » Alors je lui ai demandé de me fournir les caractéristiques techniques des disques du serveur et faisant le pari que les disques contenaient quatre plateaux. À la lecture de la documentation, je criai « Bingo ! ».

En fait il faut comprendre que les têtes de lecture des différents plateaux sont solidaires. Si l'une lit au bord, l'autre ne peut lire l'extérieure. En groupant les granules de stockage sur les mêmes cylindres virtuels, SQL Server créé des fichiers qui minimisent le déplacement des têtes. Dès lors le temps d'accès qui est un temps moyen devient beaucoup moins proche de la mesure de ce que l'on obtiendra finalement avec une telle stratégie. Ainsi, le critère le plus sensible sera donc la vitesse de rotation, car en admettant que la tête ne bouge plus, alors toute la différence se fera entre un disque avec un rpm de 7200 et un autre de 18 000 (une amélioration de 250 %  !).

Un internaute a écrit un très bon article sur ce phénomène. Il s'intitule « le disque le plus rapide du monde » (9).

Nous savons que le fichier contenant les données ira toujours en croissance. Rares sont les bases de données dont le volume des données stockées diminue au fil du temps. De plus en organisant ses tables de la manière la plus intelligente qui soit (clefs cluster sur des colonnes auto-incrément ou horodatage), alors les lignes les plus fréquemment accédées (généralement les plus récentes) seront regroupées plutôt vers la fin de la partie du fichier qui contient les données, et cela quelles que soient les tables. Dans ce cas, nous bénéficions d'un double avantage : pas de fragmentation physique du fichier, faible fragmentation logique des lignes les plus lues des différentes tables.

Règle n°2 : choisissez des disques dont la vitesse de rotation est la plus élevée possible.

Car l'autre phénomène désagréable et auquel il est difficile de remédier, c'est la fragmentation physique du fichier. Lorsque l'on crée un fichier, quel qu'il soit, il est ordinairement créé avec une taille minime, en fait la plus petite taille possible en regard des données à y stocker. Si ce fichier doit ensuite contenir une plus grande quantité d'informations, alors il faudra qu'il demande à l'OS de nouvelles granules à lui adjoindre afin de gérer cette croissance. Dès lors l'ensemble du fichier est constitué de granules éparses acquises au fur et à mesure des opérations de croissance. En effet, il y a fort peu de chances, surtout si l'activité de votre PC est intense, que ces granules soient contiguës. Or cette non-contiguïté conduit à lire l'intégralité d'un fichier en sautant d'emplacement en emplacement ce qui dégrade singulièrement les performances de la lecture. Bien évidemment ce phénomène n'est pas grave s'il s'agit d'un fichier Word ! En revanche pour un fichier de base de données il en va tout autrement.

Cette fragmentation est bien connue puisqu'un outil de défragmentation est présent dans la palette des outils systèmes de Windows.

La particularité d'un fichier de données d'une base est qu'il est en permanence ouvert à l'usage exclusif du serveur SQL. Pourquoi ? Tout simplement parce que le serveur doit pouvoir à tout moment et sans préavis écrire des données. D'où un verrou d'écriture permanent dès que la base est active.

Peut-on défragmenter un fichier de base de données fragmenté ? Oui, assurément. Mais pour cela il faut arrêter l'exploitation de la base, détacher le fichier de données de la base, procéder à la défragmentation au niveau de l'OS. De plus le chaînage interne effectué par SQL Server sur les pages du fichier de la base sera bouleversé. Il y a fort à parier qu'au redémarrage SQL Server mettra un certain temps avant de retrouver ses petits… Bref un travail incompatible avec un service efficace et continu des données !

Le plus simple est donc de créer des fichiers de données d'une taille suffisante à absorber le volume de plusieurs années d'exploitation de la base de données lors de la création de celle-ci.

Règle n°3 : créez des fichiers de taille fixe.

Quelques astuces supplémentaires nous permettent de gagner encore plus de temps :

• en prenant un disque très largement surdimensionné, on bénéficie encore plus de cet effet vitesse de rotation ;
• en dédiant un disque uniquement aux données et un autre au journal vous paralléliserez les lectures et écritures, ce qui fera que l'écriture du journal n'aura pas à attendre la fin d'une lecture des données et vice versa !
• en multipliant les fichiers de données et du journal sur différents disques physiques, vous accélérerez encore les accès, notamment des insertions, car SQL Server les répartit de manière équitable dans tous les fichiers ;
• en plaçant sur des fichiers distincts les données d'un côté et les index de l'autre, vous pourrez paralléliser mieux encore certaines requêtes ;
• en partitionnant les données des plus grosses tables sur différents disques physiques, vous bénéficierez aussi d'un parallélisme de traitement pour chacune des partitions.

Mais veuillez noter que tout cela n'est intéressant que si l'on considère différents disques physiques. En aucun cas, des disques virtuels, partitions d'un même disque physique n'apporteront un tel gain. Ce serait même le contraire : le partitionnement d'un disque physique en multiples disques virtuels, diminue de manière très sensible les performances. Et si l'on opte pour un seul disque sur son serveur, mieux vaut ne pas le partitionner : l'équilibrage du taux d'occupation n'en sera que plus facile.

Règle n°4 : répartissez les fichiers sur différents disques physiques et les données sur différents fichiers.

Dans les grosses bases de données, celles dont les volumes dépassent la capacité des disques actuels, on a de plus en plus souvent recours à un dispositif externe de stockage. En cette matière, deux technologies se distinguent : le NAS et le SAN.

Disons-le tout de suite, le NAS est l'épouvante de SQL Server. En effet, un NAS ou Network Area Storage suppose un découplage total entre le PC et le stockage. Des données sont envoyées sur le réseau pour que le NAS écrive ou lise le disque. SQL Server ne s'adresse donc plus à un disque, mais à une boîte noire pour laquelle il ne dispose d'aucune information et qui lui répond quand elle veut, car n'oublions pas qu'un réseau de type Ethernet comme TCP/IP est par nature indéterministe et ne garantit en aucune manière si ce qui est envoyé sur le réseau parviendra un jour à son destinataire… Situation idéale, on en conviendra, pour obtenir les performances les plus lamentables, d'autant que plusieurs serveurs et même certains postes clients peuvent s'adresser au NAS pour de la lecture comme pour de l'écriture ! Heureusement SQL Server ne permet pas un tel fonctionnement de manière native. Mais si vous voulez quand même pouvoir disposer d'une telle fonctionnalité, alors sachez qu'il y a moyen de le faire, à vos risques et périls, en positionnant des flags de trace particuliers (10).

En revanche la technologie SAN est bien plus intéressante : le « Storage Area Network » est en fait un espace de stockage multidisque dédié à un serveur. Dès lors la connexion entre le serveur et le SAN est directe (la plupart du temps sous la forme d'une fibre optique) et exclusive.

Le SAN apporte donc un gros intérêt pour les, VLDB (11). Mais pour être efficace avec SQL Server, un SAN se doit d'être hautement administrable. S'il se comporte comme une boîte noire (partitionnement, agrégats incontrôlés…), on y retrouve les défauts inhérents à la fragmentation. À l'inverse, si ce SAN permet de définir soi-même des zones de stockage sur des LUN (Logical Unit Number) et en les créant de manière intelligente, on se trouve dans le meilleur des deux mondes. Pensez donc à éviter les SAN qui ne permettent que le « Target-Level Zoning » et masquent ainsi la structure physique des agrégats de disques.

Règle n°5 : bannissez les NAS. Préférez les SAN hautement administrables.

Que vous ayez ou non des difficultés à estimer la taille nécessaire de vos fichiers, le mieux est de toujours leur donner une grande taille, mais surtout de les prévoir en croissance automatique (avec un pas fixe assez large, que je fixe pour ma part à 250 Mo généralement).

Le calcul du volume des données n'est pas toujours chose aisée. Mais il existe différentes techniques…

• Calcul manuel : calculer la longueur de chaque ligne de chaque table et multiplier par le nombre de lignes probable. Multiplier par 1,25 à 1,60 pour l'indexation et prévoyez une marge (pour des petites bases, je multiplie par deux).
• Calcul automatique : certains outils de modélisation de données comme Power Designer (ex AMC Designor) de Power Soft / Sybase permettent de calculer automatiquement le volume de la base et de chaque table, pourvu qu'on leur fournisse le nombre de lignes de chaque table.
• Calcul estimatif : il suffit de remplir la base de données avec un jeu d'essais assez consistant. En doublant par exemple ce jeu d'essais, on peut estimer le volume à terme de la base à l'aide d'une simple règle de trois.

Ceci pour les fichiers de données. Pour le journal, ce calcul est plus difficile qu'il n'y parait. Les paramètres qui influent le plus sont : le nombre de transactions par minute, le volume des transactions, la concurrence, la fréquence et le mode des sauvegardes et le mode de récupération de la base. Pour ma part je considère qu'en mode full (12), une base OLTP de taille et d'accès moyens (une centaine d'utilisateurs) un journal de transaction de 30 à 50 % de la taille de la base est généralement très suffisant.

Règle n°6 : estimez la taille de votre base au terme de 3 à 5 années d'exploitation.

De toutes les manières, il conviendra d'auditer régulièrement la base de données et ses fichiers afin de savoir si vous êtes dans le vrai. Ainsi en laissant des fichiers de grandes tailles avec une croissance automatique par grands blocs fixes (j'utilise 250 Mo comme pas d'incrément pour la croissance de mes fichiers), vous n'aurez pas de surprise. D'autant que les opérations de croissance de fichier, telle la Loi de Murphy, ont le désagrément de se produire généralement aux plus mauvais moments. Croyez-vous statistiquement qu'une telle opération sera entreprise au moment où le serveur est au repos ? Que nenni ! Elle a toutes les chances de se produire dans la transaction la plus longue, celle justement qui insère 32 847 nouvelles lignes dans la plus grosse et la plus scrutée de vos tables, augmentant considérablement le temps global de la transaction, et par là même la durée des verrous, favorisant ainsi la contention !

Dernier avantage de prévoir un fichier de données (et accessoirement un fichier de journal) suffisamment dimensionné : vous éviterez le coup du disque plein, surtout si vous êtes un éditeur de logiciel et que vous ne maîtrisez pas le contenu des disques du serveur de votre client. Ce coup du disque plein se caractérise généralement par le fait qu'au tout début de l'exploitation, les disques du serveur étant quasi vides, la tentation est grande d'y stocker n'importe quoi : les photos de la belle mère lors de la dernière réunion de famille, comme les rapports du service comptabilité que personne ne lit et qui font tous 4 Mo du fait des nombreux graphiques statistiques. Dans le cas où le disque commencerait à être saturé, la base de données ainsi proprement dimensionnée pourra toujours continuer à vivre.

Règle n°7 : justifiez votre travail de DBA par une technique de prévention : auditez régulièrement la croissance et gérez là par anticipation.

Nous avons déjà commencé à parler de la répartition des fichiers et des données sur différents disques. Voyons cela plus en détail et notamment sous l'angle des mécanismes de redondance de type RAID…

Vous le savez sans doute un élément essentiel de la sécurité des données est la redondance. Celle introduite par un système RAID (Originellement Redundant Arrays of Inexpensive Disks, puis modifié en Redundant Arrays of Independant Disks) est indispensable à une bonne tolérance de panne afin que la base de données puisse continuer son exploitation en cas de perte d'un disque (2e cause de panne après celle de l'alimentation des serveurs).

Pour autant, les systèmes RAID ne permettent pas toujours les mêmes performances qu'un simple disque (13). Voici un tableau comparant les différents niveaux de RAID à un disque unique :

Le RAID 5 est un compromis entre redondance et coût. Il constitue un bon point d'entrée pour SQL Server. Néanmoins on peut améliorer la chose de la manière suivante :

• placer les fichiers du journal (plus fréquemment écrit) sur un RAID 1 ;
• placer tous les fichiers (données et journal) sur un RAID 1 ;
• placer les fichiers du journal sur un RAID 10 (ou 0+1) et les fichiers de données sur du RAID 5 ou 1 ;
• placer tous les fichiers (données et journal) sur un RAID 10.

Tout ceci doit se faire avec des considérations d'estimation du volume à stocker et du coût du sous-système disque.

Si l'on conçoit en outre qu'il est nécessaire pour des raisons de récupération en cas de défaillance grave, de séparer sur des agrégats RAID différents, les fichiers de données des fichiers du journal, alors le sous-système disque doit comprendre au moins deux agrégats (14) soit au minimum 4 disques.

Bien entendu ce qui est applicable aux fichiers de la base de données est aussi intéressant à appliquer sur les différents fichiers fortement sollicités par le serveur SQL et l'OS.

On pourra par exemple placer sur un agrégat RAID spécifique les éléments suivants :

• fichiers de l'OS et exécutables (SQL Server et autres services associés), hormis fichier de pagination pagefile.sys ;
• fichier de pagination pagefile.sys ;
• fichiers de la base de données tempdb.

On gagnera encore plus pour les très grandes bases à utiliser plusieurs agrégats pour les fichiers de données, voire aussi pour le journal. Dans ce cas il faudra choisir entre performances à la mise à jour (écriture) ou à la lecture.

Si l'on veut de la performance en écriture, il suffit de disposer de plusieurs fichiers sur plusieurs agrégats pour les données comme pour le journal sans rien spécifier de particulier au niveau de la base.

En revanche si l'on veut des performances en lecture, il est intéressant de séparer les index des données brutes, voire d'isoler certaines tables ou de les partitionner (15) sur des agrégats spécifiques.

Notez que les modes d'écriture des fichiers de données et des fichiers du journal sont fondamentalement différents. Autant l'écriture dans un fichier de données se fait à un emplacement fréquemment aléatoire et finalement peu fréquent, autant le journal est écrit en fin de fichier de manière séquentielle (write ahead), pour un volume assez fort et assez fréquent. C'est pourquoi il est fréquent de placer les données et le journal sur des modes RAID différents.

Dans tous les cas, bannissez l'utilisation de systèmes RAID logiciels. Ces derniers diminuent singulièrement les performances et reportent la criticité globale du système sur le soft. N'hésitez pas à utiliser différents contrôleurs RAID si votre base est grande. Chacun des contrôleurs indépendants apportera une solution de sécurité supplémentaire par sa redondance.

Règle n°8 : choisissez le bon niveau de RAID et répartissez les fichiers de données, comme ceux du journal sur différents agrégats. Faites de même avec les exécutables, le fichier de pagination de la mémoire virtuelle (pagefile.sys) et les fichiers de la base tempdb.

Un disque, lorsqu'il commence à être rempli, ressemble à un jeu de taquin. Ce jeu mathématique inventé par Sam Loyd en 1873 consiste à découper une image en petits carrés, les mélanger et en retirer un. Pour gagner, il faut recomposer l'image en faisant glisser les petits carrés verticalement ou horizontalement.

En fait un disque dont le taux de remplissage avoisine la saturation est un gigantesque taquin dans lequel une seule granule de stockage est libre. Au moment d'enregistrer un fichier que vous venez de modifier, il faudra au système opérer l'enregistrement du fichier granule par granule. L'opération va devenir extrêmement longue et mobiliser tous les accès disques.

C'est pourquoi on recommande de ne pas dépasser un certain taux d'occupation d'un disque. En général il faut prévenir à 70 % et guérir à 80. Au-delà de 90 % de taux d'occupation, l'allongement des temps de réponse commence à se faire sentir !

Bien entendu la survenance d'un tel seuil est facilitée si les disques ne sont pas partitionnés.

Règle n°9 : surveillez les taux d'occupation des différents disques et tentez de ne jamais dépasser 70%.

Une solution ultime pour obtenir de bonnes performances si votre base tempdb est fortement saturée consiste à la placer en mémoire. Différents fabricants proposent ce que l'on appelle des « Ram disque ». Autrement dit une mémoire vive qui se substitue à un disque. Pas de panique, les fabricants ont bien prévu les filets de sécurité nécessaires. Ces dispositifs sont généralement vendus avec une batterie et un disque de secours qui en cas de panne de courant s'assure que les données en mémoire sont flashées sur le disque. Avec un tel système combiné à une forte et judicieuse utilisation de la tempdb, les résultats peuvent être spectaculaires !

Jetez donc un coup d'œil aux produits de Texas Memory Systems par exemple…

Enfin, l'un des points maintenant le mieux maîtrisé dans la technologie des disques est l'algorithme d'accès au fichier. Dans un mauvais disque, les demandes d'accès (écriture ou lecture) sont effectuées dans l'ordre de la demande (FCFS : First Come First Serve). Dans un bon disque, ils peuvent être effectués dans l'ordre du plus près servi (Shortest Seek Time First), mais cet algorithme peut conduire à la famine du système. En général il est amendé afin de ne jamais conduire à une telle situation. Mieux le parcours en « scan » aussi appelé « lift », car c'est l'algorithme de parcours des ascenseurs intelligents, permet de parcourir le disque dans toute la plage des pistes et de servir au passage les demandes, sans tenir compte de l'ordre de la demande. Enfin des améliorations notables ont été apportées à cet algorithme telles que le C-Scan ou le « look ».

La procédure sp_configure permet de régler les principaux paramètres pour piloter la mémoire.

Réglages stricts :

Réglages fins :

Autres réglages entraînant de la consommation de mémoire :

Il n'y a pas de réglage disponible pour la gestion des disques. Souvenez-vous simplement qu'il vaut mieux créer une base avec des fichiers de grande taille, si possible fixe, répartir ses fichiers sur différents agrégats de disques et surveiller leur taux de remplissage. Vous éviterez ainsi la fragmentation physique des fichiers et ne serez pas pénalisés en cas de fort flux de mise à jour.

N'oubliez pas non plus d'auditer vos disques afin d'en limiter le taux d'occupation.

Bien que l'installation de SQL Server provoque l'installation de toutes les bases dans un même répertoire, vous pouvez modifier l'emplacement de la base tempdb à l'aide de la commande ALTER TABLE.

Sachez que, lorsque vous créez des index (CREATE INDEX), vous pouvez préciser à l'aide de l'option « sort in tempdb » que le tri pour la création dudit index se fera dans la base de données des objets temporaires.

Enfin, notez qu'il est difficile et coûteux de mouvoir les fichiers d'une base en exploitation, comme de déplacer certaines structures de la base (tables, index…) (17). Mieux vaut donc avoir prévu la répartition de ces éléments lors de la création de la base, par une étude sérieuse des volumétries en jeu.

La procédure sp_configure permet de régler les principaux paramètres pour piloter la gestion des processeurs. De plus le tag MAXDOP permet de limiter l'usage de plusieurs processeurs.

Nous avons vu que SQL Server utilisait peu l'OS puisqu'il utilise son propre OS « SQL OS ». Nous en avions déduit que contrairement à d'autres applications (IIS par exemple) il était parfaitement logique d'assigner 3/4 des ressources RAM à SQL Server et 1/4 à l'OS (swith /3GB dans le fichier boot.ini).

Il en est de même pour les processeurs. SQL Server installé sur une machine dédiée n'a besoin que de 75 % des processeurs physiques.

Ainsi à partir du quadri processeur, il est intéressant de dédier à SQL Server certains processeurs pour laisser les autres à disposition de l'OS.

La répartition de fait à l'aide du paramètre « affinity mask » de la procédure stockée sp_configure.

Un masque d'affinité est une combinaison de bits qui indique par un 1 si le processeur de rang n est utilisé par SQL Server, sinon à 0 c'est l'OS qui l'utilisera. Ainsi pour indiquer à SQL Server que les processeurs 0, 1 et 2 lui sont réservés (et que par conséquent le processeur 3 sur un système quadri est utilisé par l'OS) alors il faut donner au paramètre « affinity mask » la valeur 7 obtenue par la somme de 20 + 21 + 22.

Attention : dédiez toujours les processeurs de poids faible (0, 1, 2…) à SQL Server et ceux de poids forts (… n-2, n-1, n) à l'OS. En effet le processeur de poids le plus fort est la plupart du temps utilisé par les cartes réseau. Or c'est la seule action que SQL dédie à l'OS !

Même si vous avez dédié à SQL Server 6 processeurs sur 8, vous pouvez exiger qu'aucune requête ne prenne plus de 4 processeurs par exemple. Cela fluidifiera les petites requêtes au détriment du temps de réponse des grosses. C'est possible à l'aide du paramètre « max degree of parallelism » modifiable par la procédure stockée sp_configure.

Vous pouvez aussi définir le coût (18) minimum à partir duquel le parallélisme va entrer en jeu. C'est le paramètre « cost threshold for parallelism » de la même procédure. N'oubliez pas qu'un traitement parallèle engendre un travail plus important qu'en série. Il y a donc gain sur la durée, mais perte sur la consommation des ressources.

Enfin, le tag MAXDOP peut être utilisé dans une requête pour lui indiquer de ne pas utiliser plus de n processeur pour être traitée. Cela se fait dans la clause OPTION de la requête, clause spécifique à SQL Server.