Outils pour l'optimisation

Le plan d’exécution d’une requête

Les requêtes, procédures et déclencheurs sont analysés par SQL Server et l’optimiseur de requête va stocker le plan d’exécution dans la mémoire de SQL Server, plus exactement dans la zone mémoire intitulée mémoire cache du plan. Il est possible d’analyser cette version compilée de la requête pour mieux comprendre les choix faits par l’optimiseur de requête et réagir pour permettre une exécution plus rapide de la requête. Ce qui peut se traduire par une nouvelle écriture de la requête, l’ajout d’index, la mise à jour des statistiques...

L’optimisation des requêtes n’est pas le seul point à prendre en compte pour résoudre les problèmes de performances, mais ce n’est pas non plus un élément à négliger. En effet, se focaliser sur des problèmes mémoire lorsque la requête est mal écrite peut masquer momentanément les mauvais temps de réponse, mais le problème se produira de nouveau lorsque le volume de données augmentera.

Pour visualiser le plan d’exécution sous SQL Server...

Le générateur de profils

Pour capturer l’activité du serveur et ainsi être capable d’analyser la charge de travail soumise à un serveur SQL, deux outils sont proposés : le générateur de profils (ancien outil marqué déprécié par Microsoft, mais très ergonomique et intégré à d’autres outils SQL Server) et les événements étendus qui permettent de lancer des sessions en arrière-plan.

Le générateur de profils est aux requêtes ce qu’un outil comme WireShark est au réseau : au lieu de capturer des trames réseau, il capture des requêtes SQL. Ces captures peuvent être paramétrées pour indiquer les types de requêtes à enregistrer ainsi que les informations nécessaires (comme les filtres de WireShark). Ceci peut être réalisé à partir de modèles prédéfinis.

Le générateur de profils peut être lancé à partir de Management Studio dans le menu Outils. Il est nécessaire ensuite de se connecter à une instance de SQL Server (comme pour Management Studio). Les paramètres d’une nouvelle trace sont ensuite demandés. Le modèle Standard (par défaut) est adapté dans la plupart des cas :

Dès que l’on...

Événements étendus

1. La création de session

Les événements étendus sont amenés à remplacer le générateur de profils, ainsi les nouveautés n’apparaîtront pas dans ce dernier. Un avantage des événements étendus est de pouvoir lancer une session d’analyse en arrière-plan (pas besoin de session interactive comme pour le générateur de profils).

Pour capturer l’activité, il est tout d’abord nécessaire de définir une nouvelle session depuis le nœud Gestion - Événements étendus - Sessions.

La création d’une nouvelle session peut être réalisée à partir de l’assistant ou bien en paramétrant directement la session. L’assistant permet de construire la session à partir d’un modèle dont certains sont équivalents à ceux du générateur de profils, alors que le passage par la boîte de dialogue permet de réaliser sa session en définissant les différentes options dans l’ordre que l’on souhaite. Les modifications de session passent toujours par la boîte de dialogue des propriétés quel que soit le mode de création choisi.

Depuis la page Événements, il est possible de sélectionner les événements....

L’analyseur de performances (moniteur système)

Il s’agit de l’analyseur de performances de Windows, auquel de nombreux compteurs ont été ajoutés lors de l’installation de SQL Server.

Les principaux objets spécifiques à SQL Server sont :

L’optimisation de la mémoire

Par défaut, SQL Server gère automatiquement et dynamiquement la quantité de mémoire qui lui est nécessaire. Cette option doit être conservée dans la majorité des cas. Il est pourtant possible de figer les quantités de mémoire minimum, maximum et la taille du jeu de travail.

L’Analyseur de performances va permettre de surveiller l’utilisation de la mémoire afin de s’assurer que le serveur dispose de suffisamment de mémoire. Le compteur utilisé est Gestionnaire de tampons : Durée non référencée par page d’extension (en anglais Buffer Manager : Page life expectancy). Ce compteur indique la durée moyenne de présence dans le cache des pages de données. Ainsi, plus ce compteur est élevé, plus le pourcentage de lecture de page de données en mémoire (à la place du fichier sur disque) est élevé, donc meilleures sont les performances. Une valeur de quelques minutes sera donc trop faible car à peine les pages seront arrivées dans le cache qu’elles seront remplacées par des nouvelles pages en mémoire. Elles devront donc être lues à partir du disque dur.

Attention à l’interprétation de ce compteur : une lecture en début de journée peut...

L’assistant Paramétrage du moteur de base de données

L’assistant Paramétrage du moteur de base de données a pour objectif de proposer la création d’index et de statistiques en confrontant l’organisation actuelle avec une charge de travail.

La charge de travail correspond soit à une trace capturée au préalable par le générateur de profil de SQL Server (fichier .trc), soit à un script Transact SQL.

À partir de cette charge de travail, l’outil va éventuellement proposer une réorganisation du schéma logique en ajoutant des index supplémentaires, en partitionnant certaines tables ou bien encore en proposant la création de vues indexées. Les propositions faites par l’assistant ont pour objectif de réduire le coût estimé par l’optimiseur de requête pour la charge de travail analysée.

Lors de l’analyse d’une charge de travail, il est nécessaire de paramétrer trois éléments :