MSSQL Переиндексация в несколько потоков

Не раз слышал мнения 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% выигрыша по времени. И задание успевает выполниться за тех окно.