Une procédure stockée pour défragmenter les indexes sous SQL Server 2005 et ultérieur

Voici une petite procédure stockée que l’on peut exécuter régulièrement dans un job pour défragmenter les indexes de toutes les bases de données, en fixant les seuils de nombre de page et de pourcentage moyen de fragmentation

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
---------------------------------
-- Nicolas SOUQUET - 05/06/2009 -
---------------------------------
CREATE PROCEDURE [dbo].[Ps_Indexes_Rebuild]
AS
BEGIN
  SET NOCOUNT ON
 
  -- Table dans laquelle on va récupérer les noms et caractéristiques des indexes
  DECLARE @Tbindexes TABLE
  (
    DB SYSNAME,
    Tables SYSNAME,
    IndexName SYSNAME,
    Frag VARCHAR(5),
    PageUsage VARCHAR(5),
    Pages VARCHAR(10),
    FillFactors TINYINT,
    Seeks VARCHAR(20),
    Scans VARCHAR(20),
    LastUserSeek VARCHAR(19),
    LastUserScan VARCHAR(19),
    IsUniqueConstraint BIT,
    IsPadded BIT,
    IsHypothetical BIT
  )
 
  -- Table dans laquelle on récupère les statistiques des indexes
  DECLARE @TbIndexesStats TABLE
  (
    database_id SMALLINT,
    object_id INT,
    index_id INT,
    avg_fragmentation_in_percent NUMERIC(4,2),
    avg_page_space_used_in_percent NUMERIC(4,2),
    page_count BIGINT,
    user_seeks BIGINT,
    user_scans BIGINT,
    last_user_seek DATETIME,
    last_user_scan DATETIME
  );
 
  -- Récupération des stats sur les indexes
  WITH CTE_DB AS
  (
    SELECT database_id
    FROM sys.databases
    WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb', 'ReportServer', 'ReportServerTempDB', 'distribution')
  )
  INSERT INTO @TbIndexesStats
  SELECT IPS.database_id,
      IPS.object_id,
      IPS.index_id,
      IPS.avg_fragmentation_in_percent,
      IPS.avg_page_space_used_in_percent,
      IPS.page_count,
      IUS.user_seeks,
      IUS.user_scans,
      IUS.last_user_seek,
      IUS.last_user_scan
  FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'SAMPLED') IPS
  JOIN CTE_DB ON CTE_DB.database_id = IPS.database_id
  JOIN sys.dm_db_index_usage_stats IUS
      ON IUS.database_id = IPS.database_id
      AND IUS.object_id = IPS.object_id
      AND IUS.index_id = IPS.index_id
  WHERE IPS.index_id > 0
  AND IPS.avg_fragmentation_in_percent > 25.0
  AND IPS.page_count > 50
 
  -- Récupération des noms et caractéristiques des indexes
  DECLARE @SQL VARCHAR(1024),
      @Database_id VARCHAR(10),
      @Object_id VARCHAR(10),
      @Index_id VARCHAR(10),
      @AvgFragInPercent VARCHAR(5),
      @AvgPageSpaceUsedInPercent VARCHAR(5),
      @PageCount VARCHAR(10),
      @User_seeks VARCHAR(20),
      @User_scans VARCHAR(20),
      @Last_user_seek VARCHAR(19),
      @Last_user_scan VARCHAR(19)
 
  DECLARE curDBIndexes CURSOR FOR
    SELECT CAST(database_id AS VARCHAR(10)),
      CAST(object_id AS VARCHAR(10)),
      CAST(index_id AS VARCHAR(10)),
      CAST(avg_fragmentation_in_percent AS VARCHAR(5)),
      CAST(avg_page_space_used_in_percent AS VARCHAR(5)),
      CAST(page_count AS VARCHAR(10)),
      CAST(user_seeks AS VARCHAR(20)),
      CAST(user_scans AS VARCHAR(20)),
      COALESCE(CONVERT(VARCHAR(19), last_user_seek, 120), 'NULL'),
      COALESCE(CONVERT(VARCHAR(19), last_user_scan, 120), 'NULL')
    FROM @TbIndexesStats
  FOR READ ONLY
 
  OPEN curDBIndexes
  FETCH NEXT FROM curDBIndexes INTO @Database_id, @Object_id, @Index_id, @AvgFragInPercent,
                    @AvgPageSpaceUsedInPercent, @PageCount, @User_seeks, @User_scans,
                    @Last_user_seek, @Last_user_scan
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @SQL = 'SELECT DB_NAME(' + @Database_id + '),' +
            'O.name,' +
            'IDX.name,' +
            @AvgFragInPercent + ',' +
            @AvgPageSpaceUsedInPercent + ',' +
            @PageCount + ',' +
            'IDX.fill_factor, ' +
            @User_seeks + ',' +
            @User_scans + ',''' +
            @Last_user_seek + ''',''' +
            @Last_user_scan + ''',' +
            'IDX.is_unique_constraint, ' +
            'IDX.is_padded, ' +
            'IDX.is_hypothetical' +
          ' FROM ' + DB_NAME(CAST(@Database_id AS INT)) + '.sys.indexes IDX' +
          ' JOIN '  + DB_NAME(CAST(@Database_id AS INT)) + '.sys.objects O ON O.object_id = IDX.object_id' +
          ' WHERE IDX.object_id = ' + @Object_id +
          ' AND IDX.index_id = ' + @Index_id
 
    INSERT INTO @Tbindexes
    EXEC (@SQL)
 
    FETCH NEXT FROM curDBIndexes INTO @Database_id, @Object_id, @Index_id, @AvgFragInPercent,
                      @AvgPageSpaceUsedInPercent, @PageCount, @User_seeks, @User_scans,
                      @Last_user_seek, @Last_user_scan
  END
  DEALLOCATE curDBIndexes
 
  -- Reconstruction des indexes
  DECLARE @DB SYSNAME,
      @Tables SYSNAME,
      @IndexName SYSNAME,
      @FillFactors VARCHAR(5),
      @IsPadded BIT,
      @RebuildCommand VARCHAR(1024)
 
  DECLARE CUR_INDEXES_TO_REBUILD CURSOR FOR
    SELECT DB,
        Tables,
        IndexName,
        CAST(
            CASE FillFactors
              WHEN 0 THEN 99
              ELSE FillFactors
            END AS VARCHAR(5)
          ),
        IsPadded
    FROM @Tbindexes
  FOR READ ONLY
 
  OPEN CUR_INDEXES_TO_REBUILD
  FETCH NEXT FROM CUR_INDEXES_TO_REBUILD INTO @DB, @Tables, @IndexName, @FillFactors, @IsPadded
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @RebuildCommand = 'ALTER INDEX ' + @IndexName + ' ON ' + @DB + '.dbo.' + @Tables +  
                  ' REBUILD WITH (FILLFACTOR = ' + @FillFactors +  
                  CASE @IsPadded
                    WHEN 1 THEN ', PAD_INDEX = ON)'
                    ELSE ')'
                  END
   
    EXEC (@RebuildCommand)
    FETCH NEXT FROM CUR_INDEXES_TO_REBUILD INTO @DB, @Tables, @IndexName, @FillFactors, @IsPadded
  END
  DEALLOCATE CUR_INDEXES_TO_REBUILD
END

ElSuket

2 réflexions au sujet de « Une procédure stockée pour défragmenter les indexes sous SQL Server 2005 et ultérieur »

  1. Merci pour ce retour !

    Je viens de corriger l’erreur sur le @@FETCH_STATUS.
    Tu peux voir rapidement sur quels indexes la procédure stockée est passée en interrogeant la table sys.indexes avec la fonction STATS_DATE() :

    SELECT O.name AS nomobjet,
    I.name AS nomIndex,
    STATS_DATE(I.object_id, I.index_id) AS dateDerniereMAJ
    FROM sys.indexes AS I
    JOIN sys.objects AS O
    ON I.object_id = O.object_id
    AND O.type_desc IN (‘U’, ‘V’)

    J’ai mis U (table) et V (vue) si tu as des vues indexées ;)
    Sinon tu peux supprimer la ligne AND de la jointure et remplacer sys.objects par sys.tables ou sys.views, au choix.

    Bonne défragmentation ;)

  2. Merci Elsuket pour toutes tes contributions, toujours très utiles.
    Ici je signale simplement que les 2 occurences de @@ETCH_STATUS doivent être corrigées en @@FETCH_STATUS.
    Ensuite la procédure s’éxécute correctement.
    Ce serait un plus si l’on pouvait tracer les tables et les indexes sur lesquels la procédure a agit.

    Merci encore.

Laisser un commentaire