196
Chapitre 6. Utilisation des index
Nous avons vu que les mises à jour de statistiques sont indispensables à la bonne
performance des requêtes, et que leur maintenance est nécessaire pour assurer une
bonne qualité à travers le temps. Mais, quand cette opération se déclenche-t-elle ?
Tout comme la création automatique, elle se produit au besoin, c’est-à-dire au
moment où une requête va s’exécuter et pour laquelle ces statistiques sont utiles,
aussi bien dans le cas d’une requête ad hoc (c’est-à-dire d’une requête écrite une fois
pour un besoin particulier), que d’une procédure stockée. Si le seuil de modification
de la table est atteint, il va d’abord lancer une mise à jour des statistiques nécessaires,
puis ensuite générer son plan d’exécution. Cela peut évidemment provoquer une
latence à l’exécution de la requête. Pour les plans d’exécution déjà dans le cache, la
phase d’exécution inclut une vérification des statistiques, un UPDATE éventuel de celles-ci, puis une recompilation, ce qui dans certains cas peut se révéler encore plus
lourd.
Cela pose rarement un problème, mais si vous êtes dans le cas où cela affecte vos
performances, vous avez quelques solutions à votre disposition :
• AUTO_UPDATE_STATISTICS_ASYNC (option de base de donnéees) vous permet de
réaliser une mise à jour des statistiques de façon asynchrone. Cela veut dire
que lorsque le moteur SQL décide de mettre à jour ses statistiques il n’attend
pas la fin de cette opération pour exécuter la requête déclenchante. Elle
démarre donc plus vite, mais ne s’exécute pas forcément plus rapidement, car
avec un plan d’exécution potentiellement obsolète. Les exécutions suivantes
profiteront des statistiques rafraîchies. En temps normal, cette option n’est pas
nécessaire.
• Vous pouvez mettre à jour manuellement vos statistiques durant vos fenêtres
d’administration, en utilisant par exemple sp_updatestats, ou une tâche d’un
plan de maintenance.
6.5 DATABASE ENGINE TUNING ADVISOR
Le Database Engine Tuning Advisor (DTA), traduit maladroitement en SQL Server
2005 par « assistant de paramétrage de base de données », est un outil graphique
prenant en charge l’analyse d’une charge de travail (workload) pour fournir automatiquement des recommandations de création de structures physiques de données
(physical design structures, PDS) destinées à améliorer les performances : index, vues
indexées et partitions. La charge de travail est soumise par le DTA au moteur d’optimisation de SQL Server, avec différentes options, pour juger des PDS les plus aptes
à améliorer les performances du workload.
DTA remplace l’outil disponible en SQL Server 2000, nommé Index Tuning
Wizard (ITW). Vous apprécierez le glissement sémantique : du magicien (wizard),
nous passons au conseiller (advisor), un sursaut d’humilité bienvenu (même si le
DTA est plus puissant que l’ITW) : bien que très utile et intéressant pour faire un
Chapitre 6. Utilisation des index
Nous avons vu que les mises à jour de statistiques sont indispensables à la bonne
performance des requêtes, et que leur maintenance est nécessaire pour assurer une
bonne qualité à travers le temps. Mais, quand cette opération se déclenche-t-elle ?
Tout comme la création automatique, elle se produit au besoin, c’est-à-dire au
moment où une requête va s’exécuter et pour laquelle ces statistiques sont utiles,
aussi bien dans le cas d’une requête ad hoc (c’est-à-dire d’une requête écrite une fois
pour un besoin particulier), que d’une procédure stockée. Si le seuil de modification
de la table est atteint, il va d’abord lancer une mise à jour des statistiques nécessaires,
puis ensuite générer son plan d’exécution. Cela peut évidemment provoquer une
latence à l’exécution de la requête. Pour les plans d’exécution déjà dans le cache, la
phase d’exécution inclut une vérification des statistiques, un UPDATE éventuel de celles-ci, puis une recompilation, ce qui dans certains cas peut se révéler encore plus
lourd.
Cela pose rarement un problème, mais si vous êtes dans le cas où cela affecte vos
performances, vous avez quelques solutions à votre disposition :
• AUTO_UPDATE_STATISTICS_ASYNC (option de base de donnéees) vous permet de
réaliser une mise à jour des statistiques de façon asynchrone. Cela veut dire
que lorsque le moteur SQL décide de mettre à jour ses statistiques il n’attend
pas la fin de cette opération pour exécuter la requête déclenchante. Elle
démarre donc plus vite, mais ne s’exécute pas forcément plus rapidement, car
avec un plan d’exécution potentiellement obsolète. Les exécutions suivantes
profiteront des statistiques rafraîchies. En temps normal, cette option n’est pas
nécessaire.
• Vous pouvez mettre à jour manuellement vos statistiques durant vos fenêtres
d’administration, en utilisant par exemple sp_updatestats, ou une tâche d’un
plan de maintenance.
6.5 DATABASE ENGINE TUNING ADVISOR
Le Database Engine Tuning Advisor (DTA), traduit maladroitement en SQL Server
2005 par « assistant de paramétrage de base de données », est un outil graphique
prenant en charge l’analyse d’une charge de travail (workload) pour fournir automatiquement des recommandations de création de structures physiques de données
(physical design structures, PDS) destinées à améliorer les performances : index, vues
indexées et partitions. La charge de travail est soumise par le DTA au moteur d’optimisation de SQL Server, avec différentes options, pour juger des PDS les plus aptes
à améliorer les performances du workload.
DTA remplace l’outil disponible en SQL Server 2000, nommé Index Tuning
Wizard (ITW). Vous apprécierez le glissement sémantique : du magicien (wizard),
nous passons au conseiller (advisor), un sursaut d’humilité bienvenu (même si le
DTA est plus puissant que l’ITW) : bien que très utile et intéressant pour faire un