Rechercher les index inutiles

Publié le par

Si les index représentent l’optimisation la plus simple à mettre en place, on souhaite néanmoins conserver le minimum d’entre-eux, car leur maintenance lors de l’exécution de requêtes de modifications de données (INSERT, UPDATE, DELETE) peut être coûteuse, surtout sur des tables volumineuses.
Voyons comment collecter cette information …

La requête suivante retourne tous les index de la base de données en cours pour lesquels seule la maintenance a eu lieu (mais qui n’ont servi aucune requête de type SELECT (IUS.user_lookups + IUS.user_scans + IUS.user_seeks = 0):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-------------------------------
-- Nicolas SOUQUET - 16/11/2010
-------------------------------
SELECT    T.name AS table_name
    , PS.row_count
    , I.name AS index_name
    , dbo.nico(I.object_id, I.index_id) AS index_key
    , IUS.user_lookups + IUS.user_scans + IUS.user_seeks AS total_user_searches
    , IUS.user_updates
    , IUS.system_lookups + IUS.system_scans + IUS.system_seeks AS total_system_searches
    , IUS.system_updates
FROM    sys.dm_db_index_usage_stats AS IUS
INNER JOIN  sys.indexes AS I
      ON IUS.database_id = DB_ID()
      AND IUS.object_id = I.object_id
      AND IUS.index_id = I.index_id
INNER JOIN  sys.tables AS T
      ON I.object_id = T.object_id
INNER JOIN  sys.dm_db_partition_stats AS PS  
      ON PS.object_id = T.object_id
      AND PS.index_id BETWEEN 0 AND 1
LEFT JOIN  sys.objects AS O
      ON O.parent_object_id = I.object_id
      AND O.type = 'PK'
      AND O.name = I.name
WHERE    IUS.user_lookups + IUS.user_scans + IUS.user_seeks = 0
AND    O.object_id IS NULL
ORDER BY  IUS.user_updates DESC --PS.row_count DESC

On exclut les indexes sous-jacents à une clé primaire, puisque leur suppression obligerait celle de la contrainte.

Si l’on souhaite extraire la clé de l’index, il faudra alors créer la petite fonction suivante :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
-------------------------------
-- Nicolas SOUQUET - 16/11/2010
-------------------------------
CREATE FUNCTION dbo.ufn_GetIndexKey
  (
    @_object_id int
    , @_index_id int
  )
  RETURNS varchar(max)
AS
BEGIN  
  DECLARE @index_key varchar(max)
 
  SELECT    @index_key = CASE WHEN @index_key IS NULL THEN C.name ELSE @index_key + + ', ' + C.name END  
  FROM    sys.indexes AS I
  INNER JOIN  sys.index_columns AS IC
        ON I.object_id = IC.object_id
        AND I.index_id = IC.index_id
  INNER JOIN  sys.columns AS C
        ON IC.object_id = C.object_id
        AND IC.column_id = C.column_id
  WHERE    I.object_id = @_object_id
  AND    I.index_id = @_index_id
  ORDER BY  IC.index_column_id
 
  RETURN @index_key
END

Que l’on peut utiliser directement comme suit :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT    T.name AS table_name
    , PS.row_count
    , I.name AS index_name
    , dbo.ufn_GetIndexKey (I.object_id, I.index_id) AS index_key
    , IUS.user_lookups + IUS.user_scans + IUS.user_seeks AS total_user_searches
    , IUS.user_updates
    , IUS.system_lookups + IUS.system_scans + IUS.system_seeks AS total_system_searches
    , IUS.system_updates
FROM    sys.dm_db_index_usage_stats AS IUS
INNER JOIN  sys.indexes AS I
      ON IUS.database_id = DB_ID()
      AND IUS.object_id = I.object_id
      AND IUS.index_id = I.index_id
INNER JOIN  sys.tables AS T
      ON I.object_id = T.object_id
INNER JOIN  sys.dm_db_partition_stats AS PS  
      ON PS.object_id = T.object_id
      AND PS.index_id BETWEEN 0 AND 1
LEFT JOIN  sys.objects AS O
      ON O.parent_object_id = I.object_id
      AND O.type = 'PK'
      AND O.name = I.name
WHERE    IUS.user_lookups + IUS.user_scans + IUS.user_seeks = 0
AND    O.object_id IS NULL
ORDER BY  IUS.user_updates DESC --PS.row_count DESC

Il ne reste alors plus qu’à juger de la pertinence de la suppression de l’index, car leur création est assez coûteuse.
En effet, si on supprime un index et qu’on se rend compte qu’il aurait pu servir des requêtes :

– dans le cas où on dispose d’une édition Standard de SQL Server, on doit recréer l’index et cela bloque la table
– dans le cas où on dispose d’une édition Enterprise de SQL Server, on peut recréer l’index en ligne, mais cela utilise massivement la base de données système TempDB dans laquelle est effectuée le tri, plus l’espace disque nécessaire à la copie de l’index, et enfin le coût de maintenance de deux index au lieu d’un.

Bonne indexation !

Laisser un commentaire