SQL Server - Trop d’index tue l’index : supprimez les index inutiles !

Frédéric BROUARD

Pour réagir au contenu de cet article, un espace de dialogue vous est proposé sur le forum. Commentez

- Titre : SQL Server - Trop d’index tue l’index : supprimez les index inutiles !

- Auteur : Frédéric BROUARD

- Parution : 6 octobre 2022

- Public visé :

- Licence : Copyright ® 2022 Frédéric BROUARD. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Sommaire

- I. L’auteur

- II. Introduction

- III. Trop d’index tue l’index

- IV. Trouver les index inutiles

- V. Inutile, mais sans danger !

- VI. La profondeur de scrutation

- VII. Finalement

- VIII. Remerciements Developpez.com

I - L’auteur

Blog :
https://blog.developpez.com/sqlpro
Le site :
https://sqlpro.developpez.com

II - Introduction

Je découvre souvent beaucoup trop d’index dans les bases de données des clients que j’audite… Or, tout index coûte en mise à jour, et chaque mise à jour étant « bloquante », cela pèse sur les performances. Il faut donc supprimer ou désactiver les index inutiles. Cet article propose une méthode pour analyser base, par base les index à supprimer… Attention, il existe de nombreux pièges dans lesquels il ne faut pas sombrer… D’où l’intérêt d’un tel article !

Un index n’est autre qu’un moyen destiné à accélérer certaines recherches. Une définition stricte de l’index est la suivante :

structure de données redondantes
spécialement organisée
pour accélérer certaines recherches

C’est une structure de données qui diffère de la table. Dans une table, les lignes sont « désordonnées », elles y sont mises où il y a de la place sans aucun ordre particulier. Dans un index, les informations sont placées en fonction de l’algorithme choisi pour l’index (BTree, Hash, Columnar…).

Redondante, car il y a copie des données pour construire l’index. Un index prend donc de la place et cette place va immanquablement rejaillir sur la maintenance : défragmentation, recalcul des statistiques, sauvegarde, restauration, duplication dans la cadre d’une haute disponibilité…

Spécialement organisées, car en sus de la copie et de l’arrangement des informations, on y trouve des éléments complémentaires pour aider à rechercher rapidement l’information. La plupart du temps, il s’agit d’arbres de recherche (donc des informations supplémentaires qui aiguillent vers la donnée finale recherchée) ou bien de calcul de transformation (par exemple hachage).

Accélérer certaines recherches : oui, car toutes les recherches avec tous les opérateurs disponibles du SQL (<, <=, <>, >, >=, =, IN, BETWEEN, LIKE…) ne peuvent pas bénéficier de l’accélération liée à l’index. C’est la notion de « cherchabilité » (en anglais « sargable » néologisme venant de Search ARGument ABLE) qui signifie « apte à utiliser l’argument de recherche ». Par exemple aucun index ne permet de rechercher rapidement une forme comme :

COLONNE <> valeur

En effet, une telle recherche est une « non » recherche en ce sens que l’on veut tout sauf telle valeur…

III - Trop d’index tue l’index

Les index sont utilisés automatiquement par l’optimiseur de SQL Server qui décide en analysant des statistiques si l’utilisation d’un index est préférable à la lecture de la table (parfois la lecture de la table est gagnante par rapport à l’utilisation de l’index, tout dépend des conditions de filtrage). En présence de nombreux index, l’analyse consiste à étudier chacun des index candidats et d’évaluer quel sera le meilleur gain pour chacun d’eux. Un index est candidat si au moins l’une des colonnes composant l’index est visée par au moins l’une des clauses WHERE, HAVING ou ON (du JOIN), voire parfois pour les clauses GROUP BY et ORDER BY.

Or, trouver un plan d’exécution optimal, dont la recherche est dévolue à l’optimiseur, est un sport de haut niveau qui requiert « intelligence » et rapidité… Intelligence, car il faut évaluer de nombreuses conditions complexes avec des mesures de coût très particulières (nombre estimé de lignes scrutées, nombre estimé de lignes rapportées, tailles des lignes, coût d’exécution de chaque opération…) et rapidité, car il ne servirait à rien de mettre plus d’une minute pour trouver un plan d’exécution parfait, si une solution triviale d’une banalité effrayante mettait moins de 10 secondes pour exécuter la requête…

Il y a donc un temps imparti pour que l’optimiseur de SQL Server trouve un plan, qui ne sera pas forcément parfait, mais déjà bien affiné… Cependant, en présence de trop nombreux index, l’optimiseur va perdre du temps à analyser trop de combinaisons et peut-être ne pas trouver ce fameux plan optimal !

IV - Trouver les index inutiles

SQL Server permet, à l’aide de la DMV sys.dm_db_index_usage_stats, de trouver les index qui n’ont fait l’objet d’aucun accès en lecture. C’est une vue dont le périmètre est tout le serveur (elle scrute les données à travers toutes les bases).

Les mesures d’accès sont rassemblées en trois colonnes :

- user_seeks : recherche d’une valeur dans l’index ;

- user_lookups : recherche de valeurs multiples dans l’index ;

- user_scans : lecture séquentielle des lignes de l’index.

Lorsque ces trois colonnes sont à 0, l’index n’est jamais accédé en lecture. Il semble donc inutile…

V - Inutile, mais sans danger !

Mais… il n’est pas utile et même dangereux de supprimer tous les index inutiles ! En effet, lorsqu’une table est vide, ces index sont vides aussi. Problème, cette table actuellement vide peut se remplir un jour ou l’autre… Et sans doute y aura-t-il besoin de ces index à ce moment. Or, en aucun cas ces index ne constituent un problème, car un index vide ne coûte rien !

Il existe d’autres cas pour limiter la suppression des index inutiles :

l’index hypothétique : c’est en fait une indication d’index sans aucun stockage des valeurs de l’index, mais doté de statistiques pour aider l’optimiseur. Le supprimer serait contre-productif pour l’optimisation des requêtes.

l’index unique ou de clef primaire : à l’évidence il ne faut pas modifier la structure de la base. L’éditeur de logiciel qui en serait victime aurait parfaitement raison de râler, car les données pourraient être logiquement corrompues.

La table : les index d’identifiant 0 et 1 sont en fait la table ! Inutile de dire que supprimer une table de la base serait encore plus dangereux…

VI - La profondeur de scrutation

Enfin, un élément déterminant de cette analyse est la profondeur de scrutation des statistiques fournies par les DMV… En effet, la vue de gestion sys.dm_db_index_usage_stats, comme toutes ses petites sœurs, est vidée à chaque démarrage du service SQL Server de l’instance. Si vous l’utilisez juste après un redémarrage, vous constaterez que la plupart des index sont inutiles… Tout simplement par ce que pas encore utilisés !

Il faut donc que l’instance SQL Server ait accumulé suffisamment de statistiques… Or, en dehors des opérations effectuées au fil de l’eau, certains traitements sont journaliers, d’autres hebdomadaires, quelques-uns mensuels, parfois trimestriels et enfin annuels (clôture de bilan par exemple). Pour ma part, je considère que la rémanence des informations de ces DMV doit au moins avoir 31 jours de recul… En deçà, danger !

On peut se servir de la requête suivante pour connaître de la profondeur d’analyse en jours après le dernier redémarrage de l’instance :

SELECT DATEDIFF(day, sqlserver_start_time, GETDATE()) AS RUN_DAYS FROM sys.dm_os_sys_info

VII - Finalement

Toutes ces considérations se résument dans la requête suivante :

1. SELECT -- which table :

2. s.name AS TABLE_SCHEMA, o.name AS TABLE_NAME,

3. -- which index, how many UPDATES

4. i.name AS INDEX_NAME, ius.user_updates,

5. -- stats on recoverable pages for index

6. (SELECT SUM(used_page_count)

7. FROM   sys.dm_db_partition_stats AS ps

8. WHERE  ps.object_id = ius.object_id

9. AND  ius.index_id = ps.index_id)

10. AS INDEX_USED_PAGE,

11. -- stats on recoverable pages for the all DB

12. SUM((SELECT SUM(reserved_page_count) AS P

13. FROM   sys.dm_db_partition_stats AS ps

14. WHERE  ps.object_id = ius.object_id

15. AND  ius.index_id = ps.index_id)) OVER()

16. AS DB_USED_PAGES,

17. -- build the ALTER INDEX SQL command

18. N'ALTER INDEX [' + i.name + '] ON [' + s.name

19. + '].[' + o.name + '] DISABLE;' AS SQL_INDEX_DISABLE,

20. -- build the ADROP INDEX SQL command

21. N'DROP INDEX [' + i.name + '] ON ['

22. + s.name + '].[' + o.name + '];' AS SQL_INDEX_DROP

23. FROM   sys.dm_db_index_usage_stats AS ius

24. JOIN sys.objects AS o

25. ON ius.object_id = o.object_id

26. JOIN sys.schemas AS s

27. ON o.schema_id = s.schema_id

28. JOIN sys.indexes AS i

29. ON ius.object_id = i.object_id AND

30. ius.index_id = i.index_id

31. WHERE  ius.index_id > 1             -- ignore tables

32. AND  i.is_hypothetical = 0        -- ignore hypothetical indexes

33. AND  i.is_primary_key = 0         -- ignore PK

34. AND  i.is_unique = 0              -- ignore AK

35. AND  i.is_unique_constraint = 0   -- ignore unique indexes

36. AND  user_seeks = 0               -- index has no seek

37. AND  user_lookups = 0             -- index has no lookup

38. AND  user_scans = 0               -- index has no scan

39. AND  database_id = DB_ID()        -- only for current DB

40. -- ignore empty index (empty table ?)

41. AND  EXISTS(SELECT *

42. FROM   sys.dm_db_partition_stats AS ps

43. WHERE  ps.object_id = ius.object_id

44. AND  ius.index_id = ps.index_id

45. AND  row_count > 0)

46. -- ignore the diagnosis if statistics persistence < 31 days

47. AND  EXISTS(SELECT *

48. FROM   sys.dm_os_sys_info

49. WHERE  sqlserver_start_time

50. <= DATEADD(day, -31, GETDATE()))

51. ORDER BY 1, 2, 3;

VIII - Remerciements Developpez.com

Nous tenons à remercier escartefigue pour la relecture orthographique de cet article, et Malick pour la mise au gabarit.