Parmi les index inutiles (et donc pompant des performances) on trouve les index en doublons (même composition de la clef d’index, donc index redondants) et ceux inclus dans un autre index. Comment les détecter ? Faut-il les supprimer ?
Un index en doublon est un index dont la composition de la clef est la même qu’un autre index portant sur la même table. Attention, la composition de l’index doit tenir compte du sens de l’indexation (ASC ou DESC).
Exemple – soit les index de la table T suivants :
- X1 composé de la clef (C1 ASC, C2 ASC, C3 ASC);
- X2 composé de la clef (C1 ASC, C2 ASC, C3 ASC);
- X3 composé de la clef (C1 ASC, C2 DESC, C3 ASC);
Les index X1 et X2 sont des doublons (même clef). L’index X3 n’est pas un doublon, car la colonne C2 est organisée en décroissance dans la clef d’index au contraire des deux autres index.
Un index inclus est un index dont de début de la clef est composé des mêmes éléments qu’un autre index.
Exemple – soit les index de la table T suivants :
- X1 composé de la clef (C1 ASC, C2 ASC, C3 ASC);
- X2 composé de la clef (C1 ASC, C2 ASC);
- X3 composé de la clef (C1 ASC, C3 ASC);
- X4 composé de la clef (C1 ASC, C2 DESC, C3 ASC);
L’index X2 est inclus dans l’index X1. Tous les autres index n’en sont pas.
Voici quelques requêtes MS SQL Server pour trouver les données des index de MS SQL Server :
Liste des index des tables des utilisateurs et des vues indexées :
i.index_id, i.name AS index_name
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.object_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE o."type" IN ('U', 'V')
AND index_id > 0;
Liste des colonnes relatives aux index :
CASE is_descending_key
WHEN '0' THEN 'A'
WHEN '1' THEN 'D'
END AS sens
FROM sys.index_columns
WHERE key_ordinal > 0;
Composition mathématique de la clef d’index (sous forme concaténée des identifiants de colonnes et lettre donnant le sens – D ou, A – dans l’ordre de la clef) :
T0 AS (SELECT object_id, index_id, column_id, key_ordinal,
CASE is_descending_key
WHEN '0' THEN 'A'
WHEN '1' THEN 'D'
END AS sens,
ROW_NUMBER() OVER(PARTITION BY object_id, index_id ORDER BY key_ordinal DESC) AS N
FROM sys.index_columns
WHERE key_ordinal > 0),
T1 AS (SELECT object_id, index_id, column_id, key_ordinal, N,
CAST(column_id AS VARCHAR(MAX)) + sens AS COMPOSITION
FROM T0
WHERE key_ordinal = 1
UNION ALL
SELECT T0.object_id, T0.index_id, T0.column_id, T0.key_ordinal, T0.N,
COMPOSITION + CAST(T0.column_id AS VARCHAR(MAX)) + T0.sens
FROM T0
INNER JOIN T1
ON T0.object_id = T1.object_id
AND T0.index_id = T1.index_id
AND T0.key_ordinal = T1.key_ordinal + 1)
SELECT object_id, index_id, COMPOSITION
FROM T1
WHERE N = 1;
Composition littérale de la clef d’index :
T0 AS (SELECT ic.object_id, index_id, c.column_id, key_ordinal,
CASE is_descending_key
WHEN '0' THEN 'ASC'
WHEN '1' THEN 'DESC'
END AS sens, c.name AS column_name,
ROW_NUMBER() OVER(PARTITION BY ic.object_id, index_id ORDER BY key_ordinal DESC) AS N
FROM sys.index_columns AS ic
INNER JOIN sys.COLUMNS AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE key_ordinal > 0),
T1 AS (SELECT object_id, index_id, column_id, key_ordinal, N,
CAST(column_name AS VARCHAR(MAX)) + ' ' + sens AS COMPOSITION
FROM T0
WHERE key_ordinal = 1
UNION ALL
SELECT T0.object_id, T0.index_id, T0.column_id, T0.key_ordinal, T0.N,
COMPOSITION + ', ' + CAST(T0.column_name AS VARCHAR(MAX)) + ' ' + T0.sens
FROM T0
INNER JOIN T1
ON T0.object_id = T1.object_id
AND T0.index_id = T1.index_id
AND T0.key_ordinal = T1.key_ordinal + 1)
SELECT object_id, index_id, COMPOSITION
FROM T1
WHERE N = 1;
Composition mathématique et littérale de la clef d’index :
T0 AS (SELECT ic.object_id, index_id, c.column_id, key_ordinal,
CASE is_descending_key
WHEN '0' THEN 'ASC'
WHEN '1' THEN 'DESC'
END AS sens, c.name AS column_name,
ROW_NUMBER() OVER(PARTITION BY ic.object_id, index_id ORDER BY key_ordinal DESC) AS N
FROM sys.index_columns AS ic
INNER JOIN sys.COLUMNS AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE key_ordinal > 0),
T1 AS (SELECT object_id, index_id, column_id, key_ordinal, N,
CAST(column_name AS VARCHAR(MAX)) + ' ' + sens AS COMP_LITTERALE,
CAST(column_id AS VARCHAR(MAX)) + SUBSTRING(sens, 1, 1) AS COMP_MATH
FROM T0
WHERE key_ordinal = 1
UNION ALL
SELECT T0.object_id, T0.index_id, T0.column_id, T0.key_ordinal, T0.N,
COMP_LITTERALE + ', ' + CAST(T0.column_name AS VARCHAR(MAX)) + ' ' + T0.sens,
COMP_MATH + CAST(T0.column_id AS VARCHAR(MAX)) + SUBSTRING(T0.sens, 1, 1)
FROM T0
INNER JOIN T1
ON T0.object_id = T1.object_id
AND T0.index_id = T1.index_id
AND T0.key_ordinal = T1.key_ordinal + 1)
SELECT object_id, index_id, COMP_LITTERALE, COMP_MATH
FROM T1
WHERE N = 1;
Détection des index inclus et des index en doublons :
-- sous requête CTE donnant les index avec leurs colonnes
T0 AS (SELECT ic.object_id, index_id, c.column_id, key_ordinal,
CASE is_descending_key
WHEN '0' THEN 'ASC'
WHEN '1' THEN 'DESC'
END AS sens, c.name AS column_name,
ROW_NUMBER() OVER(PARTITION BY ic.object_id, index_id ORDER BY key_ordinal DESC) AS N
FROM sys.index_columns AS ic
INNER JOIN sys.COLUMNS AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE key_ordinal > 0
AND index_id > 0),
-- sous requête CTE récursive composant les clefs des index sous forma algébrique et littérale
T1 AS (SELECT object_id, index_id, column_id, key_ordinal, N,
CAST(column_name AS VARCHAR(MAX)) + ' ' + sens AS COMP_LITTERALE,
CAST(column_id AS VARCHAR(MAX)) + SUBSTRING(sens, 1, 1) AS COMP_MATH,
MAX(N) OVER(PARTITION BY object_id, index_id) AS CMAX
FROM T0
WHERE key_ordinal = 1
UNION ALL
SELECT T0.object_id, T0.index_id, T0.column_id, T0.key_ordinal, T0.N,
COMP_LITTERALE + ', ' + CAST(T0.column_name AS VARCHAR(MAX)) + ' ' + T0.sens,
COMP_MATH + CAST(T0.column_id AS VARCHAR(MAX)) + SUBSTRING(T0.sens, 1, 1),
T1.CMAX
FROM T0
INNER JOIN T1
ON T0.object_id = T1.object_id
AND T0.index_id = T1.index_id
AND T0.key_ordinal = T1.key_ordinal + 1),
-- sous requête CTE de dédoublonnage
T2 AS (SELECT object_id, index_id, COMP_LITTERALE, COMP_MATH, CMAX
FROM T1
WHERE N = 1),
-- sous requête sélectionnant les anomalies
T4 AS (SELECT T2.object_id, T2.index_id,
T3.index_id AS index_id_anomalie,
T2.COMP_LITTERALE AS CLEF_INDEX,
T3.COMP_LITTERALE AS CLEF_INDEX_ANORMAL,
CASE
WHEN T2.COMP_MATH = T3.COMP_MATH
THEN 'DOUBLONS'
WHEN T2.COMP_MATH LIKE T3.COMP_MATH +'%'
THEN 'INCLUS'
END AS ANOMALIE,
ABS(T2.CMAX - T3.CMAX) AS DISTANCE
FROM T2
INNER JOIN T2 AS T3
ON T2.object_id = T3.object_id
AND T2.index_id <> T3.index_id
AND T2.COMP_MATH LIKE T3.COMP_MATH +'%')
-- Requête finale rajoutant les informations manquantes
SELECT T4.*,
s.name +'.' + o.name AS NOM_TABLE,
i1.name AS NOM_INDEX,
i2.name AS NOM_INDEX_ANORMAL
FROM T4
INNER JOIN sys.objects AS o
ON T4.object_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
INNER JOIN sys.indexes AS i1
ON T4.object_id = i1.object_id
AND T4.index_id = i1.index_id
INNER JOIN sys.indexes AS i2
ON T4.object_id = i2.object_id
AND T4.index_id_anomalie = i2.index_id
WHERE o."type" IN ('U', 'V')
ORDER BY NOM_TABLE, NOM_INDEX;
CONSEIL : autant on peut supprimer tous les index en doublon en laissant de préférence et dans l’ordre les index de clef primaire, les index des contraintes d’unicité et ceux de type CLUSTERED, autant il faut étudier s’il est opportun de supprimer tous les index inclus. Pour ma part je pense qu’il faut se limiter à ne retirer que les index inclus dont la distance sémantique est minimale (pas plus de 1, c’est à dire ceux n’ayant qu’une seule colonne en sus).
Détection des index inclus et des index en doublons avec affichage des colonnes incluses (2005) et des filtres (2008) :
-- sous requête CTE donnant les index avec leurs colonnes
T0 AS (SELECT ic.object_id, index_id, c.column_id, key_ordinal,
CASE is_descending_key
WHEN '0' THEN 'ASC'
WHEN '1' THEN 'DESC'
END AS sens, c.name AS column_name,
ROW_NUMBER() OVER(PARTITION BY ic.object_id, index_id ORDER BY key_ordinal DESC) AS N,
is_included_column
FROM sys.index_columns AS ic
INNER JOIN sys.COLUMNS AS c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE key_ordinal > 0
AND index_id > 0),
-- sous requête CTE récursive composant les clefs des index sous forme algébrique et littérale
T1 AS (SELECT object_id, index_id, column_id, key_ordinal, N,
CASE WHEN is_included_column = 0 THEN CAST(column_name AS VARCHAR(MAX)) + ' ' + sens ELSE '' END AS COMP_LITTERALE,
CASE WHEN is_included_column = 0 THEN CAST(column_id AS VARCHAR(MAX)) + SUBSTRING(sens, 1, 1) ELSE '' END AS COMP_MATH,
MAX(N) OVER(PARTITION BY object_id, index_id) AS CMAX,
CASE WHEN is_included_column = 1 THEN CAST(column_name AS VARCHAR(MAX)) ELSE '' END AS COLONNES_INCLUSES
FROM T0
WHERE key_ordinal = 1
UNION ALL
SELECT T0.object_id, T0.index_id, T0.column_id, T0.key_ordinal, T0.N,
COMP_LITTERALE +
CASE WHEN is_included_column = 0 THEN ', ' + CAST(T0.column_name AS VARCHAR(MAX)) + ' ' + T0.sens ELSE '' END,
COMP_MATH +
CASE WHEN is_included_column = 0 THEN CAST(T0.column_id AS VARCHAR(MAX)) + SUBSTRING(T0.sens, 1, 1) ELSE '' END,
T1.CMAX, COLONNES_INCLUSES + CASE WHEN is_included_column = 1 THEN ', ' + CAST(column_name AS VARCHAR(MAX)) ELSE '' END
FROM T0
INNER JOIN T1
ON T0.object_id = T1.object_id
AND T0.index_id = T1.index_id
AND T0.key_ordinal = T1.key_ordinal + 1),
-- sous requête CTE de dédoublonnage
T2 AS (SELECT object_id, index_id, COMP_LITTERALE, COMP_MATH, CMAX, COLONNES_INCLUSES
FROM T1
WHERE N = 1),
-- sous requête sélectionnant les anomalies
T4 AS (SELECT T2.object_id, T2.index_id,
T3.index_id AS index_id_anomalie,
T2.COMP_LITTERALE AS CLEF_INDEX,
T3.COMP_LITTERALE AS CLEF_INDEX_ANORMAL,
T2.COLONNES_INCLUSES, T3.COLONNES_INCLUSES AS COLONNES_INCLUSES_ANORMAL,
CASE
WHEN T2.COMP_MATH = T3.COMP_MATH
THEN 'DOUBLONS'
WHEN T2.COMP_MATH LIKE T3.COMP_MATH +'%'
THEN 'INCLUS'
END AS ANOMALIE,
ABS(T2.CMAX - T3.CMAX) AS DISTANCE
FROM T2
INNER JOIN T2 AS T3
ON T2.object_id = T3.object_id
AND T2.index_id <> T3.index_id
AND T2.COMP_MATH LIKE T3.COMP_MATH +'%')
-- Requête finale rajoutant les informations manquantes
SELECT T4.*,
s.name +'.' + o.name AS NOM_TABLE,
i1.name AS NOM_INDEX,
i2.name AS NOM_INDEX_ANORMAL
--, i1.filter_definition AS FILTRE_INDEX
--, i2.filter_definition AS FILTRE_INDEX_ANORMAL
FROM T4
INNER JOIN sys.objects AS o
ON T4.object_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
INNER JOIN sys.indexes AS i1
ON T4.object_id = i1.object_id
AND T4.index_id = i1.index_id
INNER JOIN sys.indexes AS i2
ON T4.object_id = i2.object_id
AND T4.index_id_anomalie = i2.index_id
WHERE o."type" IN ('U', 'V')
ORDER BY NOM_TABLE, NOM_INDEX;
Pour la version 2008 avec les filtres d’index, il suffit de retirer les marques de commentaire « — » de la clause SELECT de la requête finale :
–, i1.filter_definition AS FILTRE_INDEX
–, i2.filter_definition AS FILTRE_INDEX_ANORMAL
afin de faire figurer le contenu des filtres des deux index en jeu.
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
Pour les index inclus, il y a le cas particulier d’un index inclus avec contrainte d’unicité dont l’index parent autorise les doublons, qui n’est évidemment pas supprimable.
De même, s’il est utilisé dans une contrainte de clef étrangère, il ne devrait pas être considéré comme un index inclus, même si son index parent a aussi une contrainte d’unicité.
A+
J’ai tester le script sous SQL SERVER 2005 SP3 et SQL SERVER 2008 R2 et ça marche Nickel
J’aurais bien voulu avoir une colonne qui affiche la date de mise à jour des statistiques ou la date/heure de dernière utilisation pour chaque chaque index (doublons ou inclus). Ceci va donc permettre de savoir quels index désactivés dans un premier temps. Pour cela j’ai rajouté la colonne « last_user_update » (Heure de la dernière mise à jour de l’index) de la DMV « sys.dm_db_index_usage_stats ».
Par ailleurs, j’avais cette requête qui me permet d’obtenir les index non utilisés. Et j’ai l’impression que cela conduit au même résultat : Agir sur les index inutiles
1) Vous pouvez bloquer la création d’index via un trigger DDL capturant l’ordre CREATE INDEX de la sorte :
ON DATABASE <br />
FOR CREATE_INDEX <br />
AS <br />
DECLARE @MSG NVARCHAR(max) <br />
SET @MSG = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','nvarchar(max)'); <br />
RAISERROR('Création index : %s', 16, 1, @MSG) <br />
GO
2) Quelle est le comportement de l’optimiseur lorsqu’il doit choisir entre 2 index en doublons par exemple entre X1 et X2 ?
Excellent question… Je pense qu’il choisira celui dont les statistiques ont été mise à jour le plus récemment. En tout cas, c’est ce que je ferais !
Une fois les index en doublons,triplons,quadruplons et N-uplons détectés, l’étape suivante est de savoir ce qu’on fait ? personnellement je pense qu’il faut agir avec prudence en :
1.) Désactiver les index qui sont en trop (les doublons) et les index inclus
ALTER INDEX …. DISABLE
2.) Prendre le temps d’analyser l’impact de la désactivation de ces index
3.) Puis décider de la suppression des index concernés
DROP INDEX …..
Une fois ce travail de détection/suppression d’index inutiles fait.
Je me demande s’il serait possible de mettre en place un mécanisme qui empêcherait en amont, la création d’index en doublon ou index inclus ?
La réponse qui me vient à l’esprit c’est NON !
je pense à un trigger, mais un trigger (là je suis entrain de réfléchir à haute voix…) ne peut pas bloquer en amont la création d’index inclus ou en doublons ?!
Mais bon en attendant il faut que je teste d’abord ton script,
qui n’est pas un travail rigolo
Et qui à mon avis est utile lorsqu’il s’agit d’auditer les index et de régler des problèmes de performances.
Une dernière question pour finir :
Quelle est le comportement de l’optimiseur lorsqu’il doit choisir entre 2 index en doublons par exemple entre X1 et X2 ?
# X1 composé de la clef (C1 ASC, C2 ASC, C3 ASC);
# X2 composé de la clef (C1 ASC, C2 ASC, C3 ASC);
L’optimiseur choisira l’index le plus daté (date de création le plus ancien)?
Merci.
A+
Etienne ZINZINDOHOUE