Всем привет! Сейчас мы с Вами напишем процедуру на языке 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
После того как мы выполнили данный сценарий, можем вызвать только что созданную процедуру, например, следующим образом, в данном случае мы не указываем базу данных.
EXEC SP_ReIndex
Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.
На этом у меня все, надеюсь, материал помог начинающим, пока!