Une requête pour connaître l’utilisation des index (et leur définition) : la DMV sys.dm_db_index_usage_stats

Voici une requête qui retourne l’utilisation des index avec leur définition, sur un schéma et/ou une table ou vue indexée et/ou une base de données :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
-------------------------------
-- Nicolas Souquet - 04/06/2012
-------------------------------
 
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
 
DECLARE  @schema_name sysname = 'dbo'
  , @table_or_view_name sysname --= 'maTable' -- si NULL, étudie tous les index de toutes les table du schéma
  , @index_name sysname = NULL -- si NULL, étudie tous les index de la table
  , @separator varchar(2) = ', '
----------------------------------------------------------------------------------------------------------------------
SELECT    DISTINCT S.name + '.' + O.name AS schema_table_name
    , I.name AS index_name
    , PS.row_count
    , I.type_desc
    , LEFT(KC.key_column_list, LEN(KC.key_column_list) - 2) AS key_column_list
    , LEFT(KCI.included_key_column_list, LEN(KCI.included_key_column_list) - 1) AS included_key_column_list
    , REPLACE(REPLACE(REPLACE(REPLACE(I.filter_definition, '[', ''), ']', ''), '(', ''), ')', '') AS filter_definition
    , PS.used_page_count AS pages_count
    , STATS_DATE(O.object_id, I.index_id) AS last_stat_update
    , IUS.user_seeks
    , IUS.user_scans
    , IUS.user_lookups
    , (IUS.user_seeks + IUS.user_scans + IUS.user_lookups) AS total_user_searches
    , IUS.last_user_seek
    , IUS.last_user_scan  
    , IUS.last_user_lookup
    , LS.max_last_search_date
    , PS.used_page_count / 8 AS index_size_kb
FROM    sys.schemas AS S
INNER JOIN  sys.objects AS O
      ON S.schema_id = O.schema_id
INNER JOIN  sys.indexes AS I  
      ON O.object_id = I.object_id
INNER JOIN  sys.index_columns AS IC
      ON IC.object_id = I.object_id
      AND IC.index_id = I.index_id
INNER JOIN  sys.columns AS C
      ON IC.object_id = C.object_id
      AND IC.column_id = C.column_id
LEFT JOIN  sys.dm_db_index_usage_stats AS IUS
      ON IUS.object_id = I.object_id
      AND IUS.index_id = I.index_id
      AND IUS.database_id = DB_ID()
INNER JOIN  sys.dm_db_partition_stats AS PS
      ON PS.object_id = I.object_id
      AND PS.index_id = I.index_id
CROSS APPLY  (
      SELECT    CS.name + @separator
      FROM    sys.columns AS CS
      INNER JOIN  sys.index_columns AS ICS
            ON CS.object_id = ICS.object_id
            AND CS.column_id = ICS.column_id
      WHERE    IC.object_id = ICS.object_id
      AND    IC.index_id = ICS.index_id
      AND    ICS.is_included_column = 0
      ORDER BY  ICS.index_column_id
      FOR XML PATH ('')
    ) AS KC (key_column_list)
OUTER APPLY  (
      SELECT    CSI.name + @separator
      FROM    sys.columns AS CSI
      INNER JOIN  sys.index_columns AS ICSI
            ON CSI.object_id = ICSI.object_id
            AND CSI.column_id = ICSI.column_id
      WHERE    IC.object_id = ICSI.object_id
      AND    IC.index_id = ICSI.index_id
      AND    ICSI.is_included_column = 1
      ORDER BY  ICSI.index_column_id
      FOR XML PATH ('')
    ) AS KCI (included_key_column_list)
OUTER APPLY  (
      SELECT  MAX(M.last_search_date)
      FROM  (
            SELECT  LU_USK.last_user_seek
            FROM  sys.dm_db_index_usage_stats AS LU_USK
            WHERE  IUS.database_id = LU_USK.database_id
            AND  IUS.object_id = LU_USK.object_id
            AND  IUS.index_id = LU_USK.index_id
          UNION ALL
            SELECT  LU_USC.last_user_scan
            FROM  sys.dm_db_index_usage_stats AS LU_USC
            WHERE  IUS.database_id = LU_USC.database_id
            AND  IUS.object_id = LU_USC.object_id
            AND  IUS.index_id = LU_USC.index_id
          UNION ALL
            SELECT  LU_LK.last_user_lookup
            FROM  sys.dm_db_index_usage_stats AS LU_LK
            WHERE  IUS.database_id = LU_LK.database_id
            AND  IUS.object_id = LU_LK.object_id
            AND  IUS.index_id = LU_LK.index_id
        ) AS M (last_search_date)
    ) AS LS (max_last_search_date)
WHERE    (@schema_name IS NULL OR S.name = @schema_name)
AND    (@table_or_view_name IS NULL OR O.name = @table_or_view_name)
AND    (@index_name IS NULL OR I.name = @index_name)
ORDER BY  I.type_desc

Cette vue accumule les statistiques tant que le service SQL Server est démarré; un redémarrage du service ou du serveur purge les statistiques exposées par cette DMV.
Si elle permet de détecter les index non-utilisés et donc à supprimer, vérifiez depuis combien de temps le service SQL Server est actif :)

Bonne utilisation de vos index !

ElSüket ;)

Laisser un commentaire