Voici quelques requêtes qui vous permettrons de noter la qualité de vos index, mais aussi d’effacer ceux inutilisés.
1 Рindex faiblement utilis̩s
select OBJECT_NAME(IUS.object_id), I.name, IUS.user_seeks, IUS.user_scans, IUS.user_lookups, IUS.user_updates,
IUS.user_seeks + IUS.user_scans + IUS.user_lookups AS Total_use,
T.user_seeks + T.user_scans + T.user_lookups AS Table_use,
(CAST(IUS.user_seeks + IUS.user_scans + IUS.user_lookups AS FLOAT) /
NULLIF(CAST(T.user_seeks + T.user_scans + T.user_lookups AS FLOAT), 0)) * 100 AS efficacite_percent
from sys.dm_db_index_usage_stats AS IUS
INNER JOIN sys.indexes AS I
ON IUS.object_id = I.object_id
AND IUS.index_id = I.index_id
INNER JOIN sys.dm_db_index_usage_stats AS T
ON IUS.object_id = T.object_id
AND T.index_id IN (0, 1)
where IUS.database_id = DB_ID()
AND I.name NOT IN (SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS)
ORDER BY efficacite_percent
Cette requête mesure l’efficacité d’un index par rapport à l’utilisation de la table et donne un pourcentage. plus ce pourcentage est élevé, et plus cet index est utilisé au détriment de la table.
Les index dont la mesure d’efficacité tend vers zéro sont à supprimer.
A noter les index sous-jacents aux contraintes SQL (PRIMARY KEY et UNIQUE) ne sont pas scrutés.
ATTENTION : cette requête n’a d’intérêt que sur un serveur en exploitation n’ayant pas été récemment arrêté.
2 РCette requ̻te g̩n̬re un script SQL de suppression des index inutiles
select 'DROP INDEX [' + I.name +'] ON [' + SCHEMA_NAME(o.schema_id) + '].['+ OBJECT_NAME(IUS.object_id) +'];'
from sys.dm_db_index_usage_stats AS IUS
INNER JOIN sys.objects AS o
ON IUS.object_id = o.object_id
INNER JOIN sys.indexes AS I
ON IUS.object_id = I.object_id
AND IUS.index_id = I.index_id
where database_id = DB_ID()
AND user_seeks + user_scans + user_lookups = 0
AND I.name NOT IN (SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS)
Cette requête permet de générer un script de suppression des index inutilisés. De la même façon cette requête ne prends pas en compte les index sous-jacents aux contraintes (clefs primaires et unicité).
ATTENTION : cette requête n’a d’intérêt que sur un serveur en exploitation n’ayant pas été récemment arrêté.
3 – Contraintes d’intégrité non indexées
WITH
T_IDX AS (SELECT TABLE_SCHEMA, TABLE_NAME, i.name AS INDEX_NAME,
c.name AS COLUMN_NAME, key_ordinal AS ORDINAL_POSITION
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
INNER JOIN INFORMATION_SCHEMA.TABLES AS T
ON s.name = T.TABLE_SCHEMA AND o.name = TABLE_NAME
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 i.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE is_included_column = 0),
T_CFK AS (SELECT TC.TABLE_SCHEMA, TC.TABLE_NAME, TC.CONSTRAINT_NAME,
COLUMN_NAME, ORDINAL_POSITION
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU
ON TC.TABLE_SCHEMA = KCU.TABLE_SCHEMA
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
WHERE CONSTRAINT_TYPE = 'FOREIGN KEY')
SELECT T_CFK.TABLE_SCHEMA, T_CFK.TABLE_NAME, T_CFK.CONSTRAINT_NAME
FROM T_CFK
EXCEPT
SELECT T_CFK.TABLE_SCHEMA, T_CFK.TABLE_NAME, T_CFK.CONSTRAINT_NAME
FROM T_IDX
INNER JOIN T_CFK
ON T_IDX.TABLE_SCHEMA = T_CFK.TABLE_SCHEMA
AND T_IDX.TABLE_NAME = T_CFK.TABLE_NAME
AND T_IDX.COLUMN_NAME = T_CFK.COLUMN_NAME
WHERE (SELECT COUNT(*)
FROM T_IDX AS X
WHERE X.TABLE_SCHEMA = T_CFK.TABLE_SCHEMA
AND X.TABLE_NAME = T_CFK.TABLE_NAME
AND X.COLUMN_NAME = T_CFK.COLUMN_NAME
GROUP BY INDEX_NAME) = (SELECT COUNT(*)
FROM T_IDX AS K
WHERE K.TABLE_SCHEMA = T_CFK.TABLE_SCHEMA
AND K.TABLE_NAME = T_CFK.TABLE_NAME
AND K.COLUMN_NAME = T_CFK.COLUMN_NAME
GROUP BY INDEX_NAME);
Cette requête donne les contrainte d’intégrité référentielles n’ayant pas d’index correspondant (quelque soit l’ordre des colonnes).
--------
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 * * * * *
L’erreur est juste ! La voila rectifiée… Merci
Merci Frédéric.
Deux petites remarques en ce qui concerne ton script pour le DROP INDEX :
1. Tu as oublié de fermer les crochets après I.name
select ‘DROP INDEX [‘ + I.name +’] ON ‘ + OBJECT_NAME(IUS.object_id) +';’
2. Le nom de la table n’est pas préfixé par le nom du schéma ce qui fait que le script n’a pas pû retrouver correctement la table.