Optimisation des bases de données MS SQL Server

Il y a quelque temps, dans un forum consacré à SQL Server sur le site communautaire de developpez.com, un internaute postait un remarquable message. Il avait une procédure complexe longue et coûteuse en traitement qui importait des données dans une base, avec une planification quotidienne de nuit. Un matin quelle ne fut pas sa stupeur de constater que cette procédure qui durait habituellement un peu plus d'une heure, n'était pas encore terminée. Il attendit donc la fin du traitement et constata que ce dernier avait mis près de 10 heures, soit 8 fois plus qu'ordinairement. Que s'était-il passé ?

Nous avons déjà dit que les index sont une aide formidable pour optimiser les requêtes. La pose d'index entraîne la mise en place de statistiques sur les données de l'index, statistiques qui sont calculées à la création de l'index et recalculées lors de manœuvres de remaniement d'index ou encore de manière automatique si cette option est activée. Si les index n'ont pas été recalculés depuis longtemps, alors le moteur SQL constate qu'un décalage existe entre l'échantillon sur lequel le calcul des statistiques a été fait et la réalité des données de la table. Dès lors, le moteur SQL est en droit de considérer que ces statistiques n'ont plus d'intérêt, car elles ne reflètent plus la réalité. Dans ce cas le plan de requête est recalculé sans tenir compte des index suspects afin d'éviter d'utiliser lesdits index jugés obsolètes !

Voilà donc un premier point à surveiller : la mise à jour régulière des statistiques.

Dans une base de moyenne importance, l'option automatique de mise à jour des statistiques peut s'avérer efficace, d'autant que la version 2005 de SQL Server permet de le faire de manière asynchrone.

Dans de très grandes bases (VLDB) il vaut mieux planifier cela aux heures creuses.

Cependant, la seule mise à jour des statistiques n'est pas suffisante. Un index dont les données sont fortement impactées par des mises à jour (INSERT, UPDATE DELETE) peut s'avérer peu efficace en recherche. En effet, au fur et à mesure que les données évoluent, la structure de données qui stocke les index – en fait un arbre équilibré – peut se trouver en défaut : des pages peu pleines, des lignes déplacées, des lectures en zigzag…

Comment cela est-il possible et comment y remédier ?

Pour le remède, la chose est simple, il faut défragmenter l'index ou le reconstruire.

La défragmentation n'est pas une solution optimum. Mais l'intérêt de cette technique, c'est que l'index subsiste pendant la durée de sa réfection.

En revanche lors d'une reconstruction, l'index est détruit et aucune des requêtes qui parviennent à ce moment ne peut l'utiliser. En revanche, la reconstruction permet un index optimal, c'est-à-dire plus dense et organisé de façon optimale.

Notez en outre que la version Enterprise de SQL Server permet une réindexation avec conservation de l'index… Ceci a un coût qui peut s'avérer important notamment pour les très grandes bases. La raison est simple :

pour conserver un accès à l'index pendant sa reconstruction, il faut le dédoubler. Ainsi durant ce processus on se retrouve avec l'ancien index actif jusqu'à la fin du processus et le nouveau, ou plus exactement son remplaçant en train de se construire. Conclusion, il faut prévoir un espace de stockage de deux fois le volume de l'index…

Pour aller vite, car la réindexation de grandes bases et donc de grands index peut prendre du temps, certaines versions de SQL Server permettent de paralléliser le calcul de l'index.

Attention cependant : si la réindexation recalcule les statistiques en même temps, il n'en est pas de même pour la défragmentation. Pensez donc à recalculer les statistiques si vous optez pour la défragmentation. Sachez en outre que vous n'êtes pas obligé de calculer les statistiques sur la totalité des données – ce qui dans de grandes bases peut s'avérer très long –, mais plus simplement sur un échantillon de données.

Mais comment un index arrive-t-il à se fragmenter, se distendre, se désagréger ?

Pour comprendre ce phénomène, il faut savoir comment est constitué un index…

Un index SQL Server est une structure de données organisée en arbre équilibré (1) donc chaque nœud est une page de navigation et chaque feuille une page de données. Comme dans le cas de la table, chaque page contient un nombre variable de lignes ordonnées. Les lignes peuvent être de longueurs différentes si vos types de données dans l'index sont de taille variable (par exemple un VARCHAR).

Un index est dit dense si les pages sont remplies au maximum. Ce maximum n'est jamais 100 %, sauf hasard extraordinaire, car il y a presque toujours dans chaque page un petit espace résiduel où plus aucune ligne ne peut se loger. Un index dense est rapide à lire, car il occupe moins de place et donc coûte moins cher en lecture qu'un index grevé de « trous ». S'il est dense et parfaitement ordonné, alors on dit de l'index qu'il est optimum.

Imaginons un index dense, ordonné, donc optimum, couvrant pour une requête considérée, donc parfait pour notre exemple. Que peut-il lui arriver au fur et à mesure de sa vie ?

Puisque cet index est dense, chaque nouvelle insertion va provoquer la scission de la page de données dans laquelle la référence doit être insérée en deux nouvelles pages. On appelle ce phénomène le « split » de page. L'une des pages contiendra toutes les données avant cette référence, l'autre toutes les données après, et l'une des deux (si possible la moins remplie si l'algorithme est bien fait) contiendra en outre la nouvelle référence.

Bien entendu le phénomène a toutes les chances de se reproduire pour les pages de navigation, car ces dernières peuvent aussi être remplies à 100 % (ou tout au moins avec une valeur ne permettant pas l'ajout d'une nouvelle entrée de navigation). En effet puisque la page feuille a été scindée en deux, il faut bien ajouter une nouvelle référence pour s'aiguiller sur l'adresse de la nouvelle page. Pour peu que l'index comporte différents niveaux de pages de navigation, ce phénomène peut se reproduire jusqu'au sommet, voire provoquer l'apparition d'un niveau supplémentaire dans la structure arborescente de l'index !

N'oublions pas que l'on parle d'arbre équilibré, c'est-à-dire d'arbres dont les feuilles sont toutes au même niveau, ce qui oblige le système à certaines contorsions algorithmiques pour maintenir la structure d'aplomb.

Bref au fur et à mesure des insertions, de nombreuses pages vont se scinder provoquant une augmentation sensible du volume de l'index. La lecture, comme la recherche de l'index sera donc plus longue et moins efficace que dans notre index originel dit optimum.

Étudions maintenant le cas de la suppression…

Si nous supprimons une ligne, la référence est simplement invalidée, à la manière d'une ligne barrée ou d'une mise à blanc à coup de tipex (2). Il y a donc un espace disponible rarement comblé, à moins que par le plus grand des hasards une valeur intercalaire similaire soit insérée peu après.

Si la référence à retirer est la première de la page, alors il faut en sus modifier la page de navigation. Elle-même pouvant provoquer la mise à jour de la page de navigation de niveau supérieur si jamais elle constituait, la première référence de cette page, et ainsi de suite !

En outre, et comme nous le verrons par la suite, si la référence de navigation doit changer et que le type de donnée est de taille variable, il est alors possible de se trouver dans une situation où la référence ne peut être insérée dans la page et doit être placée dans une page annexe. Certes, pour ce dernier cas, on plonge dans la Loi de Murphy (3), mais en matière informatique nous savons qu'elle est omniprésente et la Loi des grands nombres augmente sa fréquence d'apparition en fonction du volume des données et de la fréquence des mises à jour !

Ainsi, au fil des suppressions, des trous apparaissent dans les pages de l'index. Mais à tout bien réfléchir, ce problème est le moins grave : l'index reste d'un volume comparable à sa structure originelle et la lecture des trous n'est pas vraiment pénalisante. Reste que bien reconstruit, l'index sera plus rapide à scruter.

Venons-en à la problématique de la modification. Là le bât peut blesser durement…

Si la modification d'une ligne d'index porte sur des données de taille fixe, alors le seul inconvénient est le repositionnement de la ligne si les données modifiées portent sur la clef de l'index. On se trouve donc dans le même cas que l'insertion.

Mais si la clef ou les éventuelles données complémentaires à modifier sont de taille variable et que cette taille est plus grande que la version précédente, alors cela entraîne le déplacement de cette ligne. En effet, si une ligne d'index ou une ligne de table doit passer d'une taille donnée à une taille plus grande, il n'est plus possible de la stocker à son emplacement originel, car les lignes sont mises bout à bout dans une page. Comme d'un côté il serait trop coûteux de remanier les pages chaque fois qu'un tel UPDATE survient et que l'autre solution conduirait à de nombreuses divisions de pages résultant de l'application d'un split (4) et que cela conduirait à clairsemer drastiquement l'index, SQL Server a choisi une troisième voie : déporter ces lignes dans de nouvelles pages en fin d'index, tout en indiquant l'indirection dans l'emplacement originel.

Autrement dit, dans un tel cas, SQL Server se sert de l'ancienne entrée d'index pour indiquer que la ligne a été déplacée et reporte les nouvelles valeurs de la ligne dans un espace libre de l'index, c'est-à-dire, généralement une page supplémentaire.

La problématique qui en résulte est une lecture en zigzag. Ainsi, dès que plusieurs lignes de la table doivent être lues, chaque ligne déplacée oblige à passer d'une page à l'autre et y revenir pour lire la ligne suivante. À terme, il n'est pas rare de devoir lire huit à dix fois plus de pages que nécessaire !

En définitive, la question qui se pose est la suivante : peut-on éviter que les index se fragmentent au cours de la vie des données ?

La réponse est oui, mais un oui imparfait, un oui de compromis. La solution consiste à créer des index dont les pages ne sont pas pleines, c'est-à-dire que l'on réserve quelques octets pour y stocker les lignes nouvelles et celles dont la valeur change. Cela se fait à la création de l'index en spécifiant le facteur de remplissage (5) des pages de l'index.

Avec un facteur de remplissage de 80 %, on permet à l'index de ne pas se détériorer si le volume des nouvelles données et celles à déplacer ne dépasse pas 20 % du volume actuel. En d'autres termes, cela signifie que votre index sera plus gros de 25 % donc que les temps de lecture seront allongés du quart par rapport à un index optimal… Ce n'est franchement pas énorme comparé aux chiffres que l'on peut obtenir si l'index est très fragmenté !

Cependant, méfiez-vous des chiffres : chaque page de l'index va bénéficier de 20 % de vide, mais vos nouvelles données peuvent avoir une distribution très hétérogène. Cela ne veut donc pas dire qu'avec un fill factor de 80 % une mise à jour de 20 % des données ne provoquera aucune fragmentation de l'index. Je dirais même qu'il n'y a statistiquement aucune chance pour que cela se produise. Et tant qu'à faire de rester dans les statistiques vous constaterez que lorsque votre base est petite et toute récente, alors un facteur de remplissage de 80 % peut être très insuffisant pour assurer un bon service dans une période donnée, alors qu'avec ce même fill factor et dans la même période ce peut être très largement suffisant avec quelques années d'exploitation, car le volume des données maniées est généralement stable alors qu'un pourcentage accordé à une grande table conduit forcément à réserver de grands espaces.

En définitive, s'il fallait être très rigoureux, il faudrait un fill factor évolutif régulièrement réajusté en fonction du vieillissement de la base, du volume de données mises à jour quotidiennement. Autrement dit, réajuster le facteur de remplissage des index en fonction de chaque index, de chaque table et de l'accroissement du volume de données.

Bien entendu, seule une méthode de reconstruction d'index permet d'appliquer un nouveau fill factor. Ce paramètre peut même de façon optionnelle être appliqué aux pages de navigation.

Curieusement il n'y a pas que les index qui « vieillissent ». En effet, les données aussi sont victimes de l'âge… Comment une donnée insérée dans un système d'information peut-elle présenter ce genre de défaut ? C'est en fait tout simple. Lorsqu'une base commence à emmagasiner des données sur plusieurs années, il arrive que le format des données évolue. Pour autant il est rare de voir les ABD (6) se précipiter sur leurs données pour les rectifier. Prenons quelques exemples concrets. En 25 ans nous sommes passés du n° de téléphone avec trois lettres et quatre chiffres à des numéros à dix chiffres. Avez-vous pensé à rectifier régulièrement ces données-là ? N'y a-t-il pas dans votre base quelques vieux numéros de téléphone ? Quid des mails ? Sont-ils toujours actifs ? Les vendeurs de listes connaissent bien le problème, eux qui mettent à jour en permanence leurs bases en fonction des NPAI (7)… Des données trop vieilles, donc de formats anciens ou de valeurs obsolètes constituent en fait du bruit, c'est-à-dire une information qui est brassée pour rien. C'est une perte de temps, des performances utilisées en vain.

Dans le même ordre d'esprit, il y a les données de mauvaise qualité. Elles peuvent conduire à des problématiques encore pires. Imaginez le cas suivant : l'utilisateur saisit le nom d'un client avec un espace avant le début du nom. Une commande est passée pour cet utilisateur. Quelque temps plus tard, ce client entend passer une nouvelle commande. Or, il ne sera pas retrouvé par une recherche alphabétique. On en viendra donc à le recréer, cette fois-ci correctement, et l'on se trouve dorénavant avec un doublon… De la même façon, la saisie de données non formatées pose de multiples problèmes de recherche. Par exemple je suis toujours sidéré de voir que les numéros de téléphone des clients sont tantôt émaillés de tirets, tantôt ornés de points. Parfois les chiffres sont tous colmatés les uns aux autres et parfois espacés par des blancs… Bref, comment avec autant de formats incompatibles faire des requêtes efficaces ?

Le pire arrive lorsque les développeurs ont laissé leurs jeux d'essais dans la base… C'est ainsi que l'on fausse les statistiques !

Pas étonnant alors que les spécialistes de l'élaboration de solutions décisionnelles prétendent que 50 % du coût d'une solution d'analyse multidimensionnelle consiste à nettoyer les données !

Il y aurait bien d'autres choses à dire sur l'exploitation des données dans le cadre d'une optimisation : comment faire évoluer les espaces de stockages, notamment pour maintenir un temps d'accès disque constant, comment faire évoluer les processeurs en fonction de l'accroissement des transactions, comment mieux gérer la RAM en fonction du volume des données à traiter… Mais cette série d'articles n'avait pour but que de vous sensibiliser à la chose.

Reste encore à lire, relire, potasser, expérimenter, car comme je le disais en préambule, l'optimisation est un artisanat pour ne pas parler d'art !