197
6.5 Database Engine Tuning Advisor
premier travail d’optimisation, par exemple à la mise en place d’une nouvelle base
en production, l’outil n’est pas miraculeux, et ne peut remplacer le travail d’optimisation manuel, qui, par l’alliance des outils de tuning de SQL Server, de vos connaissances et de votre réflexion, reste un travail continu et essentiellement artisanal, ce
qui en fait l’intérêt.
Pour s’assurer d’obtenir des conseils de bonne qualité de la part du DTA, l’élément essentiel est de disposer d’un bon workload. Le DTA analyse un jeu de requêtes
SQL s’appliquant à une ou plusieurs bases de données, dans lesquelles vous pouvez
éventuellement choisir des tables spécifiques. Ce workload doit être aussi représentatif que possible : toutes les requêtes importantes doivent y figurer. Si ce n’est pas le
cas, le DTA risque de fournir des recommandations incomplètes, voire nuisibles aux
performances des requêtes absentes. Il peut être de différents formats. DTA se nourrit soit de simples fichiers de batches de requêtes T-SQL, où vous pouvez séparer les
batches par l’instruction GO, soit de traces SQL sauvegardées dans un fichier ou dans
une table. Vous pouvez également insérer les commandes SQL à analyser dans un
fichier XML au format spécifique au DTA 1 , ce qui vous permet d’ajouter un poids
relatif à chaque requête pour favoriser les recommandations sur des requêtes plus
importantes que d’autres.
La trace SQL doit contenir les événements suivants :
• RPC:Completed
• SQL:BatchCompleted
• SP:StmtCompleted
et au moins les colonnes EventClass et TextData. La colonne Duration est aussi
utilisée par le DTA, qui se concentre lorsqu’elle est présente sur les instructions les
plus longues à exécuter. Le modèle (template) du profiler nommé « tuning » est déjà
prêt pour vos sessions de trace adaptées. Évidemment, il est important de tracer
l’activité de la base à un moment de forte utilisation, lorsqu’elle travaille vraiment,
sur un serveur de production, afin d’obtenir le workload le plus proche possible du
réel. Vous pouvez configurer votre trace pour la rotation de fichiers (rollover), le
DTA reconnaît les fichiers numérotés. Si vous sauvegardez votre trace dans une
table, celle-ci doit être locale au serveur sur lequel vous allez lancer le DTA, et la
trace doit être arrêtée.
Si vous utilisez un fichier de requêtes, inscrivez-y simplement une seule fois chaque requête, en essayant de donner aux critères de filtre dans la clause WHERE de
valeurs moyennes et représentatives : nous avons vu que, grâce aux statistiques,
l’optimiseur est sensible à la sélectivité de ces valeurs. Les requêtes d’insertion, de
mise à jour et de suppression sont aussi importantes. Non content d’être elles aussi
optimisables, elles donnent l’occasion au DTA de tester le coût de maintenance des
index qu’il va suggérer.
1. Ce document XML est décrit par le schéma DTASchema.xsd disponible à l’adresse http://schemas.microsoft.com/sqlserver/. Ce schéma décrit toutes les parties que nous aborderons dans ce
chapitre, notamment la définition de la session.
6.5 Database Engine Tuning Advisor
premier travail d’optimisation, par exemple à la mise en place d’une nouvelle base
en production, l’outil n’est pas miraculeux, et ne peut remplacer le travail d’optimisation manuel, qui, par l’alliance des outils de tuning de SQL Server, de vos connaissances et de votre réflexion, reste un travail continu et essentiellement artisanal, ce
qui en fait l’intérêt.
Pour s’assurer d’obtenir des conseils de bonne qualité de la part du DTA, l’élément essentiel est de disposer d’un bon workload. Le DTA analyse un jeu de requêtes
SQL s’appliquant à une ou plusieurs bases de données, dans lesquelles vous pouvez
éventuellement choisir des tables spécifiques. Ce workload doit être aussi représentatif que possible : toutes les requêtes importantes doivent y figurer. Si ce n’est pas le
cas, le DTA risque de fournir des recommandations incomplètes, voire nuisibles aux
performances des requêtes absentes. Il peut être de différents formats. DTA se nourrit soit de simples fichiers de batches de requêtes T-SQL, où vous pouvez séparer les
batches par l’instruction GO, soit de traces SQL sauvegardées dans un fichier ou dans
une table. Vous pouvez également insérer les commandes SQL à analyser dans un
fichier XML au format spécifique au DTA 1 , ce qui vous permet d’ajouter un poids
relatif à chaque requête pour favoriser les recommandations sur des requêtes plus
importantes que d’autres.
La trace SQL doit contenir les événements suivants :
• RPC:Completed
• SQL:BatchCompleted
• SP:StmtCompleted
et au moins les colonnes EventClass et TextData. La colonne Duration est aussi
utilisée par le DTA, qui se concentre lorsqu’elle est présente sur les instructions les
plus longues à exécuter. Le modèle (template) du profiler nommé « tuning » est déjà
prêt pour vos sessions de trace adaptées. Évidemment, il est important de tracer
l’activité de la base à un moment de forte utilisation, lorsqu’elle travaille vraiment,
sur un serveur de production, afin d’obtenir le workload le plus proche possible du
réel. Vous pouvez configurer votre trace pour la rotation de fichiers (rollover), le
DTA reconnaît les fichiers numérotés. Si vous sauvegardez votre trace dans une
table, celle-ci doit être locale au serveur sur lequel vous allez lancer le DTA, et la
trace doit être arrêtée.
Si vous utilisez un fichier de requêtes, inscrivez-y simplement une seule fois chaque requête, en essayant de donner aux critères de filtre dans la clause WHERE de
valeurs moyennes et représentatives : nous avons vu que, grâce aux statistiques,
l’optimiseur est sensible à la sélectivité de ces valeurs. Les requêtes d’insertion, de
mise à jour et de suppression sont aussi importantes. Non content d’être elles aussi
optimisables, elles donnent l’occasion au DTA de tester le coût de maintenance des
index qu’il va suggérer.
1. Ce document XML est décrit par le schéma DTASchema.xsd disponible à l’adresse http://schemas.microsoft.com/sqlserver/. Ce schéma décrit toutes les parties que nous aborderons dans ce
chapitre, notamment la définition de la session.