Не раз слышал мнения 1сников, что переиндексации вообще не нужна. Так как мы живем сейчас в век ssd и nvme. И не важно какая у вас фрагментация индекса нужно просто делать обновление статистики и все. И как подтверждение кидают абзац от Microsoft

Но почему – то не читают начало этой статьи, в которой сказано, как влияет фрагментация и плотность страниц на общую производительность. И расход ресурсов

И то что даже на ssd и NMVE последовательная скорость чтения запись всегда выше чем случайная

И про плотность, (если у вас конечно на сервере на 2ТБ памяти на базу 2ТБ)

Подытожим. Обновление статистики и правда даст вам сиюминутный эффект. Но со временем фрагментация сделает свое, и вам не хватает уже мощностей железа что бы перекрыть тормоза.

Так что мое мнение переиндексация нужна! Но не нужно каждый день перестраивать все индексы) Благо всевозможный скриптов хватает в сети.

Идея появилась из-за проблемы, что база в 20ТБ не могла за выделение тех окно пройти «полную» переиндексацию. И через неделю-две начинались дикие тормоза и обновление статистики не давало явного ускорения. Пробовал чужие скрипты с паузой и прочие. Но во всех скриптах перестройка индексов идет в один поток (я говорю не про maxdop). Что пока один индекс не перестроиться, другие не начнут.  Так что вот представляю мой костыль)

Скрипт представляю в немного урезанном виде и на 2 потока.

Шаги в скрипте.

1. Собрать статистику по всем индексам в базе их фрагментации и размеру. Занести данные в служебную базу.

2. Выполнить задание перестройки по первой половине данных

3. Выполнить задание перестройки по второй половине данных

4. Обновить статистку, по все базе.

Шаги 2 и 3 выполняются параллельно.

Зачем, вы спросите, обновлять статистику по всей базе если переиндексация обновляет статистику? А я отвечу. Мы перестраиваем не все таблицы. А только что перестроенные индексы (при условии, что не было еще ни одной вставки) будут пропущены с сообщение, что нечего там обновлять. Это проще чем делать еще один шаг с условием. Что если переиндексация по таблице не проходила, то обнови статистику.

Можно все сделать разными jobs с привязкой старта, разным логом и прочим. Но так как это lite версия скрипта, пример будет создан на maintenance Plan.

Так как я встречал моветон, имена БД через '–' а не '_', что заставляет городить конструкции с выделением имени БД в несколько кавычек (так как – в скриптах это может быть как действием так и символом).

 Поэтому в скрипте есть 2 разные «переменные» DATA-BASE(Имя базы) и DATA_BASE(Служебная переменная) Во всех файлах необходимо заменить  DATA-BASE имя вашей БД (к примеру SP-UPP), DATA_BASE заменить на имя базы без '-' (SPUPP или SP_UPP)

Скрипт на первом шаге, создает служебную базу profiler (если ее нет). Собираем статистику по индексам и заполняем базу profiler. Сбор статистики достаточно долгий процесс, который не сильно влияет на скорость. То есть 1 шаг лучше запускать заранее. К примеру тех окно у вас с 21:00 часа первый шаг у вас выполняется за 40 мин и не мешает пользователям, то статистику можно начать собирать в 20:20.

 1.

SET QUOTED_IDENTIFIER ON;

if DB_ID('profiler') IS NULL
BEGIN
 PRINT 'Creating Profiler database'
 CREATE DATABASE Profiler
END

declare @astor_name VARCHAR(255)
declare @astor_id INT
SELECT TOP 1 @astor_name = QUOTENAME(name), @astor_id=database_id FROM sys.databases where name like 'DATA-BASE'
DECLARE @rebuildOptions nvarchar(MAX) = N' WITH (maxdop = 10, ONLINE = ON, SORT_IN_TEMPDB = ON)'
DECLARE @partitionnum bigint;
DECLARE @partitions bigint;
DECLARE @objectid int;
DECLARE @indexid int;
DECLARE @partitioncount bigint;

PRINT 'Woring with ' + @astor_name 

DECLARE @indexTable VARCHAR(255) 
SET @indexTable= @astor_name + '.sys.indexes'

if OBJECT_ID('temporary_indexes_DATA_BASE') IS NOT NULL 
 DROP VIEW temporary_indexes_DATA_BASE;

EXEC('CREATE VIEW temporary_indexes_DATA_BASE AS 
  SELECT 
    idx.object_id as object_id, 
    idx.index_id as index_id,
    sch.name as schema_name,
    obj.name as table_name,
    idx.name as index_name,
    idx.type_desc as type_desc 
   FROM ' + @astor_name + '.sys.indexes as idx
   JOIN '  + @astor_name + '.sys.objects as obj ON obj.object_id = idx.object_id
   JOIN '  + @astor_name + '.sys.schemas as sch ON sch.schema_id = obj.schema_id')

DECLARE @FramentationReportTable VARCHAR(255) 
SET @FramentationReportTable = 'Fragmentation_DATA_BASE_'+REPLACE(convert(varchar, getdate(), 102), '.', '_')

IF OBJECT_ID('tempdb..##fragmentation_DATA_BASE') IS NOT NULL
 DROP TABLE ##fragmentation_DATA_BASE

DECLARE @reportNum INT 
SET @reportNum = 1

WHILE OBJECT_ID('[Profiler].dbo.' + @FramentationReportTable+'_' + CAST(@reportNum AS nvarchar(255))) IS NOT NULL  
 SELECT @reportNum = @reportNum + 1;

SELECT @FramentationReportTable = @FramentationReportTable + '_' + CAST(@reportNum AS nvarchar(255))

DROP SEQUENCE IF EXISTS Sequence;
CREATE SEQUENCE Sequence
    START WITH 1  
    INCREMENT BY 1  
    MINVALUE 1  
    MAXVALUE 2  
    CYCLE  

RAISERROR( N'Analyzing indexes',0,1) WITH NOWAIT
SELECT
 DB_NAME(stats.database_id) as db_name,
 idx.schema_name as schema_name,
 idx.table_name AS table_name,
    idx.index_name AS index_name,
 idx.type_desc as index_type,
    stats.partition_number AS partition_num,
    stats.avg_fragmentation_in_percent AS fragmentation,
 stats.avg_page_space_used_in_percent as page_fullness,
 stats.avg_record_size_in_bytes as record_size,
 stats.record_count as rows_count,
 stats.page_count as page_count
 --- ,next value for Sequence over (order by [record_count] desc) as num
INTO ##fragmentation_DATA_BASE
FROM sys.dm_db_index_physical_stats (@astor_id, NULL, NULL , NULL, 'SAMPLED') as stats
JOIN temporary_indexes_DATA_BASE idx ON idx.object_id = stats.object_id and idx.index_id = stats.index_id
DROP VIEW temporary_indexes_DATA_BASE

EXEC ('SELECT [db_name]
      ,[schema_name]
      ,[table_name]
      ,[index_name]
      ,[index_type]
      ,[partition_num]
      ,[fragmentation]
      ,[page_fullness]
      ,[record_size]
      ,[rows_count]
      ,[page_count]
   ,next value for Sequence over (order by ROUND ([fragmentation],0 ) desc, [rows_count] desc) as num INTO profiler.dbo.'+ @FramentationReportTable +' FROM ##fragmentation_DATA_BASE')

2 и 3 шаги почти идентичные кроме условия условий выбора и названия курсора.

2.    

DECLARE @FramentationReportTable_1 VARCHAR(255) 
SET @FramentationReportTable_1 = (SELECT top 1 table_name FROM Profiler.INFORMATION_SCHEMA.TABLES  WHERE table_Name LIKE 'Fragmentation_DATA_BASE_'+REPLACE(convert(varchar, getdate(), 102), '.', '_') + '%')
print @FramentationReportTable_1

exec ('
DECLARE bad_indexes_1 CURSOR FOR 
 
 select
 frag.db_name,
 frag.schema_name,
 frag.table_name,
 frag.index_name,
 frag.partition_num,
 case
  when frag.record_size*16 <= 403 then 95
  when frag.record_size*16 <= 806 then 90
  when frag.record_size*16 <= 1209 then 85
  else 80
 end as suggested_fillfactor
 
 
 from Profiler.dbo.'+ @FramentationReportTable_1 +' frag
 where frag.page_count > 24 and frag.fragmentation >= 5  and frag.num = 1  and frag.index_type <>''HEAP''
 order by ROUND ([fragmentation],0 ) desc, [rows_count] desc')


-- Open the cursor.
OPEN bad_indexes_1

DECLARE @db_name nvarchar(130);
DECLARE @schema_name nvarchar(130);
DECLARE @table_name nvarchar(130);
DECLARE @index_name nvarchar(130);
DECLARE @fragmentation bigint;
DECLARE @suggested_fillfactor int;
DECLARE @partition_num bigint;
DECLARE @partitionOption nvarchar(130);
DECLARE @fillfactorOption nvarchar(130);
DECLARE @object_name nvarchar(1000);
DECLARE @command nvarchar(1000);
DECLARE @time nvarchar(130)

WHILE (1=1) 
 BEGIN
 FETCH NEXT
           FROM bad_indexes_1
           INTO @db_name, @schema_name, @table_name, @index_name, @partition_num, @suggested_fillfactor;

 IF @@FETCH_STATUS < 0 BREAK
 
 

   IF @partition_num > 1
   begin
    SET @partitionOption = N' PARTITION=' + CAST(@partition_num AS nvarchar(10));
    set @fillfactorOption = N''
   end
  else 
   begin
    SET @partitionOption = N''
    set @fillfactorOption = N' FILLFACTOR=' + CAST(@suggested_fillfactor as nvarchar(10)) + N', '
   end

 SET @object_name = QUOTENAME(@db_name) + N'.' + QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name)
 BEGIN TRY

    SET @command = N'ALTER INDEX ' + QUOTENAME(@index_name) + N' ON ' + @object_name + ' REBUILD ' + @partitionOption +' WITH(' + @fillfactorOption +  N'maxdop = 0, ONLINE = ON, SORT_IN_TEMPDB = ON, MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = BLOCKERS)'
  set @time = CURRENT_TIMESTAMP;
  print @time
  RAISERROR(@command, 0, 1)
  EXEC(@command)
  set @time = CURRENT_TIMESTAMP;
  RAISERROR(N'DONE', 0, 1) 
 END TRY
 BEGIN CATCH
     SET @command = N'ALTER INDEX ' + QUOTENAME(@index_name) + N' ON ' + @object_name + ' REBUILD ' + @partitionOption +' WITH(' + @fillfactorOption +  N'maxdop = 0, SORT_IN_TEMPDB = ON)'
 set @time = CURRENT_TIMESTAMP;
  print @time
  RAISERROR(@command, 0, 1)
  EXEC(@command)
  set @time = CURRENT_TIMESTAMP;
  RAISERROR(N'DONE', 0, 1) 
  print @time
 END CATCH
END

close bad_indexes_1
deallocate bad_indexes_1

3.    

DECLARE @FramentationReportTable_2 VARCHAR(255) 
SET @FramentationReportTable_2 = (SELECT top 1 table_name FROM Profiler.INFORMATION_SCHEMA.TABLES  WHERE table_Name LIKE 'Fragmentation_DATA_BASE_'+REPLACE(convert(varchar, getdate(), 102), '.', '_') + '%')
print @FramentationReportTable_2

exec ('
DECLARE bad_indexes_2 CURSOR FOR 
 
 select
 frag.db_name,
 frag.schema_name,
 frag.table_name,
 frag.index_name,
 frag.partition_num,
 case
  when frag.record_size*16 <= 403 then 95
  when frag.record_size*16 <= 806 then 90
  when frag.record_size*16 <= 1209 then 85
  else 80
 end as suggested_fillfactor
 
 
 from Profiler.dbo.'+ @FramentationReportTable_2 +' frag
 where frag.page_count > 24 and frag.fragmentation >= 5  and frag.num = 2  and frag.index_type <>''HEAP''
 order by ROUND ([fragmentation],0 ) desc, [rows_count] desc')


-- Open the cursor.
OPEN bad_indexes_2

DECLARE @db_name nvarchar(130);
DECLARE @schema_name nvarchar(130);
DECLARE @table_name nvarchar(130);
DECLARE @index_name nvarchar(130);
DECLARE @fragmentation bigint;
DECLARE @suggested_fillfactor int;
DECLARE @partition_num bigint;
DECLARE @partitionOption nvarchar(130);
DECLARE @fillfactorOption nvarchar(130);
DECLARE @object_name nvarchar(1000);
DECLARE @command nvarchar(1000);
DECLARE @time nvarchar(130)

WHILE (1=1) 
 BEGIN
 FETCH NEXT
           FROM bad_indexes_2
           INTO @db_name, @schema_name, @table_name, @index_name, @partition_num, @suggested_fillfactor;

 IF @@FETCH_STATUS < 0 BREAK
 
 

   IF @partition_num > 1
   begin
    SET @partitionOption = N' PARTITION=' + CAST(@partition_num AS nvarchar(10));
    set @fillfactorOption = N''
   end
  else 
   begin
    SET @partitionOption = N''
    set @fillfactorOption = N' FILLFACTOR=' + CAST(@suggested_fillfactor as nvarchar(10)) + N', '
   end

 SET @object_name = QUOTENAME(@db_name) + N'.' + QUOTENAME(@schema_name) + N'.' + QUOTENAME(@table_name)
 BEGIN TRY
 
    SET @command = N'ALTER INDEX ' + QUOTENAME(@index_name) + N' ON ' + @object_name + ' REBUILD ' + @partitionOption +' WITH(' + @fillfactorOption +  N'maxdop = 0, ONLINE = ON, SORT_IN_TEMPDB = ON, MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = BLOCKERS)'
  set @time = CURRENT_TIMESTAMP;
  print @time
  RAISERROR(@command, 0, 1)
  EXEC(@command)
  set @time = CURRENT_TIMESTAMP;
  RAISERROR(N'DONE', 0, 1) 
 END TRY
 BEGIN CATCH
   SET @command = N'ALTER INDEX ' + QUOTENAME(@index_name) + N' ON ' + @object_name + ' REBUILD ' + @partitionOption +' WITH(' + @fillfactorOption +  N'maxdop = 0, SORT_IN_TEMPDB = ON)'
 set @time = CURRENT_TIMESTAMP;
  print @time
  RAISERROR(@command, 0, 1)
  EXEC(@command)
  set @time = CURRENT_TIMESTAMP;
  RAISERROR(N'DONE', 0, 1) 
  print @time
 END CATCH
END

close bad_indexes_2
deallocate bad_indexes_2

В скриптах 2 и 3. Есть пара моментов, которые я хочу прояснить.

• есть проверка на партиции, и в зависимости от этого будет меняться запрос.

• есть изменение fillfactor в зависимости от record_size

• идет 2 условия. Пробует перестроить индекс online и с ожидание если не удается, то перестраивает индекс просто.

• RAISERROR со временем было сделано для того узнать время начала переиндексации по таблице и конца (Здесь осталось как отладочная команда). Эту инфу можно передать в другую базу для истории или диагностики.

4. Обновление статистики.

USE [DATA-BASE] 
        GO  
        EXEC sp_updatestats;

Если вдруг вы хотите добавить потоков, то необходимо будет в скрипте

• Изменить параметр в SEQUENCE

• Установить MAXVALUE на значение желаемых потоков

• И создать шаг наподобие 2 или 3

Но тут нужно хорошо думать. Так как переиндексация достаточно сильно нагружаемый процесс. И можно поставить сервер колом если делать несколько alter index сразу.

После пары запусков с разными настройками удалось добиться 30% выигрыша по времени. И задание успевает выполниться за тех окно.