Каталог решений - Адаптивное обновление индексов MS SQL

Параметрический  -каждый параметр можно подстроить под себя

Успешно работает на нескольких серверах

Протестирован на MS SQL 2019

Скрипт адаптивной обработки индекса, если индекс используется активно (параметр) и фрагментирован (параметр) или статистика неактуальна (параметр), то выполняет команды по обслуживанию индекса.

Каждая команда выводит в комментарий — почему она выполнилась.

Также можно как показать, что будет делать, так и выполнить сразу

DECLARE @OnlyShow INT = 0;  --1 = только показать, 0 = еще и выполнить
DECLARE @threshold_modification_pct INT = 1; -- Сколько процентов считать важными изменениями. Таблицы большие и 1% на миллионах –уже 10 000…
DECLARE @minrows INT = 100; -- брать если строк больше в индексе
DECLARE @minused INT = 1000; -- если использовали больше чем 1000 раз
DECLARE @minSteps INT = 10; -- не берем индкесы если они с точки статистики бестолковые
DECLARE @maxRows INT = 100000; -- Или количество строк меньше чем то игнор предыдущее условие
DECLARE @FragsLevel INT = 5; -- дефраг только если больше
DECLARE @FragsLevelRebuild INT = 30; -- rebuild если фрагментация больше
DECLARE @EnterpriseEdition INT = 0; --case when SERVERPROPERTY ('edition')='Enterprise Edition (64-bit)' then 1 else 0 end

---- служебные переменные

DECLARE @command NVARCHAR(500);
DECLARE @TableName SYSNAME;
DECLARE @IndexName SYSNAME;
DECLARE @modification_counter NVARCHAR(200);
DECLARE @Table_rows NVARCHAR(200);
DECLARE @Table_used NVARCHAR(200);
DECLARE @Steps NVARCHAR(200);
DECLARE @avg_fragmentation_in_percent NUMERIC;
DECLARE @levelForDefrag NUMERIC;
DECLARE @Frag NVARCHAR(200);
DECLARE @Variant NVARCHAR(20);

 

DECLARE indexes CURSOR FOR
  SELECT top 10
         obj.NAME                                    [table],
         stat.NAME                                   [index],
         modification_counter                        [modification_counter],
         sp.rows                                     [Table_rows],
         user_seeks + user_scans + user_lookups      AS Table_used,
         sp.steps                                    Steps,
         ips.avg_fragmentation_in_percent            AS  avg_fragmentation_in_percent  ,
         ips.avg_fragmentation_in_percent            AS Frag,
         sp.rows * @threshold_modification_pct / 100 AS levelForDefrag,
         CASE
           WHEN modification_counter >
                sp.rows * @threshold_modification_pct / 100
                    THEN
                           N'UPDATE STATISTICS'
           WHEN ips.avg_fragmentation_in_percent > @FragsLevelRebuild THEN
                           'REBUILD'
           ELSE 'REORGANIZE'
         END                                         AS Variant
  FROM   sys.objects AS obj
         INNER JOIN sys.stats AS stat
                 ON stat.object_id = obj.object_id
         CROSS apply sys.Dm_db_stats_properties(stat.object_id, stat.stats_id)
                     AS
                     sp
         LEFT JOIN (SELECT *
                    FROM   sys.Dm_db_index_physical_stats(Db_id(), NULL, NULL,
                           NULL, NULL
                           )) AS ips
                ON stat.object_id = ips.object_id
                   AND stat.stats_id = ips.index_id
         INNER JOIN sys.dm_db_index_usage_stats AS s
                 ON stat.object_id = s.object_id
                    AND stat.stats_id = s.index_id
  WHERE  s.database_id = Db_id()
         AND obj.type = 'U'
         AND stat.NAME NOT LIKE ( '_WA%' )
         AND ips.alloc_unit_type_desc = 'IN_ROW_DATA'
         AND @minrows < sp.rows
         AND ( user_seeks + user_scans + user_lookups ) >= @minused
         AND ( ips.avg_fragmentation_in_percent > @FragsLevel
                OR ( modification_counter >
                     sp.rows * @threshold_modification_pct / 100 )
             )
         AND ( sp.steps > @minSteps
                OR @maxRows > sp.rows )
  --ORDER  BY stat.NAME
  ; ---(user_seeks + user_scans + user_lookups) desc;
 
OPEN indexes;

WHILE ( 1 = 1 )
  BEGIN
      FETCH next FROM indexes INTO @TableName, @IndexName, @modification_counter    ,
      @Table_rows, @Table_used, @Steps, @avg_fragmentation_in_percent, @Frag,
      @levelForDefrag,@Variant;
 
      IF @@FETCH_STATUS < 0
        BREAK;
 
      IF @levelForDefrag < @modification_counter
        SET @command = N'UPDATE STATISTICS dbo.' + @TableName + ' '
                       + @IndexName
                       + N' WITH FULLSCAN, MAXDOP=8; --  Steps '
                       + @Steps + ', used ' + @Table_used + ', changed '
                       + @modification_counter + ' rows of '
                       + @Table_rows;
      ELSE
        SET @command =
             CASE
                    WHEN @variant='REBUILD'
               THEN
                                  ''
                          ELSE
                                  N'ALTER INDEX ' + @IndexName + ' on dbo.' + @TableName+ ' SET (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON);'
                           END
                    + N'ALTER INDEX ' + @IndexName + ' on dbo.'  + @TableName
                    + CASE
                           WHEN @variant='REBUILD'
                                  THEN
                                  ' REBUILD WITH (SORT_IN_TEMPDB = ON, maxdop=8' + case When @EnterpriseEdition=1 then ', online=on' else '' end +')'
                           ELSE
                                  ' REORGANIZE '
                      END
                    + CASE
                           WHEN @variant='REBUILD'
                                  THEN
                                        ''
                           ELSE
                                  N'; ALTER INDEX ' + @IndexName + ' on dbo.' + @TableName+ ' SET (ALLOW_PAGE_LOCKS = OFF, ALLOW_ROW_LOCKS = ON)'
                           END
                    + '; --- frag = ' + @Frag + '%'   + ', rows = ' + @Table_rows;
 
      PRINT N'Executed: ' + @command;
 
      IF @OnlyShow = 1
        CONTINUE;
 
      EXEC (@command);
  END;
 

CLOSE indexes;

DEALLOCATE indexes;