SQL Server

Optimisation des performances des requêtes SQL Server

Maciej Pilecki

Vue d'ensemble:

• Analyse des plans d'exécution
• Optimisation des requêtes
• Identification des requêtes à régler

Lors de l'optimisation de votre serveur de base de données, vous devez régler les performances des requêtes individuelles. C'est aussi important, voire plus important, que le réglage d'autres aspects de votre installation de serveur affectant les performances, tels que le matériel et les configurations logicielles.

Même si votre serveur de base de données s'exécute sur le matériel le plus puissant disponible, ses performances peuvent être affectées par une poignée de requêtes au comportement indésirable. En fait, une seule mauvaise requête, parfois appelée « requête rebelle », peut provoquer des problèmes de performances sérieux pour votre base de données.

À l'inverse, les performances globales de votre base de données peuvent être fortement améliorées en réglant une série de requêtes plus coûteuses ou très fréquemment exécutées. Dans cet article, j'examinerai quelques unes des techniques auxquelles vous pouvez recourir pour identifier et régler les requêtes les plus coûteuses et les moins performantes sur votre serveur.

Analyse des plans d'exécution

Lors du réglage d'une requête individuelle, vous commencez habituellement par examiner le plan d'exécution de cette requête. Le plan d'exécution décrit la séquence des opérations, physiques et logiques, que SQL ServerTM exécutera afin de satisfaire la requête et de produire le jeu de résultats souhaité. Le plan d'exécution est produit par un composant du moteur de base de données appelé optimiseur de requête pendant la phase d'optimisation du traitement de requête. Celui-ci tient compte d'un grand nombre de facteurs différents, tels que les prédicats de recherche utilisés dans la requête, les tables impliquées et leurs conditions de jointure, la liste des colonnes renvoyées et la présence d'index utiles qui peuvent servir de chemins d'accès efficaces aux données.

Pour les requêtes complexes, le nombre de permutations possibles peut être énorme, c'est pourquoi l'optimiseur de requête n'évalue pas toutes les possibilités mais essaie plutôt de rechercher un plan « suffisamment bon » pour une requête donnée. En effet, il peut ne pas toujours être possible de trouver un plan parfait et, même lorsque c'est possible, le coût de l'évaluation de toutes les possibilités pour la recherche du plan parfait peut facilement dépasser les gains de performances. Du point de vue de l'administrateur de base de données, il est important de comprendre le processus et ses restrictions.

Il existe plusieurs façons de récupérer un plan d'exécution pour une requête :

• Management Studio fournit les fonctionnalités d'affichage du plan d'exécution réel et du plan d'exécution estimé, qui présentent le plan sous forme graphique. Ces fonctionnalités offrent la solution la plus adaptée pour un examen direct et représentent de loin l'approche la plus souvent utilisée pour l'affichage et l'analyse des plans d'exécution. (Dans cet article, j'utiliserai des plans graphiques produits de cette façon pour illustrer mes exemples)
• Les diverses options de SET, telles que SHOWPLAN_XML et SHOWPLAN_ALL, renvoient le plan d'exécution sous forme de document XML décrivant le plan avec un schéma spécial, ou de jeu de lignes avec description textuelle de chacune des opérations du plan d'exécution.
• Les classes d'événement du Générateur de profils SQL Server, telles que Showplan XML, vous permettent de rassembler les plans d'exécution d'instructions recueillies par un suivi.

Bien qu'une représentation XML du plan d'exécution ne soit peut-être pas le format le plus facile à lire pour un humain, cette option vous permet d'écrire des procédures et des utilitaires capables d'analyser vos plans d'exécution en recherchant les signes de problèmes de performance et de plans non optimaux. Une représentation XML peut également être enregistrée dans un fichier avec une extension .sqlplan et être ensuite ouverte dans Management Studio pour produire une représentation graphique. Ces fichiers peuvent également être enregistrés pour analyse ultérieure, ce qui élimine bien sûr le besoin de reproduire le plan d'exécution chaque fois que vous voulez l'analyser. Cette fonction est particulièrement utile lorsque vous voulez comparer vos plans pour examiner leur évolution au fil du temps.

Coût d'exécution estimé

La première chose à comprendre concernant les plans d'exécution est la façon dont ils sont générés. SQL Server utilise un optimiseur de requête basé sur le coût, c'est-à-dire qu'il essaie de produire un plan d'exécution avec le coût estimé le plus bas. L'estimation est basée sur les statistiques de distribution des données accessibles à l'optimiseur lorsqu'il évalue chaque table impliquée dans la requête. Si ces statistiques sont absentes ou périmées, l'optimiseur de requête ne disposera pas des informations vitales dont il a besoin pour le processus d'optimisation de requête et ses estimations seront probablement erronées. Dans de tels cas, l'optimiseur choisira un plan moins optimal en surestimant ou en sous-estimant les coûts d'exécution des différents plans.

Le coût d'exécution estimé fait l'objet de nombreuses suppositions inexactes. En particulier, il est souvent supposé que le coût d'exécution estimé est un bon indicateur de la durée nécessaire à l'exécution d'une requête et que cette estimation permet de distinguer les bons plans des mauvais plans. Ce n'est pas vrai. Premièrement, nous disposons de nombreuses informations sur les unités qui expriment le coût estimé et sur une éventuelle relation directe avec la durée d'exécution. Deuxièmement, puisqu'il s'agit d'estimations qui peuvent être incorrectes, les plans avec des coûts estimés élevés peuvent parfois être beaucoup plus efficaces en termes de processeur, d'E/S et de temps d'exécution, malgré leur estimation élevée. C'est souvent le cas avec les requêtes qui impliquent des variables de table. Puisque aucune statistique n'est disponible pour les variables de table, l'optimiseur de requête suppose toujours qu'une variable de table contient une seule ligne, même si elle en contient beaucoup plus. Par conséquent, l'optimiseur de requête choisira un plan basé sur une estimation inexacte. Donc, lors de la comparaison des plans d'exécution de vos requêtes, vous ne devez pas uniquement vous fier au coût de requête estimé. Incluez plutôt le résultat des options STATISTICS I/O et STATISTICS TIME dans l'analyse pour comprendre à quoi correspond réellement le coût d'exécution en termes d'E/S et de temps de processeur.

Un type spécial de plan d'exécution, portant le nom de plan parallèle, mérite d'être mentionné ici. Si vous exécutez votre requête sur un serveur multiprocesseur et que votre requête peut faire l'objet d'un travail en parallèle, un plan parallèle peut être choisi. (Généralement, l'optimiseur de requête prendra uniquement en compte un plan parallèle pour une requête dont le coût dépasse un certain seuil configurable) En raison des surcharges liées à la gestion des threads d'exécution parallèles multiples (ce qui signifie distribuer le travail entre des threads, exécuter la synchronisation et rassembler les résultats), les projets parallèles coûtent davantage à l'exécution et ceci se reflète dans leur coût estimé. Pourquoi sont-ils alors privilégiés par rapport aux plans non parallèles et moins coûteux ? Grâce à l'utilisation de la puissance de traitement de processeurs multiples, les plans parallèles ont tendance à produire des résultats plus rapidement que les plans standard. En fonction de votre scénario spécifique, notamment des variables telles que les ressources disponibles et la charge simultanée des autres requêtes, cette situation peut être souhaitable pour votre installation. Le cas échéant, vous devez identifier les requêtes pouvant produire des plans parallèles et le nombre de processeurs pouvant être utilisés par chacune. Pour ce faire, vous devez définir l'option Degré maximum de parallélisme au niveau du serveur et la remplacer au niveau des requêtes individuelles par OPTION (MAXDOP n) selon les besoins.

Analyse d'un plan d'exécution

Je vais maintenant examiner une requête simple, son plan d'exécution et certaines méthodes d'amélioration de ses performances. Disons que j'exécute cette requête à l'aide de Management Studio avec l'option Inclure le plan d'exécution réel activée dans l'exemple de base de données Adventure Works sur SQL Server 2005 :

SELECT c.CustomerID, SUM(LineTotal)
FROM Sales.SalesOrderDetail od 
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
JOIN Sales.Customer c ON oh.CustomerID=c.CustomerID
GROUP BY c.CustomerID

Le résultat est le plan d'exécution illustré dans la figure 1. Cette requête simple calcule la quantité totale de commandes passées par chaque client d'Adventure Works. En examinant le plan d'exécution, vous pouvez voir comment le moteur de base de données traite la requête et produit le résultat. Les plans d'exécution graphiques doivent être lus du haut vers le bas et de la droite vers la gauche. Chaque icône représente une opération logique et physique exécutée et les flèches indiquent le flux de données entre les opérations. L'épaisseur des flèches représente le nombre de lignes transmises entre les opérations. Plus la flèche est épaisse et plus le nombre de lignes impliquées est important. Si vous placez votre pointeur sur une des icônes d'opérateur, une info-bulle jaune (telle que celle illustrée à la figure 2) indique les détails de cette opération particulière.

Figure 1** Exemple de plan d'exécution **(Cliquer sur l'image pour l'agrandir)

Figure 2** Détails d'une opération **(Cliquer sur l'image pour l'agrandir)

En examinant chacun des opérateurs, vous pouvez analyser la séquence des opérations exécutées :

1. le moteur de base de données exécute une opération Clustered Index Scan sur la table Sales.Customer et renvoie la colonne CustomerID pour toutes les lignes de cette table.
2. Il exécute ensuite une opération Index Scan (ce qui correspond à une analyse de table non cluster) sur l'un des index de la table Sales.SalesOrderHeader. Il s'agit d'un index sur la colonne CustomerID, qui inclut également implicitement la colonne SalesOrderID (la clé de table non cluster). Les valeurs de ces deux colonnes sont renvoyées par l'analyse.
3. Le résultat des deux analyses est réuni sur la colonne CustomerID à l'aide de l'opérateur physique Merge Join. (Il s'agit de l'une des trois méthodes physiques d'exécution d'une opération de jointure logique) Cette opération est rapide mais nécessite que les deux entrées soient triées dans une colonne jointe. En l'occurrence, les deux opérations d'analyse ont déjà renvoyé les lignes triées sur CustomerID et il n'est donc pas nécessaire d'effectuer une opération de tri supplémentaire).
4. Ensuite, le moteur de base de données exécute une analyse de l'index en cluster sur la table Sales.SalesOrderDetail, et récupére les valeurs des quatre colonnes (SalesOrderID, OrderQty, UnitPrice et UnitPriceDiscount) à partir de toutes les lignes de cette table. (Il y avait 123 317 lignes estimées comme devant être renvoyées par cette opération et ce nombre de lignes a effectivement été renvoyé, comme vous pouvez le constater dans les propriétés Estimated Number of Rows et Actual Number of Rows de la figure 2 ; l'estimation était donc très précise.)
5. Les lignes générées par l'analyse d'index en cluster sont transmises au premier opérateur Compute Scalar, si bien que la valeur de la colonne calculée LineTotal peut être calculée pour chaque ligne, sur la base des colonnes OrderQty, UnitPrice et UnitPriceDiscount impliquées dans la formule.
6. Le second opérateur Compute Scalar applique la fonction ISNULL au résultat du calcul précédent, comme requis par la formule de colonne calculée. Cette opération termine le calcul de la colonne LineTotal et renvoie le résultat, avec la colonne SalesOrderID, à l'opérateur suivant.
7. Le résultat de l'opérateur Merge Join de l'étape 3 est joint au résultat de l'opérateur Compute Scalar de l'étape 6, à l'aide de l'opérateur physique Hash Match.
8. Un autre opérateur Hash Match est ensuite appliqué pour regrouper les lignes renvoyées depuis Merge Join par la valeur de colonne CustomerID et l'agrégat SUM calculé de la colonne LineTotal.
9. Le dernier nœud, SELECT, n'est pas un opérateur physique ou logique mais plutôt un emplacement réservé qui représente les résultats et le coût globaux de la requête.

Sur mon portable, ce plan d'exécution présentait un coût estimé de 3,31365 (comme illustré à la figure 3). Lorsqu'elle a été exécutée avec STATISTICS I/O ON, la requête a fait état d'un total de 1 388 opérations de lecture logiques sur les trois tables impliquées. Les pourcentages affichés sous chacun des opérateurs représentent le coût de chaque opérateur individuel par rapport au coût global estimé du plan d'exécution dans son ensemble. En examinant le plan de la figure 1, vous pouvez voir que l'essentiel du coût total du plan d'exécution complet est associé aux trois opérateurs suivants : l'opérateur Clustered Index Scan de la table Sales.SalesOrderDetail et les deux opérateurs Hash Match. Mais avant d'essayer d'optimiser ceux-ci, j'aimerais attirer l'attention d'une modification très simple de ma requête qui me permettra d'éliminer complètement deux des opérateurs.

Figure 3** Coût total d'exécution estimé de la requête **

Puisque la seule colonne que je renvoie à partir de la table Sales.Customer table est CustomerID, et que cette colonne est également incluse en tant que clé étrangère dans la table Sales.SalesOrderHeaderTable, je peux éliminer complètement la table Customer de la requête sans modifier le sens logique ou le résultat produit par notre requête en utilisant ce code :

SELECT oh.CustomerID, SUM(LineTotal)
FROM Sales.SalesOrderDetail od JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
GROUP BY oh.CustomerID

Ceci se traduit par un plan d'exécution différent, illustré à la figure 4.

Figure 4** Plan d'exécution après élimination de la table Customer de la requête **(Cliquer sur l'image pour l'agrandir)

Deux opérations ont été entièrement éliminées : l'opération Clustered Index Scan sur la table Customer et l'opération Merge Join entre Customer et SalesOrderHeader. L'opération Hash Match a été remplacée par une opération Merge Join beaucoup plus efficace. Cependant, pour utiliser Merge Join entre les tables SalesOrderHeader et SalesOrderDetail, les lignes des deux tables ont dû être renvoyées triées selon la colonne de jointure SalesOrderID. Pour atteindre cet objectif, l'optimiseur de requête a décidé d'exécuter une opération Clustered Index Scan sur la table SalesOrderHeader, au lieu d'utiliser une opération Non-Clustered Index Scan qui aurait été plus économique en termes des E/S impliquées. Il s'agit d'une bonne démonstration pratique du fonctionnement de l'optimiseur de requête, puisque les économies résultant de la méthode physique d'exécution de l'opération de jointure ont été supérieures au coût d'E/S supplémentaires généré par l'opération Clustered Index Scan. L'optimiseur de requête a choisi la combinaison d'opérateurs résultante car elle offrait le coût total d'exécution estimé le plus bas. Sur mon portable, bien que le nombre de lectures logiques ait augmenté (jusqu'à 1 941), le temps de processeur consommé était en fait inférieur et le coût d'exécution estimé de cette requête avait diminué de près de 13 % (2,89548).

Imaginons que je souhaite encore améliorer les performances de cette requête. J'examine maintenant l'opération Clustered Index Scan sur la table SalesOrderHeader, qui est devenue l'opérateur le plus cher de ce plan d'exécution. Dans la mesure où je n'ai besoin que de deux colonnes de cette table pour remplir la requête, je peux créer un index sans cluster contenant uniquement ces deux colonnes, ce qui remplace l'analyse de la table entière par une analyse de l'index sans cluster largement plus petit. La définition d'index pourrait ressembler à ceci :

CREATE INDEX IDX_OrderDetail_OrderID_TotalLine
ON Sales.SalesOrderDetail (SalesOrderID) INCLUDE (LineTotal)

Remarquez que l'index que j'ai créé inclut une colonne calculée. Ce n'est pas toujours possible, en fonction de la définition de la colonne calculée.

Après avoir créé cet index et exécuté la même requête, j'obtiens le nouveau plan d'exécution illustré à la figure 5.

Figure 5** Plan d'exécution optimisé **(Cliquer sur l'image pour l'agrandir)

L'opération Clustered Index Scan sur la table SalesOrderDetail a été remplacée par une opération non-Clustered Index Scan avec coût nettement inférieur en termes d'E/S. J'ai également éliminé l'un des opérateurs Compute Scalar, car mon index inclut déjà une valeur calculée de la colonne LineTotal. Le coût estimé du plan d'exécution est maintenant 2,28112 et la requête effectue 1 125 lectures logiques lorsqu'elle est exécutée.

Index de couverture

Exercice de requête de commandes client

Q Voici exercice de requête de commandes client : Essayez de déterminer la définition d'index, c'est-à-dire les colonnes qu'il doit contenir pour devenir index de couverture pour cette requête, et si l'ordre des colonnes dans la définition d'index entraînerait une différence au niveau des performances.

R Je vous ai mis au défi de trouver l'index de couverture le plus optimal à créer sur la table Sales.SalesOrderHeader pour la requête d'exemple de mon article. Pour y arriver, la première chose que vous devriez remarquer est que la requête utilise seulement deux colonnes de la table : CustomerID et SalesOrderID. Si vous avez lu mon article attentivement, vous aurez remarqué que dans le cas de la table SalesOrderHeader, il existe déjà un index qui couvre cette requête. Il s'agit de l'index sur CustomerID, qui contient implicitement la colonne SalesOrderID, clé de clustering de la table.

Bien sûr, j'ai également expliqué pourquoi l'optimiseur de requête a décidé de ne pas utiliser cet index. Oui, vous pourriez forcer l'optimiseur de requête à utiliser cet index, mais la solution serait moins efficace que le plan existant qui utilise les opérateurs Clustered Index Scan et Merge Join. En effet, vous forceriez l'optimiseur de requête à faire un choix entre l'exécution d'une opération Sort supplémentaire afin de pouvoir toujours utiliser Merge Join ou revenir à l'utilisation d'une opération Hash Join moins efficace. Les deux options ont un coût d'exécution estimé plus élevé que le plan existant (la version avec l'opération Sort offrirait des performances particulièrement médiocres), et l'optimiseur de requête ne les utilisera pas à moins qu'il soit forcé à le faire. Par conséquent, dans cette situation, le seul index qui offrira de meilleures performances que Clustered Index Scan est un index sans cluster sur SalesOrderID, CustomerID. Mais il est important de noter que les colonnes doivent être dans cet ordre particulier :

CREATE INDEX IDX_OrderHeader_SalesOrderID_CustomerID
ON Sales.SalesOrderHeader (SalesOrderID, CustomerID)

Si vous créez cet index, le plan d'exécution contiendra l'opérateur Index Scan au lieu de l'opérateur Clustered Index Scan. Il s'agit d'une différence significative. Dans ce cas, l'index sans cluster contenant seulement deux colonnes est beaucoup plus petit que la table entière sous forme d'index avec cluster. Par conséquent, elle nécessitera moins d'E/S pour lire les données nécessaires.

Cet exemple démontre également que l'ordre des colonnes de votre index peut avoir un impact significatif sur son utilité pour l'optimiseur de requête. Gardez ceci à l'esprit lorsque vous concevez des index multi-colonnes.

L'index que j'ai créé sur SalesOrderDetail est un exemple de ce que l'on appelle « index de couverture ». Il s'agit d'un index sans cluster qui contient toutes les colonnes nécessaires à l'exécution de la requête, éliminant ainsi la nécessité d'analyser la table entière avec les opérateurs Table Scan ou Clustered Index Scan. L'index est essentiellement une copie plus réduite de la table, contenant un sous-ensemble des colonnes de la table. Seules les colonnes nécessaires pour répondre à la requête (ou aux requêtes) sont incluses dans l'index. En d'autres termes, l'index contient juste ce dont il a besoin pour « couvrir » la requête.

La création d'index de couverture pour les requêtes les plus fréquemment exécutées est l'une des tâches les plus simples et les plus courantes utilisées dans le réglage de requêtes. Elle fonctionne particulièrement bien dans les situations où la table contient un nombre de colonnes réduit mais où peu d'entre elles sont souvent référencées par les requêtes. En créant un ou plusieurs index de couverture, vous pouvez améliorer fortement les performances des requêtes concernées, car elles accéderont à une quantité de données beaucoup plus réduite qui, à leur tour, provoqueront moins d'E/S. Il y a, cependant, un coût masqué lié à la maintenance des index supplémentaires pendant les opérations de modification de données (INSERT, UPDATE et DELETE). En fonction de votre environnement et du rapport entre les requêtes SELECT et les modifications de données, vous devez juger avec précaution si ces surcharges de maintenance d'index supplémentaires sont justifiées par les améliorations de performances de requêtes.

Ne craignez pas de créer des index multi-colonnes, par opposition à des index à colonne unique. Ceux-ci ont tendance à être beaucoup plus utiles que les index à colonne unique et l'optimiseur de requête est plus susceptible de les utiliser pour couvrir la requête. La plupart des index de couverture sont des index multi-colonnes.

Dans le cas de mon exemple de requête, celle-ci peut toujours être améliorée et cette requête pourrait être optimisée davantage en plaçant un index de couverture sur la table SalesOrderHeader. Ceci élimine l'opération Clustered Index Scan en la remplaçant par une opération Non-Clustered Index Scan Je vous laisse ceci comme exercice. Essayez de déterminer la définition d'index, c'est-à-dire les colonnes qu'il doit contenir pour devenir index de couverture pour cette requête et si l'ordre des colonnes dans la définition d'index provoquerait une différence pour les performances. Voir l'encadré "Exercice de requête de commandes client" pour la solution.

Vues indexées

Si les performances de ma requête d'exemple sont très importantes, je peux aller plus loin et créer une vue indexée qui enregistre physiquement les résultats matérialisés de la requête. Les vues indexées s'accompagnent de certaines conditions préalables et restrictions, mais s'il est possible d'en utiliser, celles-ci peuvent améliorer considérablement les performances. Gardez à l'esprit que les vues indexées sont synonymes de coût de maintenance plus élevé que les index standard. Par conséquent, soyez prudent lorsque vous les utilisez. Dans ce cas, la définition d'index pourrait ressembler à ceci :

CREATE VIEW vTotalCustomerOrders
WITH SCHEMABINDING
AS
SELECT oh.CustomerID, SUM(LineTotal) AS OrdersTotalAmt, COUNT_BIG(*) AS TotalOrderLines
FROM Sales.SalesOrderDetail od 
JOIN Sales.SalesOrderHeader oh
ON od.SalesOrderID=oh.SalesOrderID
GROUP BY oh.CustomerID 

Vous remarquerez l'option WITH SCHEMABINDING, qui est une condition préalable pour créer un index sur une telle vue et la fonction COUNT_BIG(*), qui est nécessaire si notre définition d'index contient une fonction d'agrégat (dans cet exemple, SUM). Après avoir créé cette vue, je peux créer un index sur celle-ci, comme ceci :

CREATE UNIQUE CLUSTERED INDEX CIX_vTotalCustomerOrders_CustomerID 
ON vTotalCustomerOrders(CustomerID)

Lorsque je crée cet index, le résultat de la requête incluse dans la définition de la vue est matérialisé et enregistré physiquement sur le disque dans l'index. Notez que toutes les opérations de modification de données sur les tables de base mettent ensuite automatiquement à jour les valeurs dans la vue conformément à sa définition.

Si je réexécute maintenant la requête, ce qui se produit dépend de l'édition de SQL Server que j'exécute. Dans les éditions Enterprise ou Developer, l'optimiseur de requête fait automatiquement correspondre cette requête à la définition de vue indexée et utilise la vue indexée au lieu d'interroger les tables de la base impliquées. La figure 6 illustre le plan d'exécution généré dans ce cas. Il consiste en une seule opération : un Clustered Index Scan de l'index que j'ai créé sur la vue. Le coût d'exécution estimé est seulement 0,09023 et il effectue seulement 92 lectures logiques.

Figure 6** Plan d'exécution avec utilisation de la vue indexée **(Cliquer sur l'image pour l'agrandir)

Vous pouvez toujours créer et utiliser cette vue indexée dans les autres éditions de SQL Server, mais pour arriver au même résultat, vous devez modifier la requête pour référencer la vue directement en utilisant l'indicateur NOEXPAND, comme ceci :

SELECT CustomerID, OrdersTotalAmt
FROM vTotalCustomerOrders WITH (NOEXPAND)

Comme vous pouvez le voir, les vues indexées peuvent constituer une fonctionnalité très puissante si elles sont convenablement utilisées. Elles sont particulièrement utiles pour optimiser les requêtes qui réalisent des agrégations sur de grandes quantités de données. Si elles sont utilisées dans Enterprise Edition, elles peuvent profiter à de nombreuses requêtes sans nécessiter de modifications de votre code.

Identification des requêtes à régler

Comment puis-je identifier les requêtes qui valent la peine d'être réglées ? Je recherche les requêtes qui sont fréquemment exécutées. Elles peuvent ne pas avoir un coût d'exécution unique élevé, mais le coût agrégé d'exécution peut être beaucoup plus élevé que celui d'une grande requête rarement exécutée. Je ne dis pas qu'il n'est pas nécessaire de régler les grandes requêtes, mais je pense juste que vous devriez d'abord vous concentrer sur les requêtes les plus fréquemment exécutées. Mais alors, comment les identifier ?

Malheureusement, la méthode la plus fiable est quelque peu compliquée et implique l'exécution d'un suivi de toutes les requêtes exécutées pour votre serveur et leur regroupement en fonction de leur signature. (C'est-à-dire, le texte de requête avec les valeurs de paramètre réelles remplacées par des espaces réservés afin d'identifier le même type de requête, même s'il a été exécuté avec des valeurs de paramètre différentes) Ce processus est compliqué, car les signatures de requête sont difficiles à générer. Itzik Ben-Gan décrit une solution utilisant des fonctions CLR définies par l'utilisateur et des expressions régulières dans son livre Inside Microsoft SQL Server 2005: T-SQL Querying.

Il existe une autre méthode beaucoup plus simple, bien qu'un peu moins fiable. Vous pouvez vous appuyer sur les statistiques conservées dans le cache de plan d'exécution pour toutes les requêtes, et les interroger en utilisant des vues de gestion dynamiques. La figure 7 contient un exemple de requête qui vous montre le texte et le plan d'exécution des 20 requêtes de votre cache avec le nombre cumulé le plus élevé de lectures logiques. Cette requête est très pratique pour identifier rapidement les requêtes qui génèrent le plus grand nombre de lectures logiques, mais elle s'accompagne de restrictions. En effet, elle affichera uniquement les requêtes dont le plan est mis en cache au moment où vous exécutez la requête. Si une opération n'est pas mise en cache, vous la manquerez.

Figure 7 Identification des 20 requêtes les plus coûteuses en termes d'E/S de lecture

SELECT TOP 20 SUBSTRING(qt.text, (qs.statement_start_offset/2)+1, 
        ((CASE qs.statement_end_offset
          WHEN -1 THEN DATALENGTH(qt.text)
         ELSE qs.statement_end_offset
         END - qs.statement_start_offset)/2)+1), 
qs.execution_count, 
qs.total_logical_reads, qs.last_logical_reads,
qs.min_logical_reads, qs.max_logical_reads,
qs.total_elapsed_time, qs.last_elapsed_time,
qs.min_elapsed_time, qs.max_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.encrypted=0
ORDER BY qs.total_logical_reads DESC

Après avoir identifié ces éléments peu performants, prenez le temps d'examiner leur plan de requête et recherchez des moyens d'améliorer leurs performances en utilisant certaines techniques d'indexation que j'ai décrites dans cet article. Si vous réussissez, ce sera du temps bien investi.

Bon réglage !

Maciej Pilecki est mentor associé chez Solid Quality Mentors, une entreprise internationale spécialisée dans la formation, l'encadrement et la consultation. Il est Microsoft Certified Trainer (MCT) et Most Valuable Professional (MVP) SQL Server, et participe fréquemment à des cours et des conférences sur de nombreux aspects de SQL Server et du développement d'applications.

© 2008 Microsoft Corporation et CMP Media, LLC. Tous droits réservés. Toute reproduction, totale ou partielle, est interdite sans autorisation préalable.