Массовое перестроение индексов в Microsoft SQL Server (переиндексация БД)

Всем привет! Сейчас мы с Вами напишем процедуру на языке Transact-SQL, которая будет осуществлять массовое перестроение индексов или их реорганизацию в зависимости от степени фрагментации этих индексов.

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

Итак, нам понадобится инструмент разработки и запуска сценариев на T-SQL, в Microsoft SQL Server таким инструментом является среда SQL Server Management Studio (SSMS), но Вы это, конечно же, уже должны знать.

Открываем Management Studio, создаем запрос (сценарий) и вставляем следующий код, он создает процедуру (я ее назвал SP_ReIndex), которая и будет перестраивать или реорганизовывать индексы в зависимости от степени их фрагментации. Microsoft рекомендует:

  • Если степень фрагментации менее 5%, индекс перестраивать или реорганизовывать не нужно;
  • Если степень фрагментации от 5 до 30%, лучше выполнять реорганизацию индекса;
  • Если степень фрагментации более 30%, лучше выполнять перестроение индекса.

Мы так и поступим.

Код процедуры я прокомментировал, чтобы Вы понимали какой участок кода что делает.

Примечание! В качестве SQL сервера у меня выступает версия Microsoft SQL Server 2016 Express.

Код процедуры для массового перестроения индексов

   
        CREATE PROCEDURE SP_ReIndex (@DbId SMALLINT = NULL)
        AS
        BEGIN
        /*
                Процедура для массовой переиндексации БД.
        */
        --Запрещаем вывод количества строк
        --Для того чтобы выводилась только интересующая нас информация
        SET NOCOUNT ON; 

        --Табличная переменная для хранения названий объектов (индексов, таблиц) для обслуживания
        DECLARE @IndexTmpTable TABLE (Id INT IDENTITY(1,1) PRIMARY KEY,
                                                                  SchemaName SYSNAME, 
                                                                  TableName SYSNAME, 
                                                                  IndexName SYSNAME, 
                                                                  AvgFrag FLOAT);
        --Вспомогательные переменные
        DECLARE @RowNumber INT = 1, @CntRows INT, @CntReorganize INT = 0, @CntRebuild INT = 0;
        DECLARE @SchemaName SYSNAME, @TableName SYSNAME, @IndexName SYSNAME, @AvgFrag FLOAT;
        DECLARE @Command VARCHAR(8000);

        --Идентификатор БД (можно передать во входящем параметре, по умолчанию текущая)
        SELECT @DbId = COALESCE(@DbId, DB_ID()); 

        --Определяем степень фрагментации индексов на основе системной табличной функции
        --sys.dm_db_index_physical_stats, а также название индексов и соответствующих таблиц
        INSERT INTO @IndexTmpTable
                SELECT Sch.name AS SchemaName,
                           Obj.name AS TableName,
                           Inx.name AS IndexName, 
                           AvgFrag.avg_fragmentation_in_percent AS Fragmentation 
                  FROM sys.dm_db_index_physical_stats (@DbId, NULL, NULL, NULL, NULL) AS AvgFrag
                  LEFT JOIN sys.indexes AS Inx ON AvgFrag.object_id = Inx.object_id AND AvgFrag.index_id = Inx.index_id
                  LEFT JOIN sys.objects AS Obj ON AvgFrag.object_id = Obj.object_id 
                  LEFT JOIN sys.schemas AS Sch ON Obj.schema_id = Sch.schema_id
                  WHERE AvgFrag.index_id > 0 
                        AND AvgFrag.avg_fragmentation_in_percent > 5 --5 - это минимальная степень фрагментации индекса

        --Количество строк для обработки
        SELECT @CntRows = COUNT(*)
        FROM @IndexTmpTable

        --Цикл обработки каждого индекса
        WHILE @RowNumber <= @CntRows
                BEGIN
                  --Получаем названия объектов, а также степень фрагментации текущего индекса
                  SELECT @SchemaName = SchemaName, 
                         @TableName = TableName, 
                         @IndexName = IndexName, 
                         @AvgFrag = AvgFrag
                  FROM @IndexTmpTable
                  WHERE Id = @RowNumber
                        
                  --Если степень фрагментации менее 30%, выполняем реорганизацию индекса
                  IF @AvgFrag < 30
                     BEGIN
                       --Формируем строку инструкции и выполняем ее
                       SELECT @Command = 'ALTER INDEX [' + @IndexName + '] ON ' + '[' + @SchemaName + ']' 
                                          + '.[' + @TableName + '] REORGANIZE';
                       EXEC (@Command);
                       SET @CntReorganize = @CntReorganize + 1; --Количество реорганизованных индексов
                      END 
                        
                  --Если степень фрагментации более 30%, выполняем перестроение индекса
                  IF @AvgFrag >= 30
                     BEGIN
                        --Формируем строку инструкции и выполняем ее
                        SELECT @Command = 'ALTER INDEX [' + @IndexName + '] ON ' + '[' + @SchemaName + ']' 
                                            + '.[' + @TableName + '] REBUILD';
                        EXEC (@Command);
                        SET @CntRebuild = @CntRebuild + 1; --Количество перестроенных индексов
                     END
                        
                   --Выводим служебную информацию о текущей операции
                   PRINT 'Выполнена инструкция ' + @Command;
                        
                   --Переходим к следующему индексу
                   SET @RowNumber = @RowNumber + 1
                END
                
                --Итог
                PRINT 'Всего обработано индексов: ' + CAST(@CntRows AS VARCHAR(10)) 
                        + ', Реорганизовано: ' + CAST(@CntReorganize AS VARCHAR(10)) 
                        + ', Перестроено: ' + CAST(@CntRebuild AS VARCHAR(10))
        END

Скриншот 1

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

      
        EXEC SP_ReIndex

Заметка! Начинающим программистам рекомендую почитать мою книгу «Путь программиста T-SQL», в ней я подробно, с большим количеством примеров, рассказываю про другие полезные возможности языка Transact-SQL.

На этом у меня все, надеюсь, материал помог начинающим, пока!

Понравилась статья? Поделиться с друзьями:
Заметки IT специалиста
Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:
Нажимая на кнопку «Отправить комментарий», я даю согласие на обработку персональных данных и принимаю политику конфиденциальности.