Регламентные операции с индексами в MS SQL Server (Скрипты для SQL-Server — Часть 2)

-------------------------------------------
-- НАСТРАИВАЕМЫЕ ПЕРЕМЕННЫЕ
-- База данных для анализа
USE WorkBase

-------------------------------------------
-- ТЕЛО СКРИПТА

-- Отбираем объекты, которые:
-- являются индексами (index_id > 0)
-- фрагментация которых более 5%
-- количество страниц в индексе более 128
SELECT
OBJECT_NAME(object_id) AS TableName,
object_id,
index_id,
partition_number,
page_count,
partition_number,
index_type_desc,
avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE index_id > 0
AND avg_fragmentation_in_percent > 5.0
AND page_count > 128
ORDER BY avg_fragmentation_in_percent DESC

GO

-------------------------------------------
-- НАСТРАИВАЕМЫЕ ПАРАМЕТРЫ
-- База данных для анализа
USE WorkBase

-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ
DECLARE @object_id int; -- ID объекта
DECLARE @index_id int; -- ID индекса
DECLARE @partition_number bigint; -- количество секций если индекс секционирован
DECLARE @schemaname nvarchar(130); -- имя схемы в которой находится таблица
DECLARE @objectname nvarchar(130); -- имя таблицы
DECLARE @indexname nvarchar(130); -- имя индекса
DECLARE @partitionnum bigint; -- номер секции
DECLARE @fragmentation_in_percent float; -- процент фрагментации индекса
DECLARE @command nvarchar(4000); -- инструкция T-SQL для дефрагментации либо ренидексации

-------------------------------------------
-- ТЕЛО СКРИПТА

-- Отключаем вывод количества возвращаемых строк, это несколько ускорит обработку
SET NOCOUNT ON;

-- Удалим временные таблицы, если вдруг они есть
IF OBJECT_ID('tempdb.dbo.#work_to_do') IS NOT NULL DROP TABLE #work_to_do

-- Отбор таблиц и индексов с помощью системного представления sys.dm_db_index_physical_stats
-- Отбор только тех объектов которые:
-- являются индексами (index_id > 0)
-- фрагментация которых более 5%
-- количество страниц в индексе более 128
SELECT
object_id,
index_id,
partition_number,
avg_fragmentation_in_percent AS fragmentation_in_percent
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE index_id > 0
AND avg_fragmentation_in_percent > 5.0
AND page_count > 128;

-- Объявление Открытие курсора курсора для чтения секций
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
OPEN partitions;

-- Цикл по секциям
FETCH NEXT FROM partitions INTO @object_id, @index_id, @partition_number, @fragmentation_in_percent;
WHILE @@FETCH_STATUS = 0
BEGIN

-- Собираем имена объектов по ID
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @object_id;

SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE  object_id = @object_id AND index_id = @index_id;

SELECT @partition_number = count (*)
FROM sys.partitions
WHERE object_id = @object_id AND index_id = @index_id;

-- Если фрагментация менее или равна 30% тогда дефрагментация, иначе реиндексация
IF @fragmentation_in_percent < 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
IF @fragmentation_in_percent >= 30.0
SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
IF @partition_number > 1
SET @command = @command + N' PARTITION=' + CAST(@partition_number AS nvarchar(10));

-- Выполняем команду
EXEC (@command);
PRINT N'Index: object_id=' + STR(@object_id) + ', index_id=' + STR(@index_id) + ', fragmentation_in_percent=' + STR(@fragmentation_in_percent);
PRINT N'Executed: ' + @command;

-- Следующий элемент цикла
FETCH NEXT FROM partitions INTO @object_id, @index_id, @partition_number, @fragmentation_in_percent;

END;

-- Закрытие курсора
CLOSE partitions;
DEALLOCATE partitions;

-- Удаление временной таблицы
DROP TABLE #work_to_do;

GO

---------------------------------------------
-- НАСТРАИВАЕМЫЕ ПАРАМЕТРЫ
-- Условие для выборки, '%' - все базы данных
DECLARE @namelike varchar(100) = 'WorkBase%'
-- Имя почтового профиля, для отправки электонной почты
DECLARE @profilename as nvarchar(100) = 'ОсновнойПрофиль'
-- Получатели сообщений электронной почты, разделенные знаком ";"
DECLARE @recipients as nvarchar(500) = 'admin@mydomen.com'

-------------------------------------------
-- СЛУЖЕБНЫЕ ПЕРЕМЕННЫЕ
DECLARE @database_id varchar(100) -- ID баз данных
DECLARE @database_name varchar(100) -- Имена баз данных
DECLARE @command nvarchar(4000); -- инструкция T-SQL для дефрагментации либо ренидексации
DECLARE @subject as NVARCHAR(1000) = '' -- тема сообщения
DECLARE @finalmassage as NVARCHAR(4000) = '' -- текст сообщения

-------------------------------------------
-- ТЕЛО СКРИПТА
USE master

-- Отключаем вывод количества возвращаемых строк, это несколько ускорит обработку
SET NOCOUNT ON;

-- Выбираем базы данных
DECLARE DBcursor CURSOR FOR
(
SELECT
database_id as database_id,
name as database_name
FROM sys.databases d
WHERE
d.name <> 'tempdb'
AND d.name <> 'master'
AND d.name <> 'model'
AND d.name <> 'msdb'
AND d.state_desc = 'ONLINE' -- база должна быть в сети
AND d.name like @namelike -- база должна содержать указанное слово
)

-- Цикл по всем базам, попавшим в выборку
OPEN DBcursor
FETCH NEXT FROM DBcursor INTO @database_id, @database_name
WHILE @@FETCH_STATUS = 0
BEGIN

-- База данных из цикла
PRINT N'----------------------------------------------------------'
PRINT N'USE [' + @database_name + N']'
SET @command =
N'USE [' + @database_name + N']

DECLARE @object_id int; -- ID объекта
DECLARE @index_id int; -- ID индекса
DECLARE @partition_number bigint; -- количество секций если индекс секционирован
DECLARE @schemaname nvarchar(130); -- имя схемы в которой находится таблица
DECLARE @objectname nvarchar(130); -- имя таблицы
DECLARE @indexname nvarchar(130); -- имя индекса
DECLARE @partitionnum bigint; -- номер секции
DECLARE @fragmentation_in_percent float; -- процент фрагментации индекса
DECLARE @command nvarchar(4000); -- инструкция T-SQL для дефрагментации либо ренидексации

-- Отбор таблиц и индексов с помощью системного представления sys.dm_db_index_physical_stats
-- Отбор только тех объектов которые:
--  являются индексами (index_id > 0)
--   фрагментация которых более 5%
--   количество страниц в индексе более 128
SELECT
object_id,
index_id,
partition_number,
avg_fragmentation_in_percent AS fragmentation_in_percent
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, ''LIMITED'')
WHERE index_id > 0
AND avg_fragmentation_in_percent > 5.0
AND page_count > 128;

-- Объявление Открытие курсора курсора для чтения секций
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
OPEN partitions;

-- Цикл по секциям
FETCH NEXT FROM partitions INTO @object_id, @index_id, @partition_number, @fragmentation_in_percent;
WHILE @@FETCH_STATUS = 0
BEGIN

-- Собираем имена объектов по ID
SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @object_id;

SELECT @indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @object_id AND index_id = @index_id;

SELECT @partition_number = count (*)
FROM sys.partitions
WHERE object_id = @object_id AND index_id = @index_id;

-- Если фрагментация менее или равна 30% тогда дефрагментация, иначе реиндексация
IF @fragmentation_in_percent < 30.0
SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REORGANIZE'';
IF @fragmentation_in_percent >= 30.0
SET @command = N''ALTER INDEX '' + @indexname + N'' ON '' + @schemaname + N''.'' + @objectname + N'' REBUILD'';
IF @partition_number > 1
SET @command = @command + N'' PARTITION='' + CAST(@partition_number AS nvarchar(10));

-- Выполняем команду
PRINT N''    Executed: '' + @command;
EXEC sp_executesql @command

-- Следующий элемент цикла
FETCH NEXT FROM partitions INTO @object_id, @index_id, @partition_number, @fragmentation_in_percent;

END;

-- Закрытие курсора
CLOSE partitions;
DEALLOCATE partitions;

-- Удаление временной таблицы
DROP TABLE #work_to_do;';
BEGIN TRY
EXEC sp_executesql @command
SET @finalmassage = @finalmassage + 'Успешное выполнение операций обслуживания индексов для базы данных ' + @database_name + CHAR(13) + CHAR(13)
END TRY
BEGIN CATCH
-- Ошбика выполнения операции
SET @subject = 'БЫЛИ ОШИБКИ при выполнении операций обслуживания индексов '
SET @finalmassage = @finalmassage + 'ОШИБКА обслуживания индекса для базы данных ' + @database_name + CHAR(13) + CHAR(13)
+ 'Код ошибки: ' + CAST(ERROR_NUMBER() as nvarchar(10)) + CHAR(13) + CHAR(13)
+ 'Текст ошибки: ' + ERROR_MESSAGE()  + CHAR(13) + CHAR(13)
+ 'Текст T-SQL: ' + CHAR(13) + @command + CHAR(13) + CHAR(13)
END CATCH;

-- Следующая база данных
FETCH NEXT FROM DBcursor INTO @database_id, @database_name
END;

CLOSE DBcursor;
DEALLOCATE DBcursor;

-- Формируем сообщение об успешном или не успешном выполнении операций
IF @subject = ''
BEGIN
-- Успешное выполнение всех операций
SET @subject = 'Успешное выполнение операций обслуживания индексов '
END

-- Если задан профиль электронной почты, отправим сообщение
PRINT N'----------------------------------------------------------'
IF @profilename <> ''
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profilename,
@recipients = @recipients,
@body = @finalmassage,
@subject = @subject;

-- Выводим сообщение о результате
SELECT
@subject as subject,
@finalmassage as finalmassage

GO