Настройка плана обслуживания базы SQL

План

1. Сбор статистики - сделано

2. Дефрагментация и реиндексация.
   Для того, чтобы бесполезно не тратить время сервера, и дефрагментацию и перестроение индексов делаем не по всей базе, а только по тем, где фрагментация превышает 10%. Если фрагментация выше 30% - то перестроение индекса. Позже можно будет скорректировать значения.

   Дефрагментация и реиндексация Развернуть исходный код

   USE OG_MERCHANT-- устанавливаем текущую базу
   SET NOCOUNT ON; -- отключаем вывод количества возвращаемых строк, это несколько ускорит обработку
   DECLARE @objectid int; -- ID объекта
   DECLARE @indexid int; -- ID индекса
   DECLARE @partitioncount bigint; -- количество секций если индекс секционирован
   DECLARE @schemaname nvarchar(130); -- имя схемы в которой находится таблица
   DECLARE @objectname nvarchar(130); -- имя таблицы 
   DECLARE @indexname nvarchar(130); -- имя индекса
   DECLARE @partitionnum bigint; -- номер секции
   DECLARE @frag float; -- процент фрагментации индекса
   DECLARE @command nvarchar(4000); -- инструкция T-SQL для дефрагментации либо ренидексации
   
   -- Отбор таблиц и индексов с помощью системного представления sys.dm_db_index_physical_stats
   -- Отбор только тех объектов которые являются индексами (index_id > 0), 
   -- фрагментация которых более 10% и количество страниц в индексе более 128
   SELECT
       object_id AS objectid,
       index_id AS indexid,
       partition_number AS partitionnum,
       avg_fragmentation_in_percent AS frag
   INTO #work_to_do
   FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
   WHERE avg_fragmentation_in_percent > 5.0 AND index_id > 0 AND page_count > 128;
   
   -- Объявление курсора для чтения секций
   DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
   
   -- Открытие курсора
   OPEN partitions;
   
   -- Цикл по секциям
   WHILE (1=1)
       BEGIN;
           FETCH NEXT
              FROM partitions
              INTO @objectid, @indexid, @partitionnum, @frag;
           IF @@FETCH_STATUS < 0 BREAK;
   		
   -- Собираем имена объектов по 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 = @objectid;
           SELECT @indexname = QUOTENAME(name)
           FROM sys.indexes
           WHERE  object_id = @objectid AND index_id = @indexid;
           SELECT @partitioncount = count (*)
           FROM sys.partitions
           WHERE object_id = @objectid AND index_id = @indexid;
   
   -- Если фрагментация менее или равна 30% тогда дефрагментация, иначе реиндексация
           IF @frag <= 30.0
               SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE';
           IF @frag > 30.0
               SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD';
           IF @partitioncount > 1
               SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS nvarchar(10));
   			
   -- Если реиндексация, то для ускорения добавляем параметры использования TEMPDB(имеет смысл только если TempDB на отдельном физ. диске) и многопроцессорной обработки 
   			IF @frag > 30.0
   			SET @command = @command + N' WITH (SORT_IN_TEMPDB = ON, MAXDOP = 0)';	
           EXEC (@command);
           PRINT N'Executed: ' + @command;
       END;
   
   -- Закрытие курсора
   CLOSE partitions;
   DEALLOCATE partitions;
   
   -- Удаление временной таблицы
   DROP TABLE #work_to_do;
   GO