Détecter les index obèses

Voici en complément des 20 requêtes pour auditer la qualité de votre modèle de données (voir 20 requêtes pour auditer la qualité de votre modèle de données) une requête destinée à déceler les index obèses (c’est à dire, ceux ayant trop de colonnes dans leur définition.

Voici la requête :

WITH
T0 AS
(
SELECT o.object_id, i.index_id,
       s.name AS TABLE_SCHEMA,
       o.name AS TABLE_NAME,
       i.name AS INDEX_NAME,
       is_included_column,
       COUNT(*) AS NOMBRE
FROM   sys.objects AS o
       JOIN sys.schemas AS s
            ON o.schema_id = s.schema_id
       JOIN sys.indexes AS i
            ON o.object_id = i.object_id
       JOIN sys.index_columns AS ic
            ON i.object_id = ic.object_id
            AND i.index_id = ic.index_id
WHERE  i.index_id > 0
  AND  is_ms_shipped = 0
GROUP  BY o.object_id, i.index_id, s.name, o.name, i.name, is_included_column
)
SELECT T1.TABLE_SCHEMA,
       T1.TABLE_NAME,
       T1.INDEX_NAME,
       T1.NOMBRE + T0.NOMBRE AS NOMBRE_COLONNES,
       T0.NOMBRE AS NOMBRE_COLONNES_CLEFS,
       T1.NOMBRE AS NOMBRE_COLONNES_INCLUSES
FROM   T0
       JOIN T0 AS T1
            ON T0.object_id = T1.object_id
            AND T0.index_id = T1.index_id
WHERE  T0.is_included_column = 1
  AND  T1.is_included_column = 0
  AND  T1.NOMBRE + T0.NOMBRE > 10 --> seuil limite du nombre de colonnes dans l'index à ne pas dépasser

Le seuil étant fixé à 10 colonnes, vous pouvez être plus sévère encore ou plus laxiste à votre choix !!!

Frédéric Brouard, alias SQLpro, ARCHITECTE DE DONNÉES
Expert  S.G.B.D  relationnelles   et   langage  S.Q.L
Moste  Valuable  Professionnal  Microsoft  SQL Server
Société SQLspot  :  modélisation, conseil, formation,
optimisation,  audit,  tuning,  administration  SGBDR
Enseignant: CNAM PACA, ISEN Toulon, CESI Aix en Prov.
Développez et administrez pour la performance avec SQL Server 2014

Développez et administrez pour la performance avec SQL Server 2014

L’entreprise SQL Spot
Le site web sur le SQL et les SGBDR

MVP Microsoft SQL
Server

Laisser un commentaire