Les requêtes de cette page lisent des informations de l'instance sans modifier vos données. Les commandes qui modifient quelque chose sont signalées dans le texte. Testez toujours sur un environnement non critique avant la production, et relisez chaque requête avant de l'exécuter.
Le script
Une clé étrangère est considérée comme indexée si un index de la table enfant commence par les colonnes de la clé (dans n'importe quel ordre).
-- Clés étrangères de la base courante et présence d'un index sur la table enfant
-- À exécuter dans la base à analyser.
SET NOCOUNT ON;
;WITH cle_etrangere AS (
SELECT f.object_id AS fk_id,
f.name AS fk_nom,
f.parent_object_id AS table_enfant_id,
f.referenced_object_id AS table_parente_id,
COUNT(*) AS nb_colonnes
FROM sys.foreign_keys AS f
JOIN sys.foreign_key_columns AS fc
ON fc.constraint_object_id = f.object_id
GROUP BY f.object_id, f.name, f.parent_object_id, f.referenced_object_id
)
SELECT
SCHEMA_NAME(oe.schema_id) + N'.' + oe.name AS table_enfant,
c.fk_nom AS cle_etrangere,
SCHEMA_NAME(op.schema_id) + N'.' + op.name AS table_parente,
cols.colonnes AS colonnes_cle,
CASE WHEN ix.index_nom IS NULL THEN 'NON' ELSE 'OUI' END AS cle_indexee,
ix.index_nom AS index_couvrant,
n.nb_lignes AS lignes_table_enfant
FROM cle_etrangere AS c
JOIN sys.objects AS oe ON oe.object_id = c.table_enfant_id
JOIN sys.objects AS op ON op.object_id = c.table_parente_id
CROSS APPLY (
SELECT STUFF((SELECT N', ' + col.name
FROM sys.foreign_key_columns AS fc
JOIN sys.columns AS col
ON col.object_id = fc.parent_object_id
AND col.column_id = fc.parent_column_id
WHERE fc.constraint_object_id = c.fk_id
ORDER BY fc.constraint_column_id
FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'), 1, 2, N'') AS colonnes
) AS cols
OUTER APPLY (
-- un index dont les n premières colonnes clés sont exactement les colonnes de la clé étrangère
SELECT TOP (1) i.name AS index_nom
FROM sys.indexes AS i
WHERE i.object_id = c.table_enfant_id
AND i.index_id > 0
AND i.is_disabled = 0
AND i.is_hypothetical = 0
AND NOT EXISTS (
SELECT 1
FROM sys.foreign_key_columns AS fc
WHERE fc.constraint_object_id = c.fk_id
AND NOT EXISTS (
SELECT 1
FROM sys.index_columns AS ic
WHERE ic.object_id = i.object_id
AND ic.index_id = i.index_id
AND ic.column_id = fc.parent_column_id
AND ic.key_ordinal BETWEEN 1 AND c.nb_colonnes))
ORDER BY i.index_id
) AS ix
CROSS APPLY (SELECT SUM(p.row_count) AS nb_lignes
FROM sys.dm_db_partition_stats AS p
WHERE p.object_id = c.table_enfant_id
AND p.index_id IN (0, 1)) AS n
ORDER BY CASE WHEN ix.index_nom IS NULL THEN 0 ELSE 1 END,
n.nb_lignes DESC,
table_enfant;Comment lire le résultat
- Les clés non indexées sont en tête, classées par volume de la table enfant : ce sont les plus coûteuses en cas de suppression ou de modification dans la table parente.
- index_couvrant : l'index qui commence par les colonnes de la clé. S'il existe, la clé est indexée.
- Sur une petite table enfant, l'absence d'index est sans conséquence. Sur une grande, elle provoque scans et blocages.
Corriger une clé non indexée
Créer un index sur les colonnes de la clé étrangère, dans le même ordre :
-- Exemple : clé étrangère Commandes.ClientId -> Clients.Id
CREATE NONCLUSTERED INDEX IX_Commandes_ClientId
ON dbo.Commandes (ClientId);- Avant de créer l'index, vérifiez qu'un index existant ne peut pas être élargi à la place.
- Les clés étrangères sont aussi utilisées dans les jointures : un index sur leurs colonnes profite souvent aux requêtes de lecture.
- Une clé étrangère peut volontairement ne pas être indexée si la table parente n'est jamais modifiée ni supprimée : la décision se justifie, elle ne se subit pas.