Lorsque l'exécution de
nos requêtes deviennent de plus en plus longue à être exécutées,
c'est souvent un signe d'une mauvaise stratégie d'implémentation
d'index d'une base de données sur les tables que nos requêtes
utilisent.
Pour nous aider, SQL
Server nous expose trois vues de gestion dynamique (DMV: Dynamic
management view) qui nous indiquent quelles tables auraient besoin
d'index, avec quels champs et quels bénéfices on en tirerait en
ajoutant ces index.
Petite note importante,
comme toutes les vues de gestion dynamique, elles sont réinitialisées
à chaque redémarrage du serveur. Aussi, pour vous aider dans votre
stratégie d'implémentation d'index, il est important d'exécuter
ces requêtes sur le serveur de production plutôt que sur un serveur
de développement ou de test. En faisant cela, on s'assure que le
résultat de nos requêtes refléteront la réalité de la production
plutôt que d'être influencé par des requêtes de déboggage en
développement ou ne pas bien refléter la charge de travail réelle
en se basant sur l'environnement de test seulement.
sys.dm_db_missing_index_detail
Cette vue nous indique la
table et les colonnes qui bénéficierait d'un index. Cette vue
sépare les colonnes en trois types bien distinct : les colonnes
d'égalités, les colonnes d'inégalité et les colonnes
d'inclusions. Lors de la création d'un index, on prendra soin de
spécifier les colonnes d'égalités en premier, suivi des colonnes
d'inégalités. Les colonnes d'inclusions nous indiquent les
colonnes à inclure dans la création de l'index, avec la clause
INCLUDE.
sys.dm_db_missing_index_group_stat
Cette vue nous indique les
bénéfices que l'index apporterait en fonction des requêtes
effectuées depuis le dernier démarrage du serveur SQL. Les colonnes
qui nous importeront le plus sont les colonnes user_seeks,
user_scans, avg_total_user_cost, avg_user_impact ainsi que leur
contrepartie système system_seeks, system_scans,
avg_total_system_cost et avg_system_impact.
Les colonnes user_seeks et
user_scans nous indique le nombre de fois qu'un requête a eu besoin
de faire un recherche direct (user_seek) ou un balayage (user_scans)
des données. La colonne avg_total_user_cost nous indique la réduction
du coût de la requête si l'index existerait et la colonne
avg_user_impact nous donne un indice de la réduction, en
pourcentage, du coût de la requête. Lorsqu'on décidera
d'implémenter un index plutôt qu'un autre, on devrait tenir compte
de ces informations.
sys.dm_db_missing_index_groups
Cette vue est seulement
une vue d'association entre les bénéfices de l'implémentation d'un
index et des colonnes de ce dernier.
À titre d'exemple, je
prends le petit script suivant pour démonter comment on peut
améliorer les performance grace à l'indexation d'une table.
Au départ, le
script créer la base de données « SQLQuébec » ainsi
que le schéma « SQLQuébec » si ceux ci n'existe pas.
Ensuite, le script crée une table « SQLQuébec.TestIndexManquant »
qui contiendra un million d'enregistrements sans aucun index pour
continuer par une première boucle de mille requêtes seulement,
question de ne pas faire rouler la requête trop longtemps
inutilement.
On affiche ensuite
le temps d'exécution pour ces mille requêtes ainsi que les
informations des vues SQL Server pour connaître les bénéfices
qu'on pourrait avoir en implémentant un index.
Ensuite, on créer
l'index de façon dynamique en interrogeant les les vues et on
exécute une deuxième boucle, d'un millions de requêtes cette fois
ci.
Les résultats
peuvent varier selon les configurations du serveur SQL et de la
charge du serveur lors de l'exécution des requêtes. Lors de
l'exécution du script sur mon serveur SQL, on voit que les premières
mille requêtes sans index se sont exécutées en 32 secondes, alors
que un millions de requêtes sur la même table se sont exécutées
en 10 secondes. En extrapolant avec la bonne vieille règle de 3, on
peut dire que 1 000 000 de requêtes sur la table sans index aurait
pris près de 9 heures à s'exécuter, tandis qu'avec un index,
il nous a fallu seulement 10 secondes pour exécuter le million de
requêtes.
Références :
Aucun commentaire:
Publier un commentaire