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