sys.dm_db_missing_index_details (Transact-SQL)

Article
12/19/2023

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

Retourne des informations détaillées sur les index manquants.

Dans Azure SQL Database, les vues de gestion dynamique ne peuvent pas exposer les informations susceptibles d’avoir un impact sur le contenu de la base de données ou d’exposer des informations sur d’autres bases de données auxquelles l’utilisateur a accès. Pour éviter d’exposer ces informations, chaque ligne qui contient des données qui n’appartiennent pas au locataire connecté est filtrée.

Nom de la colonne	Type de données	Description
index_handle	int	Identifie un index manquant. L'identificateur est unique sur le serveur. `index_handle` est la clé de ce tableau.
database_id	smallint	Identifie la base de données dans laquelle réside la table comportant les index manquants. Dans Azure SQL Database, les valeurs sont uniques au sein d’une base de données unique ou d’un pool élastique, mais pas dans un serveur logique.
object_id	int	Identifie la table dans laquelle est situé l'index manquant.
equality_columns	nvarchar(4000)	Liste de colonnes, séparées par des virgules, qui contribuent aux prédicats d'égalité au format : table.column = constant_value
inequality_columns	nvarchar(4000)	Liste de colonnes, séparées par des virgules, qui contribuent aux prédicats d'inégalité, par exemple, les prédicats au format : table.column>constant_value Tout opérateur de comparaison autre que "=" exprime l'inégalité.
included_columns	nvarchar(4000)	Liste de colonnes, séparées par des virgules, requises comme colonnes de couverture pour la requête. Pour plus d’informations sur la couverture ou les colonnes incluses, consultez Créer des index avec des colonnes incluses. Pour les index à mémoire optimisée (hachage et non cluster à mémoire optimisée), ignorez `included_columns`. Toutes les commandes de la table sont incluses dans chaque index optimisé en mémoire.
instruction	nvarchar(4000)	Nom de la table dans laquelle est situé l'index manquant.

Notes

Les informations retournées par sys.dm_db_missing_index_details sont mises à jour lorsqu’une requête est optimisée par l’optimiseur de requête et n’est pas conservée. Les informations d’index manquantes sont conservées uniquement tant que le moteur de base de données n’est pas redémarré. Les administrateurs de base de données doivent effectuer régulièrement des copies de sauvegarde des informations sur les index manquants s'ils souhaitent les conserver après le recyclage du serveur. Utilisez la colonne sqlserver_start_time dans sys.dm_os_sys_info pour rechercher la dernière heure de démarrage du moteur de base de données.

Pour déterminer les groupes d’index manquants dont un index manquant particulier fait partie, vous pouvez interroger la sys.dm_db_missing_index_groups vue de gestion dynamique en l’équijoinant en sys.dm_db_missing_index_details fonction de la index_handle colonne.

Remarque

Le jeu de résultats pour cette vue de gestion dynamique est limité à 600 lignes. Chaque ligne contient un index manquant. Si vous avez plus de 600 index manquants, vous devez traiter les index manquants existants afin de pouvoir afficher les plus récents.

Utilisation des informations d’index manquantes dans les instructions CREATE INDEX

Pour convertir les informations retournées par sys.dm_db_missing_index_details une instruction CREATE INDEX pour les index mémoire optimisés et basés sur disque, les colonnes d’égalité doivent être placées avant les colonnes d’inégalité, et ensemble, elles doivent rendre la clé de l’index. Les colonnes incluses doivent être ajoutées à l'instruction CREATE INDEX à l'aide de la clause INCLUDE. Pour déterminer un ordre efficace pour les colonnes d'égalité, vous devez les organiser en fonction de leur sélectivité : répertoriez les colonnes les plus sélectives (les colonnes de gauche dans la liste des colonnes). En savoir plus sur l’optimisation des index non cluster avec des suggestions d’index manquantes, notamment les limitations de la fonctionnalité d’index manquante.

Pour plus d’informations sur les index à mémoire optimisée, consultez Index pour les tables mémoire optimisées.

Cohérence des transactions

Si une transaction crée ou supprime une table, les lignes qui contiennent les informations d'index manquants concernant les objets supprimés sont retirées de cet objet de gestion dynamique, ce qui permet de préserver la cohérence des transactions. En savoir plus sur les limitations de la fonctionnalité d’index manquante.

autorisations

Sur SQL Server et SQL Managed Instance, l’autorisation VIEW SERVER STATE est requise.

Sur les objectifs de service SQL Database Basic, S0 et S1, et pour les bases de données dans des pools élastiques, le compte d’administrateur du serveur, le compte d’administrateur Microsoft Entra ou l’appartenance au ##MS_ServerStateReader## rôle serveur est requis. Sur tous les autres objectifs de service SQL Database, l’autorisation VIEW DATABASE STATE sur la base de données ou l’appartenance au rôle serveur ##MS_ServerStateReader## est requise.

Autorisations pour SQL Server 2022 (et versions plus récentes)

Nécessite l’autorisation VIEW SERVER PERFORMANCE STATE sur le serveur.

Exemples

L’exemple suivant retourne des suggestions d’index manquantes pour la base de données active. Les suggestions d’index manquantes doivent être combinées si possible avec les autres et avec des index existants dans la base de données active. Découvrez comment appliquer ces suggestions dans l’optimisation des index non cluster avec des suggestions d’index manquantes.

SELECT
  CONVERT (varchar(30), getdate(), 126) AS runtime,  mig.index_group_handle,  mid.index_handle,
  CONVERT (decimal (28, 1), migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) ) AS improvement_measure,
  'CREATE INDEX missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle) + ' ON ' + mid.statement + ' (' + ISNULL (mid.equality_columns, '') + CASE
    WHEN mid.equality_columns IS NOT NULL
    AND mid.inequality_columns IS NOT NULL THEN ','
    ELSE ''
  END + ISNULL (mid.inequality_columns, '') + ')' + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement,
  migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
	INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
	INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE CONVERT (decimal (28, 1),migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans)) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

Remarque

Le script Index-Creation dans Tiger Toolbox de Microsoft examine les vues DMV d’index manquants et supprime automatiquement tous les index suggérés redondants. De plus, il analyse les index à faible impact et génère des scripts de création d’index que vous pouvez passer en revue. Comme dans la requête ci-dessus, il n’exécute PAS les commandes de création d’index. Le script Index-Creation convient à SQL Server et Azure SQL Managed Instance. Pour Azure SQL Database, implémentez le paramétrage automatique d’index.

Étapes suivantes

En savoir plus sur la fonctionnalité d’index manquante dans les articles suivants :

Partager via