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 Management Studio, il existe deux options :

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. 

Aucun modèle n’existe par défaut pour SQL Server 2022, il faut donc choisir les évènements à enregistrer et éventuellement créer un modèle pour les prochaines utilisation. Les évènements intéressants et correspondant au modèle standard des anciennes versions sont RPC:Completed et SQL:BatchCompleted :

Dès que l’on clique sur le bouton Exécuter, l’enregistrement de l’activité commence :

Pour arrêter l’enregistrement de la trace, il faut aller dans le menu Fichier...

É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. Dans le cas où un modèle est utilisé, alors des événements sont prédéfinis pour être analysés.

Exemple

Les événements définis par défaut depuis le modèle Standard

Enfin, la page Stockage de données est importante car elle permet de définir l’emplacement où ces données de type événement vont être enregistrées.

Dans...

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.

Voici les principaux objets spécifiques à SQL Server :

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 :