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. Or 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 a 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 ou il y a de la place sans aucun ordre particulier. Dans un index, les informations sont placées en fonction de l’algorithme choisit 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ées finale recherchée) ou bien de calcul de transformation (par exemple hachage) .
Accélérer certains recherches : oui, car toutes les recherches avec tous les opérateurs disponible du SQL (<, <=, <>, >, >=, =, IN, BETWEEN, LIKE…) ne peuvent 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…
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érables à 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 une des colonnes composant l’index est visé par au moins un 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 le rôle est dévolu à l’optimiseur, est un sport de haut niveau qui requiert « intelligence » et rapidité… Intelligence car il faut évaluer de nombreuses conditions complexes avec des métriques 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 mettais moins de 10 secondes pour exécuter la requête…
Il y a donc un temps impartit pour que l’optimiseur de SQL Server trouve un plan, qui ne sera pas forcément parfait, mais déjà bien affiné… Or en présence de trop nombreux index, l’optimiseur va perdre du temps à analyser trop de combinaison et peut être ne pas trouver ce fameux plan optimal!
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 scope est le serveur (elle scrute les données à travers toutes les bases).
Les mesures d’accès sont rassemblées en 3 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…
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 constitue 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 clé 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…
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ée suffisamment de statistiques… Or, en dehors des opérations effectuées au fil de l’eau, certains traitements sont journaliers, d’autres hebdomadaire, 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 jour suite au dernier redémarrage de l’instance :
SELECT DATEDIFF(day, sqlserver_start_time, GETDATE()) AS RUN_DAYS FROM sys.dm_os_sys_info
Finalement
Toutes ces considérations se résument dans la requête suivante :
SELECT -- which table :
s.name AS TABLE_SCHEMA, o.name AS TABLE_NAME,
-- which index, how many UPDATES
i.name AS INDEX_NAME, ius.user_updates,
-- stats on recoverable pages for index
(SELECT SUM(used_page_count)
FROM sys.dm_db_partition_stats AS ps
WHERE ps.object_id = ius.object_id
AND ius.index_id = ps.index_id)
AS INDEX_USED_PAGE,
-- stats on recoverable pages for the all DB
SUM((SELECT SUM(reserved_page_count) AS P
FROM sys.dm_db_partition_stats AS ps
WHERE ps.object_id = ius.object_id
AND ius.index_id = ps.index_id)) OVER()
AS DB_USED_PAGES,
-- build the ALTER INDEX SQL command
N'ALTER INDEX [' + i.name + '] ON [' + s.name
+ '].[' + o.name + '] DISABLE;' AS SQL_INDEX_DISABLE,
-- build the ADROP INDEX SQL command
N'DROP INDEX [' + i.name + '] ON ['
+ s.name + '].[' + o.name + '];' AS SQL_INDEX_DROP
FROM sys.dm_db_index_usage_stats AS ius
JOIN sys.objects AS o
ON ius.object_id = o.object_id
JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
JOIN sys.indexes AS i
ON ius.object_id = i.object_id AND
ius.index_id = i.index_id
WHERE ius.index_id > 1 -- ignore tables
AND i.is_hypothetical = 0 -- ignore hypothetical indexes
AND i.is_primary_key = 0 -- ignore PK
AND i.is_unique = 0 -- ignore AK
AND i.is_unique_constraint = 0 -- ignore unique indexes
AND user_seeks = 0 -- index has no seek
AND user_lookups = 0 -- index has no lookup
AND user_scans = 0 -- index has no scan
AND database_id = DB_ID() -- only for current DB
-- ignore empty index (empty table ?)
AND EXISTS(SELECT *
FROM sys.dm_db_partition_stats AS ps
WHERE ps.object_id = ius.object_id
AND ius.index_id = ps.index_id
AND row_count > 0)
-- ignore the diagnosis if statistics persistence < 31 days
AND EXISTS(SELECT *
FROM sys.dm_os_sys_info
WHERE sqlserver_start_time
<= DATEADD(day, -31, GETDATE()))
ORDER BY 1, 2, 3;
Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
* Le site sur les SGBD relationnels et le SQL : https://sqlpro.developpez.com *
* le blog SQL, SQL Server, SGBDR... sur : https://blog.developpez.com/sqlpro/ *
* Expert Microsoft SQL Server, MVP (Most valuable Professional) depuis 14 ans *
* Entreprise SQL SPOT : modélisation, conseil, audit, optimisation, formation *
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *