Quelques requêtes SQL Server pour connaître la qualité de vos index

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  * * * * *

MVP Microsoft SQL Server

2 réflexions au sujet de « Quelques requêtes SQL Server pour connaître la qualité de vos index »

  1. Avatar de cavo789cavo789

    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.

Laisser un commentaire