Script SQL d’aide à la gestion des index sur SQL Server

Suite à l’article sur l’indexation d’un DataWarehouse, voici quelques scripts qui vous aideront dans l’administration des index (merci à Frédéric BO pour la majorité des scripts listés ci-dessous)

  • Lister les index d’une base

SELECT   
 T.name AS nom_table,
 I.type_desc AS type_index,
 I.name AS nom_index
FROM   
 sys.tables AS T
  INNER JOIN  sys.indexes AS I ON T.object_id = I.object_id
  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 IC.object_id = C.object_id AND IC.column_id = C.column_id
WHERE   
 is_included_column=0 AND IC.key_ordinal=1
ORDER BY 
 T.name

  • Lister les index en doublon ou inclus
WITH
T0 AS -- sous requête CTE donnant les index avec leurs colonnes
(
 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
),
T1 AS -- sous requête CTE récursive composant les clefs des index sous forme algébrique et littérale
(
 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
),
T2 AS -- sous requête CTE de dédoublonnage
(
 SELECT object_id, index_id, COMP_LITTERALE, COMP_MATH, CMAX, COLONNES_INCLUSES
 FROM   T1
 WHERE  N = 1
),
T4 AS -- sous requête sélectionnant les anomalies
(
 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 ajoutant les informations manquantes       
SELECT
 s.name +'.' + o.name AS NOM_TABLE, 
 CLEF_INDEX_ANORMAL,
 i1.name AS NOM_INDEX, 
 i2.name AS NOM_INDEX_ANORMAL,
 ANOMALIE, DISTANCE
 CLEF_INDEX
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') 
and distance <= 1
ORDER  BY
 s.name +'.' + o.name,
 clef_index

  • Lister les index inutilisés

SELECT
 SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(i.object_id) as tbl,
 i.name as idx,
 ISNULL(user_updates, 0) + ISNULL(system_updates, 0) as updates
FROM
 sys.dm_db_index_usage_stats ius
  RIGHT JOIN
 (
  sys.indexes i
   JOIN sys.tables t ON i.object_id = t.object_id
 )
   ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE
 ISNULL(user_seeks, 0) + ISNULL(system_seeks, 0) = 0
AND i.type_desc = 'NONCLUSTERED' AND i.is_primary_key = 0
AND t.type_desc = 'USER_TABLE'
AND SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(i.object_id) <> 'dbo.sysdiagrams'
ORDER BY tbl

-- génération de code pour supprimer les index inutiles
SELECT
 'DROP INDEX [' + SCHEMA_NAME(t.schema_id) + '].[' + OBJECT_NAME(i.object_id) + '].[' +
 i.name + ']'
FROM
 sys.dm_db_index_usage_stats ius
  RIGHT JOIN
 (
  sys.indexes i
   JOIN sys.tables t ON i.object_id = t.object_id
 )
   ON ius.object_id = i.object_id AND ius.index_id = i.index_id
WHERE
 ISNULL(user_seeks, 0) + ISNULL(system_seeks, 0) = 0
AND i.type_desc = 'NONCLUSTERED'
AND i.is_primary_key = 0
AND t.type_desc = 'USER_TABLE'
AND SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(i.object_id)<>'dbo.sysdiagrams'

  • Utilisation des index sur la base
SELECT
 SCHEMA_NAME(t.schema_id) + '.' + OBJECT_NAME(ius.object_id) as tbl,
 i.name as idx,
 i.type_desc as idxType,
 i.is_unique,
 i.is_primary_key,
 user_seeks,
 user_scans,
 user_lookups,
 user_updates,
 last_user_seek,
 last_user_scan,
 last_user_lookup,
 last_user_update,
 system_seeks,
 system_scans,
 system_lookups,
 system_updates,
 last_system_seek,
 last_system_scan,
 last_system_lookup,
 last_system_update
FROM
 sys.dm_db_index_usage_stats ius
  JOIN sys.indexes i ON ius.object_id = i.object_id AND ius.index_id = i.index_id
  JOIN sys.tables t ON i.object_id = t.object_id
WHERE
 database_id = DB_ID()
AND i.type_desc<>'heap'
AND i.type_desc<>'clustered'
ORDER BY
 tbl
  • Vous pouvez aussi avoir besoin de scripter la (re)création des index d’une table (cela peut être bien utile dans vos packages SSIS) http://wp.me/p2yhHc-8r

Une réflexion sur “Script SQL d’aide à la gestion des index sur SQL Server

  1. mkrief dit :

    En voici un autre qui permet d’avoir la liste des index (top 25) manquants

    SELECT TOP 25
    dm_mid.database_id AS DatabaseID,
    dm_migs.avg_user_impact*(dm_migs.user_seeks+dm_migs.user_scans) Avg_Estimated_Impact,
    dm_migs.last_user_seek AS Last_User_Seek,
    OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) AS [TableName],
    ‘CREATE INDEX [IX_’ + OBJECT_NAME(dm_mid.OBJECT_ID,dm_mid.database_id) + ‘_’
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.equality_columns, »),’, ‘,’_’),'[‘, »),’]’, ») +
    CASE
    WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ‘_’
    ELSE  »
    END
    + REPLACE(REPLACE(REPLACE(ISNULL(dm_mid.inequality_columns, »),’, ‘,’_’),'[‘, »),’]’, »)
    + ‘]’
    + ‘ ON ‘ + dm_mid.statement
    + ‘ (‘ + ISNULL (dm_mid.equality_columns, »)
    + CASE WHEN dm_mid.equality_columns IS NOT NULL AND dm_mid.inequality_columns IS NOT NULL THEN ‘,’ ELSE
     » END
    + ISNULL (dm_mid.inequality_columns,  »)
    + ‘)’
    + ISNULL (‘ INCLUDE (‘ + dm_mid.included_columns + ‘)’,  ») AS Create_Statement
    FROM sys.dm_db_missing_index_groups dm_mig
    INNER JOIN sys.dm_db_missing_index_group_stats dm_migs
    ON dm_migs.group_handle = dm_mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details dm_mid
    ON dm_mig.index_handle = dm_mid.index_handle
    WHERE dm_mid.database_ID = DB_ID()
    ORDER BY Avg_Estimated_Impact DESC

    ce script donne aussi pour chacun d’eux leur script de création.

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s