Адаптивное обновление индексов 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 
         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;