Règles de base

  7 minutes à lire  

Optimisation du système

Un SGBDR comme SQL Server, ça se soigne. Vous l’installez confortablement sur une machine adaptée, vous le laissez s’épanouir, s’étendre à son aise dans un mobilier choisi, vous le nourrissez de modèles de données bien structurés, vitaminés, surtout pas trop gras, vous l’ajustez pour qu’il donne le meilleur de lui-même, enfin, vous surveillez ses signes vitaux de façon pro-active, pour lui garantir une vie longue et efficace. Ce sont tous ces éléments que nous allons détailler ici.

Les règles de base de l’optimisation

Que veut dire optimiser ? Ce court chapitre présente les bonnes pratiques et la démarche logique qui encadrent l’optimisation d’un système informatique, il vous invite à inclure la démarche d’optimisation dans l’approche globale de votre projet de base de données, d’en faire une composante justement dosée et toujours en évolution.

Les étapes de l’optimisation ?

Que pouvons-nous, et que faut-il optimiser ? Ce qui vient le plus souvent à l’esprit, est ce qu’on appelle en termes génériques la configuration.

Configuration matérielle, c’est-à-dire les éléments de hardware constituant un serveur, comme la configuration logicielle, c’est-à-dire les paramètres du système d’exploitation et du SGBDR¹ lui-même.

Bien qu’ils soient importants, ces éléments ne constituent que des étapes pour assurer un fonctionnement optimal. D’autres aspects, souvent négligés, sont cruciaux, et nous nous efforcerons de vous les présenter en détail pour vous permettre de tirer meilleur parti de vos bases de données.

Il ne s’agit pas seulement de penser performance à l’installation du serveur, mais dès la phase de conception de la structure des données, jusqu’à l’écriture du code.

La réflexion sur les performances doit donc prendre place dès les premiers moments du projet, et durant toute son évolution : modélisation, choix du matériel, installation, organisation du stockage physique et logique (une bonne indexation des tables est naturellement déterminante), codage SQL, supervision du serveur…

Dans les faits, malheureusement, cette réflexion est trop souvent négligée, et le besoin d’optimisation émerge en bout de chaîne, lorsque le système est en place et que les temps de réponse, soudainement ou progressivement, ne donnent plus satisfaction. Parfois, l’augmentation du volume de données ou du nombre d’utilisateurs simultanés a ralenti le temps d’exécution des requêtes de façon inacceptable. On se retrouve alors en face d’un constat, et dans l’obligation urgente de remédier au problème.

Cette situation n’est pas favorable pour effectuer un travail en profondeur, et dans le bon sens, mais c’est malheureusement la situation la plus fréquente, lorsqu’une planification prenant en compte les performances n’a pas été menée depuis le début du projet.

Nous présenterons dans cet ouvrage tous les outils qui vous permettront d’identifier la source des ralentissements et d’y remédier avec la précision d’un chirurgien. Pour autant, l’urgence ne doit pas vous pousser à répondre trop rapidement à la pression, et à choisir les solutions trop évidentes, celles qui sont maintenant presque des lieux communs de l’informatique, avec en tête, la mise à jour du matériel.

Augmenter la puissance du matériel est trop souvent une première réponse facile, un pis-aller basé sur un bon sens erroné. Combien de programmeurs en C faut-il pour changer une ampoule ? Un seul suffit, bien entendu. Il ne sert à rien de changer une ampoule à plusieurs, même si la blague en rajoute cinq de plus, qui seront nécessaires, six mois plus tard pour comprendre l’algorithme. De même pour SQL Server.

Les gains de performance ne seront pas automatiquement obtenus par un matériel plus puissant, car bien souvent, le problème provient d’une mauvaise architecture de la base de données, un manque d’index, des requêtes peu optimales… toutes sources de problèmes que l’augmentation de la puissance du matériel ne pourra pallier que très faiblement.

On ne peut donc optimiser au hasard. Pour améliorer les performances, nous ne pouvons faire l’économie de la recherche des causes. Cette recherche demande des outils appropriés – qui sont fort heureusement livrés avec SQL Server – et un peu d’obstination.

Souvent, donc, l’optimisation est urgente. Une base de données servant par exemple un site web marchand, ne peut se permettre un ralentissement prolongé. Lorsque les performances chutent, les sirènes sont déclenchées et les équipes entrent en mode d’urgence. Souvent, la première solution qui vient à l’esprit est de relancer la machine, ce qui, empiriquement, améliore souvent la situation, notamment lorsque celle-ci provient d’un problème de blocage de verrous. Si cela peut, ou pas, régler rapidement le problème, il est certain que cela ne donne aucune information concrète sur la source du ralentissement. Vouloir trop rapidement solutionner nuit aux performances sur le long terme, car cela empêche de mener l’enquête nécessaire à l’identification des causes, donc à la mise en place d’une solution adaptée et pérenne.

Souvent, les problèmes non résolus à la racine s’accumulent pour provoquer une sorte de situation d’urgence permanente où toute l’attention des techniciens est portée à la résolution en temps réel des problèmes, comme des médecins urgentistes. En optimisation, il faut savoir différer l’administration du médicament, pour s’assurer de bien comprendre la cause. Il est indispensable de prendre du recul, et ne pas travailler dans la précipitation. Cette recherche de la cause se fait nécessairement à chaud, lorsque le système souffre de lenteur, tout comme un médecin ne peut identifier une maladie qu’en auscultant le patient lorsqu’il est souffrant, et pas après sa guérison. Elle peut prendre un peu de temps, mais cette période douloureuse permettra d’en éviter bien d’autres plus tard.

Faut-il tout optimiser ?

Faut-il privilégier exclusivement les performances sur tout autre critère ? Évidemment non. Il est important de maintenir un équilibre entre la simplicité, la lisibilité et la performance. L’optimiseur de requête de SQL Server est un des meilleurs du marché, et fait en général un excellent travail. Il est souvent inutile de sur-optimiser vos requêtes, spécialement quand, d’une syntaxe à l’autre, le plan de requête généré par l’optimiseur est identique. De même, l’optimisation ne doit pas devenir une obsession, car dans ce cas, le temps qui lui est dédié peut devenir exagéré. Ici comme ailleurs, la loi de Pareto s’applique : 20 % des efforts d’optimisation bien choisis permettent 80 % des gains de performance. Les 80 % restants peuvent coûter beaucoup d’effort sans générer des résultats proportionnels.

Prenons un exemple. La collation détermine pour un serveur ou une base de donnée, l’ordre de classement des colonnes de type chaîne de caractère. Le choix d’une collation a un impact sur les performances de requêtes lorsqu’elles effectuent des comparaisons ou des recherches de chaînes. Une collation sensible à la casse, ou plus forte encore – de type binaire par exemple – améliore la rapidité de ces requêtes, car la comparaison de chaîne peut s’effectuer directement sur les octets sans avoir besoin d’utiliser une table d’équivalences. C’est donc un chemin d’optimisation possible, mais le jeu en vaut-il la chandelle, en sachant les contraintes fonctionnelles que cela implique ? Dans une base de données en collation binaire, non seulement toutes les requêtes devront mentionner le nom de tous les objets (tables, colonnes, …) dans leur casse exacte, ce qui n’est pas fondamentalement mauvais, cela oblige le développeur à une certaine discipline, mais de plus, toutes les comparaisons de chaînes devront se faire strictement, comme dans Oracle. Nous n’avons pas cette habitude en SQL Server. Est-il nécessaire de l’adopter pour un gain peut-être minime en performances ?

Maintenez une baseline

Afin de juger correctement des performances de votre système, vous devez bien le connaître. Lorsqu’un utilisateur final de votre base de données vous signale des problèmes de performance, comment pouvez-vous savoir si les lenteurs reportées sont exceptionnelles, ou « normales » ? Vous n’avez qu’un moyen : bâtir, à l’avance, ce qu’on appelle une baseline, et la maintenir au fil du temps.

Une baseline est simplement une collecte sur une période caractéristique, des compteurs importants qui vous permettent de connaître le comportement de votre système en temps normal. En journalisant régulièrement ces indicateurs, vous aurez une idée claire de la constitution physique de vos serveurs, comme vous pouvez dire pour vous-même si vous êtes fatigué ou de bonne humeur, parce que vous avez appris à vous connaître au fil du temps. Il s’agit aussi bien de recueillir les données de la charge matérielle (CPU, mémoire, réseau, disques), de l’utilisation du système d’exploitation (processus consommateurs de ressources, temps privilégié et temps utilisateur...) que de SQL Server lui-même (nombre de transactions et de requêtes par seconde, comportement des caches, …