24 октября 2012 в 13:15

Дефрагментация индексов со сбором статистики MS SQL 2008 R2 из песочницы

Одна из первых задач, которая возникает перед DBA после развертывания новой БД — это настройка планов по ее обслуживанию. Зачастую, в план обслуживания включается задача по дефрагментации индексов. Мне нравится, когда я знаю не только то, что дефрагментация выполнилась ночью с воскресенья на понедельник, но и то, как она прошла, сколько выполнялась, какие индексы были перестроены и в каком состоянии они остались после дефрагментации.

Для сбора такой статистики мною был написан небольшой скриптик, который собирает информацию о выполненной работе, а так же дает максимально подробное описание
о состоянии индексов до и после проделанной процедуры.

Но начнем с простого, создадим таблицу для хранения этих самых данных (я создал отдельную БД, куда складываю таблицы, которыми пользуюсь во время обслуживания баз данных сервера):
Столбец Тип Комментарий
proc_id int Порядковый номер процедуры, для идентификации
start_time datetime Начало выполнения запроса ALTER INDEX
end_time datetime Завершение выполнения запроса ALTER INDEX
database_id smallint Идентификатор БД
object_id Int Идентификатор таблицы
table_name varchar(50) Имя таблицы
index_id Int Идентификатор индекса
index_name varchar(50) Имя индекса
avg_frag_percent_before float Процент фрагментации индекса перед выполнением ALTER INDEX
fragment_count_before bigint Количество фрагментов до дефрагментации
pages_count_before bigint Количество страниц индекса до дефргаментации
fill_factor tinyint Уровень заполнения страниц индекса
partition_num int Номер секции
avg_frag_percent_after float Процент фрагментации индекса после выолнения ALTER INDEX
fragment_count_after bigint Количество фрагментов после дефрагментации
pages_count_after bigint Количество страниц индекса после дефргаментации
action varchar(10) Выполняемое действие

Вся процедура дефрагментации, будет брать данные из этой таблицы, а значит, надо ее заполнить:
DECLARE @currentProcID INT –-Порядковый номер процедуры дефрагментации
 –-Выбираем последний номер, и просто добавляем единичку
SELECT @currentProcID = ISNULL(MAX(proc_id), 0) + 1 FROM dba_tasks.dbo.index_defrag_statistic
--И заполняем таблицу данными о состоянии индексов
INSERT INTO dba_tasks.dbo.index_defrag_statistic (
	proc_id, 
	database_id, 
	[object_id], 
	table_name, 
	index_id, 
	index_name, 
	avg_frag_percent_before, 
	fragment_count_before, 
	pages_count_before, 
	fill_factor, 
	partition_num)
SELECT 
	@currentProcID, 
	dm.database_id, 
	dm.[object_id], 
	tbl.name, 
	dm.index_id, 
	idx.name, 
	dm.avg_fragmentation_in_percent, 
	dm.fragment_count, 
	dm.page_count, 
	idx.fill_factor, 
	dm.partition_number
FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) dm
	INNER JOIN sys.tables tbl ON dm.object_id = tbl.object_id
	INNER JOIN sys.indexes idx ON dm.object_id = idx.object_id AND dm.index_id = idx.index_id
WHERE page_count > 8
	AND avg_fragmentation_in_percent > 10
	AND dm.index_id > 0

Условия выборки:
page_count > 8 – я считаю, что перестраивать индексы с малым количеством страниц не имеет смысла, т.к. лучше не станет, а время, затраченное на выполнение процедуры — очень ценно, особенно если база работает круглосуточно и постоянно находится под высокой нагрузкой. (После замечания unfilled поднял планку до 8 страниц)
avg_fragmentation_in_percent > 10 – Тоже очень субъективная цифра, практически во всей документации предлагают не трогать индекс, если его фрагментация составляет 10 или менее процентов, с чем я согласен, если у вас дела обстоят по другому, меняем.
dm.index_id > 0 – 0 – это куча

После того, как таблица заполнена, нам известно какие индексы необходимо обслужить.
Займемся делом:
--Обьявим необходимые переменные
DECLARE @partitioncount INT --Количество секций
DECLARE @action VARCHAR(10) --Действие, которые мы будем делать с индексом
DECLARE @start_time DATETIME --Начало выполнения запроса ALTER INDEX
DECLARE @end_time DATETIME --Конец выполнения запроса ALTER INDEX
--см описание таблицы
DECLARE @object_id INT 
DECLARE @index_id INT
DECLARE @tableName VARCHAR(250) 
DECLARE @indexName VARCHAR(250)
DECLARE @defrag FLOAT
DECLARE @partition_num INT
DECLARE @fill_factor INT
--Сам запрос, который мы будем выполнять, я поставил MAX, потому как иногда меняю такие скрипты, и забываю поправить размер данной переменной, в результате получаю ошибку.
DECLARE @sql NVARCHAR(MAX)

--Далее объявляем курсор
DECLARE defragCur CURSOR FOR
	SELECT 
		[object_id], 
		index_id, 
		table_name, 
		index_name, 
		avg_frag_percent_before, 
		fill_factor, 
		partition_num 
	FROM dba_tasks.dbo.index_defrag_statistic
	WHERE proc_id = @currentProcID
	ORDER BY [object_id], index_id DESC --Сначала не кластерные индексы

OPEN defragCur
FETCH NEXT FROM defragCur INTO @object_id, @index_id, @tableName, @indexName, @defrag, @fill_factor, @partition_num
WHILE @@FETCH_STATUS=0
BEGIN
	SET @sql = N'ALTER INDEX ' + @indexName + ' ON ' + @tableName

	SELECT @partitioncount = count (*)
	FROM sys.partitions
	WHERE object_id = @object_id AND index_id = @index_id;
	
	--В моем случае, важно держать неможко пустого места на страницах, потому, что вставка в тоже таблицы имеете место, и не хочеться тратить драгоценное время пользователей на разбиение страниц
	IF (@fill_factor != 80)
	BEGIN
		@sql = @sql + N' REBUILD WITH (FILLFACTOR = 80)'
		SET @action = 'rebuild80'
	END
	ELSE
	BEGIN --Тут все просто, действуем по рекомендации MS
		IF (@defrag > 30) --Если фрагментация больше 30%, делаем REBUILD
		BEGIN
			SET @sql = @sql + N' REBUILD'
			SET @action = 'rebuild'
		END
		ELSE --В противном случае REORGINIZE
		BEGIN
			SET @sql = @sql + N' REORGANIZE'
			SET @action = 'reorginize'
		END
	END
	
	--Если есть несколько секций
	IF @partitioncount > 1
		SET @sql = @sql + N' PARTITION=' + CAST(@partition_num AS nvarchar(5))
	
	print @sql --Вывод выполняемого запроса
	
	--Фиксируем время старта
	SET @start_time = GETDATE()
	EXEC sp_executesql @sql
	--И время завершения
	SET @end_time = GETDATE()
	
	--Сохраняем время в таблицу
	UPDATE dba_tasks.dbo.index_defrag_statistic
	SET 
		start_time = @start_time,
		end_time = @end_time,
		[action] = @action
	WHERE proc_id = @currentProcID
		AND [object_id] = @object_id
		AND index_id = @index_id
	
	FETCH NEXT FROM defragCur INTO @object_id, @index_id, @tableName, @indexName, @defrag, @fill_factor, @partition_num
END
CLOSE defragCur
DEALLOCATE defragCur


Ну и на последок, соберем информацию о индексах после процедуры дефрагментации:
UPDATE dba
SET
	dba.avg_frag_percent_after = dm.avg_fragmentation_in_percent,
	dba.fragment_count_after = dm.fragment_count,
	dba.pages_count_after = dm.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), null, null, null, null) dm
	INNER JOIN dba_tasks.dbo.index_defrag_statistic dba 
		ON dm.[object_id] = dba.[object_id] 
			AND dm.index_id = dba.index_id
WHERE dba.proc_id = @currentProcID
	AND dm.index_id > 0


После выполнения такого скрипта, можно получить и посчитать очень много полезной информации. Например, время обслуживания всех индексов и каждого отдельно. Понять как это связано с размером индекса, увидеть эффективность данной операции. Собрав такую информацию за несколько раз, можно немного поменять процедуру, наверняка какие-то индексы фрагментируются больше и быстрее. В таком случае их обслуживание следует выполнять чаще. Как воспользоваться полученной информацией, решайте сами. Что же касается меня, то я, после анализа каждой такой процедуры, меняю планы обслуживания, если того требует ситуация. Мои базы работают под высокой нагрузкой круглосуточно. Поэтому постоянно перестраивать все индексы и на 1-2 часа снижать производительность сервера я не могу. Если Ваша БД, тоже работает круглосуточно, для выполнения таких вещей, следует настроить Resource Governor.

Хочу так-же заметить, что в инете есть подробные скрипты, но будьте бдительны, многие из них пользуются устаревшими командами.

Более подробно об используемых мною системных представлениях можно почитать в msdn:

sys.sysindexes
sys.tables
sys.dm_db_index_physical_stats
@sp00n
карма
4,0
рейтинг 0,0
Самое читаемое Разработка

Комментарии (10)

  • 0
    Спасибо. Вовремя, как раз искала что-то подобное. В закладки :)
  • 0
    Да, по-любому в закладки. Попозже попробую у себя
  • 0
    thx
  • +1
    ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    Также есть самописный скрипт на павершеле, который отправляет результат на почту.
  • 0
    Я может быть сплю ещё, но вот курсор смущает:
    DECLARE defragCur CURSOR FOR
        SELECT 
            [object_id], 
            index_id, 
            table_name, 
            index_name, 
            avg_frag_percent_before, 
            fill_factor, 
            partition_num 
        FROM dba_tasks.dbo.index_defrag_statistic
        ORDER BY [object_id], index_id DESC --Сначала не кластерные индексы
    


    Вы не накладываете отбор по @currentProcID, соответственно, в результате выполнения запроса вы получите вообще все записи из index_defrag_statistics и, следовательно, будете дефрагментировать/перестраивать все индексы, когда либо записанные в неё. Причём вполне возможна ситуация когда один и тот же индекс сначала перестроится, потом дефрагментируется, потом снова перестроится и т.д.
    Если вы перед запуском этой процедуры очищаете таблицу — то зачем вам @currentProcID?

    Ну и вообще — вопрос с дефргаментацией/перестройкой индексов достаточно сложный. Не понятна «полезность» этой процедуры. Ну точнее полезность понятна — индексы становятся ровные, красивые и т.д. Но, я ни разу не видел СЕРЬЁЗНЫХ проблем с производительностью, вызванных фрагментированием индексов.

    Степень фрагментации индекса никак не учитывается оптимизатором, соответственно и не влияет на план запроса. Фактически, если используется поиск по индексу (INDEX SEEK) — степень фрагментации никак не повлияет на выполнение запроса — головка и так будет носиться по диску, выцепляя отдельные страницы индекса. Если же при выполнении запроса используется INDEX SCAN на очень большой таблице — вот тут может быть проблема из-за того, что SQL Server не сможет читать данные последовательно большими кусками. Но такие запросы и так достаточно медлительны и фрагментированность индекса на производительность, в итоге, влияет не сильно.

    Так же, бывает, что запрос «тормозит», админ выполняет перестройку (ALTER INDEX REBUILD) индекса и запрос начинает летать — тут дело не столько в самой перестройке индекса, сколько в попутном обновлении статистики, которое почему-то часто забывают — как и автор топика, не добавил её в скрипт обслуживания индексов, что, имхо, неправильно.
    • 0
      И ещё вот момент увидел:
      page_count > 5 – я считаю, что перестраивать индексы с малым количеством страниц не имеет смысла
      Перестраивать индексы с малым количеством страниц действительно не имеет смысла, только границу надо поднять как минимум до 8. До тех пор пока индекс содержит меньше 8 страниц, он размещается в смешанных экстентах и его дефрагментация/перестройка может не принести желаемого результата (msdn).
      Я для себя выбрал границу в 128 страниц, т.е. только те, которые «весят» больше мегабайта. Сейчас вот правда задумался — не поднять ли планку ещё :).
    • +1
      Условие по proc_id поставил, спасибо за ценное замечание.
      Что касается полезности этой процедуры, то даже с теоретической точки зрения она полезна (с практической же, ее полезность бывает разной, я согласен), ведь уменьшая фрагментацию, мы увеличиваем плотность данных на страницах, значит страниц надо будет прочесть меньше, меньше работа диска.
      Про статистику я не забыл, msdn утверждает, что обновление статистики произойдет автоматически, если после rebuild мы явно не напишем STATISTICS_NORECOMPUTE = ON, я явно не писал.
      • 0
        уменьшая фрагментацию, мы увеличиваем плотность данных на страницах

        Из-за fill factor = 80 вы местами плотность данных не увеличиваете, а уменьшаете :). (это я занудствую, в общем-то, вы же писали зачем это делаете, но кто-то может удивиться, увидев, что после процедур, направленных на увеличение плотности данных, файл данных вырастает)

        Про статистику я не забыл, msdn утверждает, что обновление статистики произойдет автоматически, если после rebuild мы явно не напишем STATISTICS_NORECOMPUTE = ON, я явно не писал

        И это верно, но статистика обновится только по тем индексам, для которых был сделан REBUILD. Для индекса, «подвергшегося» дефрагментации (REORGANIZE), статистика не обновится. И также не обновится статистика, созданная SQL Server'ом автоматически.
        • 0
          Из-за fill factor = 80 вы местами плотность данных не увеличиваете, а уменьшаете :). (это я занудствую, в общем-то, вы же писали зачем это делаете, но кто-то может удивиться, увидев, что после процедур, направленных на увеличение плотности данных, файл данных вырастает)
          Я думаю, из этого можно раздуть холивар, но дефрагментацию делать надо, почему бы не следить за тем как она делается, и получать информацию о состоянии индексов :)
          И это верно, но статистика обновится только по тем индексам, для которых был сделан REBUILD. Для индекса, «подвергшегося» дефрагментации (REORGANIZE), статистика не обновится. И также не обновится статистика, созданная SQL Server'ом автоматически.
          Согласен. Но я не ставил перед собой цель описать весь план обслуживания БД, статья касается только индексов. А статистика у меня обновляется отдельным шагом.
  • 0
    Небольшое замечание — при пересоздании таблицы, впрочем, как и любого другого объекта — меняется его object_id.
    Попробуйте несколько раз выполнить этот скрипт, чтобы увидеть описанную ситуацию:

    IF OBJECT_ID('dbo.test1', 'U') IS NOT NULL
    	DROP TABLE dbo.test1
    GO
    
    CREATE TABLE dbo.test1
    (
    	ID INT,
    	CONSTRAINT PK_test1_ID PRIMARY KEY (ID)
    )
    GO
    
    SELECT OBJECT_ID('dbo.test1')
    


    Поэтому, во избежание коллизий, желательно привязываться не к object_id, а к полному имени объекта (схема + имя).

Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.