Настройка плана обслуживания базы 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

   
   
   

3. Ежедневный бэкап
   
   

   Open