Parcourir les aide-mémoire · SQL Server
Trouver les index manquants
Utilisez les DMV d’index manquants de SQL Server comme signal de tri, classez les candidats selon leur impact estimé et validez chaque recommandation avant de créer un index.
Sur cette page
- Problème
- Réponse rapide
- Code / commandes
- 1. Classer les candidats d’index manquants selon le gain estimé
- 2. Afficher le détail brut des index manquants pour une table
- 3. Comparer les recommandations d’index manquants aux index existants
- 4. Générer un modèle CREATE INDEX à relire
- 5. Vérifier l’activité d’écriture sur la table cible
- 6. Vérifier quand la fenêtre d’observation actuelle a commencé
- Interpréter le résultat
- Points à vérifier
- Score élevé + nombre de seeks élevé
- Nombreuses recommandations pour la même table
- Longues listes INCLUDE
- Colonnes d’égalité et d’inégalité mélangées
- Recommandation apparue une seule fois après un redémarrage récent
- Notes de production
- Les DMV d’index manquants sont indicatives
- Les recommandations sont éphémères
- Les recommandations peuvent se recouvrir
- Les DMV d’index manquants ne disent pas tout
- Les autorisations varient selon la version et la plateforme
- La création d’un index peut provoquer des blocages ou consommer beaucoup de ressources
- Erreurs fréquentes
- 1. Transformer chaque recommandation en instruction CREATE INDEX
- 2. Considérer avg_user_impact comme un pourcentage d’amélioration garanti
- 3. Ignorer les index existants
- 4. Ignorer le coût en écriture
- 5. Créer des index couvrants très larges
- 6. Utiliser une fenêtre d’observation trop courte
- 7. Supposer qu’un index manquant est la cause racine
- Étape suivante
- Pour aller plus loin
Problème
SQL Server peut enregistrer des suggestions d’index manquants lorsque l’optimiseur estime qu’un autre index pourrait réduire le coût d’une requête.
Ces DMV sont utiles, mais ce sont des recommandations, pas des instructions de conception.
L’objectif est de répondre aux questions suivantes :
- Quelles suggestions d’index manquants présentent la plus forte valeur estimée ?
- Quelles requêtes ou quelles tables en sont à l’origine ?
- Plusieurs suggestions demandent-elles en réalité presque le même index ?
- Un index existant couvre-t-il déjà l’essentiel de la recommandation ?
- Quel est le gain probable en lecture par rapport au coût en écriture et en stockage ?
Réponse rapide
Commencez par joindre les DMV d’index manquants et classez les candidats à l’aide du signal de gain estimé par l’optimiseur.
SELECT TOP (50)
DB_NAME(mid.database_id) AS database_name,
OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
migs.user_seeks,
migs.user_scans,
migs.avg_total_user_cost,
migs.avg_user_impact,
CAST(
migs.user_seeks
* migs.avg_total_user_cost
* (migs.avg_user_impact / 100.0)
AS decimal(18,2)
) AS estimated_improvement_score,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mid.index_handle = mig.index_handle
WHERE
mid.database_id = DB_ID()
ORDER BY
estimated_improvement_score DESC;
Utilisez ce résultat pour prioriser l’analyse. Ne transformez pas directement la sortie en instructions CREATE INDEX sans vérifier les index existants, l’importance de la charge de travail, l’ordre des clés et le coût en écriture.
Code / commandes
1. Classer les candidats d’index manquants selon le gain estimé
C’est la meilleure requête pour une première passe.
SELECT TOP (50)
DB_NAME(mid.database_id) AS database_name,
OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id) AS schema_name,
OBJECT_NAME(mid.object_id, mid.database_id) AS table_name,
migs.unique_compiles,
migs.user_seeks,
migs.user_scans,
migs.last_user_seek,
migs.last_user_scan,
migs.avg_total_user_cost,
migs.avg_user_impact,
CAST(
(migs.user_seeks + migs.user_scans)
* migs.avg_total_user_cost
* (migs.avg_user_impact / 100.0)
AS decimal(18,2)
) AS estimated_improvement_score,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mid.index_handle = mig.index_handle
WHERE
mid.database_id = DB_ID()
ORDER BY
estimated_improvement_score DESC;
Le score est une heuristique de tri fondée sur le coût et l’impact estimés par l’optimiseur.
Ce n’est pas un gain de performance garanti.
2. Afficher le détail brut des index manquants pour une table
Utilisez cette requête lorsqu’une table apparaît à plusieurs reprises dans le classement.
DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name sysname = N'YourTable';
SELECT
mid.index_handle,
mid.database_id,
mid.object_id,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns,
mid.statement
FROM sys.dm_db_missing_index_details AS mid
WHERE
mid.database_id = DB_ID()
AND mid.object_id = OBJECT_ID(
QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name)
)
ORDER BY
mid.index_handle;
Si vous voyez plusieurs lignes similaires, regroupez-les mentalement avant de concevoir quoi que ce soit.
Par exemple, ces suggestions peuvent largement se recouvrir :
(A, B) INCLUDE (C)
(A, B) INCLUDE (C, D)
(A, B, E) INCLUDE (C)
Créer les trois est rarement la bonne réponse.
3. Comparer les recommandations d’index manquants aux index existants
Avant de créer quoi que ce soit, examinez les définitions d’index actuelles.
DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name sysname = N'YourTable';
SELECT
i.index_id,
i.name AS index_name,
i.type_desc,
i.is_unique,
i.is_primary_key,
i.is_unique_constraint,
i.has_filter,
i.filter_definition,
ic.key_ordinal,
ic.is_included_column,
c.name AS column_name
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
ON ic.object_id = i.object_id
AND ic.index_id = i.index_id
INNER JOIN sys.columns AS c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id
WHERE
i.object_id = OBJECT_ID(
QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name)
)
AND i.is_hypothetical = 0
ORDER BY
i.index_id,
ic.is_included_column,
ic.key_ordinal,
ic.index_column_id;
Vous cherchez :
- les mêmes colonnes de tête dans la clé
- des ensembles INCLUDE quasi identiques
- la même logique de filtre
- des index qui pourraient éventuellement être élargis plutôt que dupliqués
- une sémantique d’unicité ou de contrainte qui compte
4. Générer un modèle CREATE INDEX à relire
Cette requête produit un brouillon, pas un script de déploiement automatique.
SELECT TOP (50)
'CREATE INDEX IX_'
+ REPLACE(
REPLACE(
OBJECT_NAME(mid.object_id, mid.database_id),
']',
''
),
' ',
'_'
)
+ '_MissingIndex'
+ CHAR(13) + CHAR(10)
+ 'ON '
+ QUOTENAME(OBJECT_SCHEMA_NAME(mid.object_id, mid.database_id))
+ '.'
+ QUOTENAME(OBJECT_NAME(mid.object_id, mid.database_id))
+ ' ('
+ COALESCE(mid.equality_columns, '')
+ CASE
WHEN mid.equality_columns IS NOT NULL
AND mid.inequality_columns IS NOT NULL
THEN ', '
ELSE ''
END
+ COALESCE(mid.inequality_columns, '')
+ ')'
+ CASE
WHEN mid.included_columns IS NOT NULL
THEN ' INCLUDE (' + mid.included_columns + ')'
ELSE ''
END
+ ';' AS create_index_draft,
mid.equality_columns,
mid.inequality_columns,
mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON mig.index_group_handle = migs.group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mid.index_handle = mig.index_handle
WHERE
mid.database_id = DB_ID()
ORDER BY
(
migs.user_seeks
* migs.avg_total_user_cost
* (migs.avg_user_impact / 100.0)
) DESC;
Relisez manuellement le SQL généré.
Les modifications habituelles avant déploiement peuvent consister à :
- choisir un vrai nom d’index
- supprimer les colonnes INCLUDE redondantes
- modifier l’ordre des clés
- fusionner plusieurs recommandations similaires
- envisager un index filtré
- décider de ne pas créer l’index du tout
5. Vérifier l’activité d’écriture sur la table cible
Un index optimisé pour la lecture peut pénaliser une table fortement modifiée.
DECLARE @schema_name sysname = N'dbo';
DECLARE @table_name sysname = N'YourTable';
SELECT
s.name AS schema_name,
t.name AS table_name,
i.name AS index_name,
COALESCE(us.user_seeks, 0) AS user_seeks,
COALESCE(us.user_scans, 0) AS user_scans,
COALESCE(us.user_lookups, 0) AS user_lookups,
COALESCE(us.user_updates, 0) AS user_updates
FROM sys.indexes AS i
INNER JOIN sys.tables AS t
ON t.object_id = i.object_id
INNER JOIN sys.schemas AS s
ON s.schema_id = t.schema_id
LEFT JOIN sys.dm_db_index_usage_stats AS us
ON us.database_id = DB_ID()
AND us.object_id = i.object_id
AND us.index_id = i.index_id
WHERE
t.object_id = OBJECT_ID(
QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name)
)
AND i.is_hypothetical = 0
ORDER BY
i.index_id;
Si la table subit beaucoup d’écritures, chaque index ajouté a un coût.
6. Vérifier quand la fenêtre d’observation actuelle a commencé
Les recommandations d’index manquants sont éphémères.
SELECT
sqlserver_start_time
FROM sys.dm_os_sys_info;
Si l’instance a redémarré récemment, les DMV d’index manquants ne reflètent que la charge de travail depuis ce redémarrage.
Cette fenêtre peut être trop courte pour prendre une décision de conception.
Interpréter le résultat
| Champ | Pourquoi c’est important |
|---|---|
user_seeks |
Nombre de recherches (seeks) utilisateur qui auraient pu bénéficier de la recommandation. |
user_scans |
Nombre d’analyses (scans) utilisateur associées à la recommandation. |
avg_total_user_cost |
Coût moyen, estimé par l’optimiseur, des requêtes utilisateur associées au groupe. |
avg_user_impact |
Estimation par l’optimiseur de la réduction possible du coût de la requête grâce à l’index, exprimée en pourcentage. |
estimated_improvement_score |
Heuristique de tri combinant fréquence, coût et impact estimé. Pas une garantie. |
equality_columns |
Colonnes référencées par des prédicats d’égalité tels que =. |
inequality_columns |
Colonnes référencées par des prédicats de type plage tels que >, < ou BETWEEN. |
included_columns |
Colonnes hors clé que l’optimiseur suggère de couvrir dans l’index. |
unique_compiles |
Nombre de compilations/recompilations ayant contribué au groupe de recommandations. |
last_user_seek |
Dernière recherche (seek) observée associée à la recommandation. |
Points à vérifier
Score élevé + nombre de seeks élevé
C’est un bon candidat pour une analyse approfondie.
Vérifiez que :
- la requête est importante pour le métier
- la charge de travail est représentative
- aucun index existant ne couvre déjà l’essentiel du modèle d’accès
- le nouvel index n’entraînera pas un coût d’écriture excessif
Nombreuses recommandations pour la même table
Cela signifie souvent que la sortie brute des DMV doit être consolidée.
Repérez les colonnes de tête répétées et les ensembles INCLUDE qui se recouvrent.
Un petit nombre d’index soigneusement conçus vaut généralement mieux que l’application littérale de chaque recommandation.
Longues listes INCLUDE
Soyez prudent.
Un index couvrant très large peut :
- consommer beaucoup de stockage
- accroître la pression sur le buffer pool
- augmenter le coût en écriture
- allonger la durée de maintenance
Ne couvrez que ce qui apporte un bénéfice défendable pour la charge de travail.
Colonnes d’égalité et d’inégalité mélangées
Un point de départ courant consiste à placer :
- les colonnes d’égalité en premier
- les colonnes d’inégalité ensuite
Mais ne considérez pas le texte renvoyé par la DMV comme une conception parfaite de l’ordre des clés.
La sélectivité des colonnes, les besoins de tri, les modèles de jointure, les index existants et la nature de la charge de travail restent déterminants.
Recommandation apparue une seule fois après un redémarrage récent
Indice faible.
Collectez les données sur une fenêtre d’observation plus longue ou validez avec Query Store et les plans d’exécution.
Notes de production
Les DMV d’index manquants sont indicatives
L’optimiseur enregistre ce qu’il estime pouvoir aider pour un problème d’optimisation précis.
Il ne tient pas compte de l’ensemble de vos index, de la charge d’écriture, du budget de stockage, de la stratégie de maintenance ni de toutes les autres requêtes sur la table.
Les recommandations sont éphémères
Les informations sur les index manquants peuvent être effacées par des événements tels qu’un redémarrage de SQL Server.
Ne considérez pas les DMV comme un historique durable.
Si vous voulez analyser des tendances, enregistrez-en périodiquement des instantanés dans votre propre base de supervision.
Les recommandations peuvent se recouvrir
Une même charge de travail sous-jacente peut générer plusieurs suggestions d’index similaires.
Les appliquer toutes aveuglément peut entraîner une prolifération d’index.
Comparez toujours :
- les colonnes de clé
- l’ordre des clés
- les colonnes INCLUDE
- les filtres
- l’unicité
- les index existants
- l’importance de la charge de travail
Les DMV d’index manquants ne disent pas tout
Elles ne remplacent pas :
- l’analyse des plans d’exécution
- Query Store
- les statistiques d’utilisation des index
- l’analyse de la taille des tables
- l’analyse du volume d’écriture
- les tests sur une charge de travail réelle
Les autorisations varient selon la version et la plateforme
L’accès aux DMV d’index manquants dépend de la version et de la plateforme de SQL Server.
Sur les versions récentes de SQL Server, des autorisations d’état des performances au niveau du serveur sont utilisées pour une visibilité étendue sur les DMV ; les versions antérieures reposent généralement sur VIEW SERVER STATE.
Azure SQL Database a ses propres règles de portée et d’autorisations.
La création d’un index peut provoquer des blocages ou consommer beaucoup de ressources
Sur une grande table de production, la création d’un index peut nécessiter une quantité importante de :
- CPU
- E/S
- journal des transactions
- mémoire
- tempdb
- activité de verrouillage/métadonnées
N’utilisez les options en ligne (online) ou avec reprise (resumable) que si votre édition/version de SQL Server les prend en charge et qu’elles s’intègrent à votre plan opérationnel.
Erreurs fréquentes
1. Transformer chaque recommandation en instruction CREATE INDEX
C’est l’erreur la plus fréquente.
Les DMV d’index manquants génèrent des suggestions propres à une charge de travail, pas une conception d’index optimisée globalement.
2. Considérer avg_user_impact comme un pourcentage d’amélioration garanti
C’est une estimation de l’optimiseur.
Cela ne signifie pas que la requête deviendra littéralement 80 % plus rapide.
3. Ignorer les index existants
Une recommandation peut être proche d’un index que vous avez déjà.
Parfois, la bonne réponse consiste à :
- modifier un index existant
- fusionner deux index
- ajuster les colonnes INCLUDE
- ne rien faire
4. Ignorer le coût en écriture
Chaque index non cluster doit être maintenu lors des modifications de données qui le concernent.
Le gain en lecture doit justifier ce coût permanent.
5. Créer des index couvrants très larges
Davantage de colonnes INCLUDE peut réduire les lookups, mais les index larges peuvent devenir coûteux en stockage, en mémoire, en écritures et en maintenance.
6. Utiliser une fenêtre d’observation trop courte
Un redémarrage peut donner aux DMV une apparence propre et ciblée simplement parce qu’elles ne contiennent que la charge de travail récente.
Ne repensez pas l’indexation à partir de quelques minutes ou heures de données, sauf si l’incident lui-même justifie ce périmètre restreint.
7. Supposer qu’un index manquant est la cause racine
Une requête lente peut aussi être due à :
- de mauvaises estimations de cardinalité
- des blocages
- des statistiques obsolètes ou inadaptées
- une sensibilité aux paramètres
- une pression mémoire
- une requête mal construite
- une pression sur tempdb
- une contention des ressources à l’échelle du serveur
Considérez les indices d’index manquants comme un signal parmi d’autres.
Ce contenu vous a-t-il été utile ?
Que pourrions-nous améliorer ?
Merci — cela m’aide à améliorer Javi Builds.
Restez au courant
Recevez uniquement les nouvelles de Javi Builds qui vous intéressent.
Consultez votre boîte de réception
Je vous ai envoyé un lien de confirmation. Votre abonnement sera actif après confirmation.