Dans la suite de l’article Index manqants pour les clés étrangères, il convient de prendre en main la liste (éventuellement longue) des clés étrangères listées et de chercher à constituer des index autour de ce besoin. Voici un petit complément à l’article, qui présente un script donnant en plus des instructions de création d’index.

Je tiens à bien préciser que les instructions proposées ne sont pas nécessairement celles que vous devrez passer sur votre base de données, notamment vis-à-vis des points suivants :

• L’analyse des plans d’exécution des requêtes mettra peut-être en évidence le besoin de créer des index à plusieurs colonnes (ou avec des colonnes incluses) qui contiendraient certains index minimalistes associés aux clés étrangères
• Les instructions de création proposées sont minimalistes. Elles ne tiennent notamment pas compte du positionnement des index sur un autre FileGroup ni d’un éventuel partitionnement. Elles ne précisent pas non plus le taux de remplissage ou autres paramètres des index
• La redondance de clés étrangères pourra amener à la création d’index en doublons. Pour détecter ce point, je vous invite à consulter cet article.

Voici donc le script complété (une colonne de plus dans la liste finale) :

-- Liste des clés étrangères avec index absent
-- Avec génération d'instructions de création d'index
-- Auteur : Jean-Nicolas BERGER (www.sqlserver.fr)
-- Declaration des variables
DECLARE @FK TABLE (fk_id int,
					colonnes varchar(MAX) DEFAULT '',
                    noms_colonnes varchar(MAX) DEFAULT NULL)
DECLARE @Index TABLE (object_id int, 
					index_id int,
					colonnes varchar(MAX) DEFAULT '')
DECLARE @id1 int, @id2 int, @id3 int
DECLARE @nom_colonne varchar(MAX)

-- Initialisation
INSERT INTO @FK (fk_id)
SELECT fk.object_id
FROM sys.foreign_keys fk

INSERT INTO @Index (object_id,index_id)
SELECT i.object_id,
       i.index_id
FROM sys.indexes i

-- Listes des colonnes des FK
DECLARE fk CURSOR FOR
SELECT fkc.constraint_object_id,
       fkc.parent_column_id,
       c.name
FROM sys.foreign_key_columns fkc
JOIN sys.columns c ON c.object_id=fkc.parent_object_id
AND c.column_id=fkc.parent_column_id
ORDER BY fkc.constraint_column_id OPEN fk FETCH NEXT
FROM fk INTO @id1,
             @id2,
             @nom_colonne

WHILE @@FETCH_STATUS = 0
BEGIN

	UPDATE @Fk
	SET colonnes+=convert(varchar(MAX),@id2)+',',
		noms_colonnes=isnull(noms_colonnes+',','')+QUOTENAME(@nom_colonne)
	WHERE fk_id=@id1 FETCH NEXT
	  FROM fk INTO @id1,
				   @id2,
				   @nom_colonne

END
CLOSE fk
DEALLOCATE fk
			   
-- Liste des colonnes des index
DECLARE ind CURSOR FOR
  SELECT ic.object_id,
         ic.index_id,
         ic.column_id
  FROM sys.index_columns ic WHERE key_ordinal>0
  ORDER BY ic.key_ordinal
OPEN ind
FETCH NEXT FROM ind INTO @id1,@id2,@id3
WHILE @@FETCH_STATUS = 0
BEGIN

	UPDATE @Index
	SET colonnes+=convert(varchar(MAX),@id3)+','
	WHERE object_id=@id1 AND index_id=@id2

FETCH NEXT FROM ind INTO @id1,@id2,@id3
END
CLOSE ind
DEALLOCATE ind
				
-- FK sans index (avec script de création d'index)
SELECT DISTINCT QUOTENAME(s.name)+'.'+QUOTENAME(t.name) AS [Table parente],
                  QUOTENAME(s_r.name)+'.'+QUOTENAME(t_r.name) AS [Table de référence],
                  QUOTENAME(fk.name) AS [Clé étrangère],
                  'CREATE INDEX ' + QUOTENAME('IX_' + fk.name) + ' ON ' + QUOTENAME(s.name)+'.'+QUOTENAME(t.name) + '(' + fk_col.noms_colonnes+');' AS [Création Index]
  FROM sys.foreign_keys fk
  JOIN @FK fk_col ON fk_col.fk_id=fk.object_id
  JOIN sys.tables t ON t.object_id=fk.parent_object_id
  JOIN sys.schemas s ON s.schema_id=t.schema_id
  JOIN sys.tables t_r ON t_r.object_id=fk.referenced_object_id
  JOIN sys.schemas s_r ON s_r.schema_id=t_r.schema_id
  LEFT JOIN @Index ind ON ind.object_id=t.object_id
  AND ind.colonnes LIKE fk_col.colonnes+'%' WHERE ind.object_id IS NULL

En l’état, ce script peut en tout cas être passé sur une base de test copie de votre base principale, ne serait-ce que pour évaluer l’espace de stockage supplémentaire nécessaire ainsi que les impacts en termes de performance …