Etude de table : retourner les caractéristiques des statistiques et index d’une table (ou vue indexée)

Voici un script qui retourne :

– l’en-tête des statistiques d’une table
– la liste des colonnes qui participent à la statistique, ou des colonnes clé de l’index (incluses et filtrées)
– le vecteur de la statistique, qui permet de connaître sa sélectivité
– optionnellement, le niveau de fragmentation des index
– le nombre d’utilisation des index
– la date de dernière mise à jour de chaque statistique scrutée par le script

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
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
-----------------------------------------
-- 28/05/2012 - Copyright Nicolas Souquet
-----------------------------------------
 
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
 
------------------------------------------------------------------------------------------------------------------------------
DECLARE  @schema_name sysname = 'dbo'
  , @table_or_indexed_view_name sysname = 'maTable'
  , @statistic_name sysname = NULL -- NULL effectue l'étude pour toutes les statistiques ou index de la table
  ---
  , @index_study bit = 1 -- mettre à 1 pour indiquer si l'on souhaite regarder la fragmentation des index, 0 sinon
  , @index_scan_level_mode varchar(8) = 'LIMITED' -- scan level mode (DETAILED, SAMPLED, LIMITED)  
  ---
  , @separator varchar(2) = ', '
------------------------------------------------------------------------------------------------------------------------------
-- NE RIEN CHANGER APRÈS CETTE LIGNE
------------------------------------------------------------------------------------------------------------------------------
IF (SELECT OBJECT_ID('TEMPDB.dbo.#STUDY')) IS NOT NULL
BEGIN
  DROP TABLE #STUDY
END
 
------------------------------------------------------------------------------
-- Vérifie que la table ou vue indexée existe dans la base de données en cours
------------------------------------------------------------------------------
DECLARE @object_type char(1)
 
SELECT    @object_type = O.type
FROM    sys.schemas AS S
INNER JOIN  sys.objects AS O ON S.schema_id = O.schema_id
WHERE    S.name = @schema_name
AND    O.name = @table_or_indexed_view_name
AND    O.type IN ('U', 'V') -- table ou vue (indexée)
 
IF @object_type IS NULL
BEGIN
  RAISERROR('La table ou vue indexée ''%s.%s'' n''existe pas !', 16, 1, @schema_name, @table_or_indexed_view_name)
  RETURN
END
 
-- Seules les vues indexées on des statistiques
IF @object_type = 'V'
AND NOT EXISTS
(
  SELECT    *
  FROM    sys.schemas AS S
  INNER JOIN  sys.views AS V ON S.schema_id = V.schema_id
  INNER JOIN  sys.indexes AS I ON V.object_id = I.object_id
  WHERE    S.name = @schema_name
  AND    V.name = @table_or_indexed_view_name
)
BEGIN
  RAISERROR('The %s.%s view is not indexed !', 16, 1, @schema_name, @table_or_indexed_view_name)
  RETURN
END
 
DECLARE @qualified_table_or_indexed_view_name nvarchar(256) = @schema_name + '.' + @table_or_indexed_view_name
 
--------------------------------------------------
-- Collecte des statistiques de colonne et d'index
--------------------------------------------------
DECLARE  @stat_header TABLE
(
  stat_name varchar(256)
  , updated varchar(32)
  , row_count bigint
  , rows_sampled bigint
  , steps int
  , density float
  , avg_key_length float
  , string_index char(3)
  , filter_expression varchar(1024)  
  , unfiltered_rows bigint
)
 
DECLARE @density_vector TABLE
(
  all_density float
  , average_length float
  , column_list varchar(1024)
)
 
DECLARE @stat_name_density_vector TABLE
(
  stat_name varchar(256)
  , all_density float
)
 
DECLARE @stat_name varchar(256) = ' '
  , @object_id int
  , @sql_stat_header nvarchar(256) = 'DBCC SHOW_STATISTICS(@TABLE@, @STATS@) WITH STAT_HEADER, NO_INFOMSGS'
  , @sql_density_vector nvarchar(256) = 'DBCC SHOW_STATISTICS(@TABLE@, @STATS@) WITH DENSITY_VECTOR, NO_INFOMSGS'
  , @exec_string nvarchar(512)
 
WHILE DATALENGTH(@stat_name) > 0
BEGIN
  SET @stat_name = NULL
 
  SELECT    TOP(1) @stat_name = QUOTENAME(D.name)
  FROM    sys.stats AS D
  INNER JOIN  sys.objects AS O ON D.object_id = O.object_id
  INNER JOIN  sys.schemas AS S ON O.schema_id = S.schema_id
  LEFT JOIN  @stat_header AS SH ON D.name = SH.stat_name
  WHERE    S.name = @schema_name
  AND    O.name = @table_or_indexed_view_name
  AND    SH.stat_name IS NULL
  AND    (@statistic_name IS NULL OR D.name = @statistic_name)
 
  -- Statistics header
  SELECT @exec_string = REPLACE(REPLACE(@sql_stat_header, '@STATS@', @stat_name), '@TABLE@', '''' + @schema_name + '.' + @table_or_indexed_view_name  + '''')
 
  INSERT  @stat_header
  EXEC  (@exec_string)
 
  -- Density vector
  SELECT @exec_string = REPLACE(REPLACE(@sql_density_vector, '@STATS@', @stat_name), '@TABLE@', '''' + @schema_name + '.' + @table_or_indexed_view_name  + '''')
 
  INSERT  @density_vector (all_density, average_length, column_list)
  EXEC  (@exec_string)
 
  INSERT  INTO @stat_name_density_vector
  SELECT  TOP 1 @stat_name
    , all_density
  FROM  @density_vector
  ORDER  BY average_length
 
  DELETE  FROM @density_vector
END
-----------------------------------------------------------------------------------------------------------------------
;WITH
  INDEX_CTE AS
  (
    SELECT    DISTINCT I.name AS index_name
        , I.index_id
        , PS.partition_number
        , PS.row_count
        , I.type_desc
        , I.is_unique
        , I.is_unique_constraint
        , I.is_primary_key
        , LEFT(KC.key_column_list, LEN(KC.key_column_list) - LEN(@separator)) AS key_column_list
        , LEFT(KCI.included_column_list, LEN(KCI.included_column_list) - 1) AS included_column_list
        , REPLACE(REPLACE(REPLACE(REPLACE(I.filter_definition, '[', ''), ']', ''), '(', ''), ')', '') AS filter_definition
        , 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.user_updates
        , LS.max_last_search_date
        , I.is_disabled  
        , (PS.reserved_page_count * 8192) / 1024 AS index_size_kb
        , IPS.index_level
        , IPS.index_depth
        , CAST(IPS.avg_fragmentation_in_percent AS decimal(5, 2)) AS avg_frag_pct
        , I.fill_factor
        , IPS.forwarded_record_count
    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()
    LEFT JOIN  (
          SELECT  object_id
            , index_id
            , database_id
            , index_level
            , index_depth
            , avg_fragmentation_in_percent
            , forwarded_record_count
          FROM  sys.dm_db_index_physical_stats
            (
              DB_ID() -- database
              , OBJECT_ID(@table_or_indexed_view_name) -- all tables
              , NULL -- all indexes
              , NULL -- all partitions
              , @index_scan_level_mode -- scan level mode (DETAILED, SAMPLED, LIMITED)
            )
          WHERE  @index_study = 1
        ) AS IPS
        ON IPS.object_id = I.object_id
        AND IPS.index_id = I.index_id
        AND IPS.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 + CASE ICS.is_descending_key WHEN 1 THEN ' DESC' ELSE '' END + @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_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_indexed_view_name IS NULL OR O.name = @table_or_indexed_view_name)
    AND    (@stat_name IS NULL OR I.name = @stat_name)
  )
SELECT    COALESCE(C.type_desc, 'STAT') AS type_desc
    , CAST(SH.density AS decimal(7,6)) AS header
    , SNDV.all_density AS vector
    , C.row_count
    , SH.stat_name
    , COALESCE(C.key_column_list, LEFT(SC.stat_column_list, LEN(SC.stat_column_list) - 1)) AS stat_column_list
    , C.included_column_list
    , C.index_id
    , C.partition_number
    , C.is_disabled
    , C.is_unique AS is_uq
    , C.is_unique_constraint AS is_uqc
    , C.is_primary_key AS is_PK
    , REPLACE(REPLACE(REPLACE(REPLACE(SH.filter_expression, '[', ''), ']', ''), '(', ''), ')', '') AS filter_definition
    , SH.row_count AS stat_row_count
    , COALESCE(C.row_count, SH.row_count) AS real_row_count
    , SI.rowmodctr
    , SH.rows_sampled
    , C.index_size_kb
    , STATS_DATE(D.object_id, D.stats_id) AS last_stat_update
    , C.user_seeks
    , C.user_scans
    , C.user_lookups    
    , C.total_user_searches
    , C.user_updates
    , C.max_last_search_date
    , C.index_depth
    , C.avg_frag_pct
    , C.index_level
    , CASE
      WHEN COALESCE(C.type_desc, 'STAT') = 'STAT' THEN NULL
      WHEN C.index_level = 0 THEN 'LEAF'
      WHEN C.index_depth - C.index_level > 1 THEN 'INTERMEDIATE'
      ELSE 'ROOT'
    END AS index_level_type
    , C.forwarded_record_count
INTO    #STUDY
FROM    @stat_header AS SH
INNER JOIN  @stat_name_density_vector AS SNDV
      ON QUOTENAME(SH.stat_name) = SNDV.stat_name
LEFT JOIN  INDEX_CTE AS C
      ON SH.stat_name = C.index_name
INNER JOIN  sys.stats AS D
      ON D.object_id = OBJECT_ID(@schema_name + '.' + @table_or_indexed_view_name)
      AND D.name = SH.stat_name
INNER JOIN  sys.sysindexes AS SI
      ON SI.id = D.object_id
      AND SI.indid = D.stats_id
CROSS APPLY  (
      SELECT    C.name + ','
      FROM    sys.stats AS S
      INNER JOIN  sys.stats_columns AS SC
            ON S.object_id = SC.object_id
            AND S.stats_id = SC.stats_id
      INNER JOIN  sys.columns AS C
            ON C.object_id = SC.object_id
            AND C.column_id = SC.column_id
      WHERE    SH.stat_name = S.name
      ORDER BY  SC.stats_column_id
      FOR XML PATH ('')
    ) AS SC (stat_column_list)
ORDER BY  stat_name
 
SELECT  *
FROM  #STUDY
ORDER  BY type_desc

Bonne étude de table !

17 réflexions au sujet de « Etude de table : retourner les caractéristiques des statistiques et index d’une table (ou vue indexée) »

  1. Effectivement, merci de l’avoir relevé :)
    En fait l’erreur est levée par le script, et pas par l’appel de la DMF sys.dm_db_index_physical_stats().
    Les commentaires sont donc incorrects, et je les ai enlevés :)

    Ce script n’est destiné qu’à l’étude manuelle d’une table ou vue indexée en particulier.
    Normalement il vient en complément de jobs collectant la fragmentation des index d’une base de données et l’éliminant, et d’un job maintenant les statistiques.

    Est-ce à dire que vous seriez intéressé par un script / une procédure stockée faisant ce que fait ce script mais pour toute une base de données ?
    Notez que suivant le volume de la base de données, le temps d’exécution d’un tel script peut être long et impacter les performances de la base de données auditée.

  2. Bonjour,
    Il y a un petit bug vers la ligne 10 ou 11

    , @table_or_indexed_view_name sysname = NULL– NULL étudie toutes les tables

    Avec la valeur NULL on a un message qui dit que NULL n’existe pas.

Laisser un commentaire