[SQL Server] DMV : comment identifier les indexes manquants ?

Ce billet présente quelques DMV permettant d’identifier des indexes manquants.

Vous pouvez jeter un coup d’œil ici, pour en savoir plus sur l’architecture d’un index.

Présentation des DMVs utiles pour la détermination des indexes manquants

Le tableau ci-dessous présente quelques DMVs utiles :

DMV Description Remarque
sys.dm_db_missing_index_details Permet de récupérer des informations détaillées relatives aux indexes manquants (hors indexes spatiaux). Les informations sont mises-à-jour en cas d’optimisation de la (ou les) requête(s) utilisant les tables ou vues concernées, ou suite au redémarrage de l’instance.
sys.dm_db_missing_index_group_stats Permet de récupérer un résumé sur les groupes d’indexes manquants (hors indexes spatiaux). Les informations sont mises-à-jour à chaque exécution (ou optimisation) de requêtes utilisant les tables ou vues concernées, ou au redémarrage de l’instance.
sys.dm_db_missing_index_groups Permet de récupérer des informations relatives à des indexes manquants contenus dans un groupe d’indexes manquants spécifique (hors indexes spatiaux). Les informations sont mises-à-jour en cas d’optimisation de la (ou les) requête(s) utilisant les tables ou vues concernées, ou suite au redémarrage de l’instance.
sys.dm_db_missing_index_columns Permet de récupérer des informations relatives aux colonnes utilisées par un index manquant (hors indexes spatiaux). Les informations sont mises-à-jour en cas d’optimisation de la (ou les) requête(s) utilisant les tables ou vues concernées, ou suite au redémarrage de l’instance.
Bien que ces DMVs peuvent être utiles pour déterminer quels sont les indexes manquants pouvant contribuer à optimiser une ou plusieurs requêtes, il est de bon ton de savoir qu’une sur-indexation peut impacter les performances des requêtes d’insertion, mise-à-jour ou suppression, et la volumétrie de la base de données.
En outre, les informations fournies par les DMVs ne concernent que jusqu’à 500 indexes manquants.

Utilisation de la DMF sys.dm_db_index_physical_stats

Lançons la requête suivante (exemple arbitraire basé sur la base de données AdventureWorks) :

USE AdventureWorks
GO
SELECT TransactionID ,
 ProductID ,
 ReferenceOrderID ,
 ReferenceOrderLineID ,
 TransactionDate ,
 Quantity ,
 ActualCost ,
 ModifiedDate
FROM Production.TransactionHistory
WHERE TransactionType = N'W'
GO

La requête ci-dessous combine les informations utiles des 4 DMVs présentées dans la section précédente, dans le but d’identifier le nom de la table concernée, le nom de la colonne, le groupe d’indexes manquants, le nombre de scans/seeks, leurs impacts (par rapport au manque d’index),…

SELECT dmig.index_group_handle ,
 dmig.index_handle ,
 statement AS table_name ,
 column_id ,
 column_name ,
 column_usage ,
 dmigs.user_seeks ,
 dmigs.user_scans ,
 dmigs.last_user_seek ,
 dmigs.avg_user_impact
FROM sys.dm_db_missing_index_details AS dmid
 CROSS
APPLY sys.dm_db_missing_index_columns(dmid.index_handle)
 INNER
JOIN sys.dm_db_missing_index_groups AS dmig ON dmig.index_handle = dmid.index_handle
 INNER
JOIN sys.dm_db_missing_index_group_stats AS dmigs ON dmig.index_group_handle = dmigs.group_handle
ORDER BY dmig.index_group_handle ,
 dmig.index_handle ,
 column_id
GO

Voici le résultat :


La colonne column_usage offre des informations intéressantes permettant de savoir de quelle façon les colonnes (de column_name) pourraient être indexées. Ainsi :

  • INCLUDE à TransactionID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, Quantity, ActualCost et ModifiedDate pourraient être utilisées comme colonnes incluses (ou couvertes) dans (par) l’index manquant.
  • EQUALITY à TransactionType doit être indexée.

Le fait de n’avoir qu’un seul groupe d’indexes manquants (colonne index_group_handle) signifie donc que toutes les colonnes à créer concernent un seul index manquant. En effet, un groupe d’indexes ne contient qu’un seul index (index_handle).

En bref, l’optimiseur de requêtes a déterminé qu’il valait mieux créer un index sur la colonne utilisée dans la clause WHERE (TransactionType, donc) et d’y inclure ceux de la clause SELECT (TransationID, ProductID, ReferenceOrderID, ReferenceOrderLineID, TransactionDate, Quantity, ActualCost et ModifiedDate), ce qui donnerait la requête DDL suivante (nom d’index arbitraire) :

USE AdventureWorks
GO
CREATE NONCLUSTERED INDEX IDX_TransactionType_INC_TranID_PrdID_RefOrdID_RefOrdLineID_TranDate_Qty_Cost_ModDate
 ON Production.TransactionHistory(TransactionType)
 INCLUDE (TransactionID,ProductID,ReferenceOrderID,ReferenceOrderLineID,TransactionDate,Quantity,ActualCost,ModifiedDate)
GO
Notons que si vous utilisez un opérateur autre que celui d’égalité, il est possible de voir apparaître INEQUALITY.

Pour aller plus loin…

Vous pouvez garder un œil ici, en ce qui concerne les autres billets sur les indexes.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s