March 2008

You are currently browsing the monthly archive for March 2008.

В электронной документации к SQL Server 2005 описано замечательное динамическое административное представление sys.dm_db_index_physical_stats. Описание сопровождается примерами использования, один из которых (в русской редакции страницы это пример “Г”, а в английской “D”) предлагает метод и правила автоматизации операций дефрагментации индексов в базе данных. Суть метода в том, что если значение avg_fragmentation_in_percent находиться в диапазоне от 10 до 30, то в инструкции ALTER INDEX используется ключевое слово REORGANIZE, а если значение больше 30, то используется ключевое слово REBUILD. Однако, существует и другой алгоритм выбора метода дефрагментации, который подробно изложен в книге: “ Microsoft SQL Server 2005. Реализация и обслуживание. Учебный курс Microsoft“. Вот выдержка из этой книги, со страницы 368: “Исполняйте инструкцию ALTER INDEX … REORGANIZE, чтобы выполнить дефрагментацию индекса, при соблюдении следующих пороговых значений: значение avg_page_space_used_in_percent в диапазоне от 60 до 75 или значение avg_fragmentation_in_percent в диапазоне от 10 до 15. Исполняйте инструкцию ALTER INDEX … REBUILD, чтобы выполнить дефрагментацию индекса, при соблюдении следующих пороговых значений: значение avg_page_space_used_in_percent меньше 60 или значение avg_fragmentation_in_percent больше 15.“.

Ниже представлен сценарий, демонстрирующий в упрощённом виде этот алгоритм. В этом сценарии кластеризованные и не кластеризованные индексы анализируются отдельно, если для таблицы необходима дефрагментация кластеризованного индекса, её некластеризованные индексы отбрасываются. Кроме того, не анализируются маленькие индексы, занимающие меньше дюжины страниц.

SELECT	'ALTER INDEX ALL ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON);' AS [Инструкция T-SQL]
FROM	sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS afp
WHERE	afp.database_id = DB_ID()
AND	afp.index_type_desc IN ('CLUSTERED INDEX')
AND	(afp.avg_fragmentation_in_percent >= 15 OR afp.avg_page_space_used_in_percent <= 60)
AND	afp.page_count > 12
UNION ALL
SELECT	[Инструкция T-SQL] = 
	CASE 
		WHEN afp.avg_fragmentation_in_percent >= 15 
		OR afp.avg_page_space_used_in_percent <= 60
		THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON);' 
		WHEN (afp.avg_fragmentation_in_percent < 15 AND afp.avg_fragmentation_in_percent >= 10)
		OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
		THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REORGANIZE;'
	END
FROM	sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') AS afp
JOIN	sys.indexes AS i 
ON	(afp.OBJECT_ID = i.OBJECT_ID AND afp.index_id = i.index_id)
AND	afp.database_id = DB_ID()
AND	afp.index_type_desc IN ('NONCLUSTERED INDEX')
AND	(
		(afp.avg_fragmentation_in_percent >= 10 AND	afp.avg_fragmentation_in_percent < 15)
	OR	(afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
	)
AND	afp.page_count > 12
AND	afp.OBJECT_ID NOT IN	(	
					SELECT	OBJECT_ID 
					FROM	sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'SAMPLED') 
					WHERE	database_id = DB_ID()
					AND	index_type_desc IN ('CLUSTERED INDEX')
					AND	(avg_fragmentation_in_percent >= 15 OR avg_page_space_used_in_percent < 60)
					AND	page_count > 1
				)
ORDER BY [Инструкция T-SQL]

Бонус

-- INDEX REBUILD or REORGANIZE
DECLARE @SQL varchar(256), @DB_ID int;
SET @DB_ID = (SELECT DB_ID());

DECLARE reindex CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR
SELECT	'ALTER INDEX ALL ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (SORT_IN_TEMPDB = ON);' AS [Инструкция T-SQL]
FROM	sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL, NULL, 'SAMPLED') AS afp
WHERE	afp.database_id = @DB_ID
AND	afp.index_type_desc IN ('CLUSTERED INDEX')
AND	(afp.avg_fragmentation_in_percent >= 15 OR afp.avg_page_space_used_in_percent <= 60)
AND	afp.page_count > 12
UNION ALL
SELECT	[Инструкция T-SQL] = 
	CASE 
		WHEN afp.avg_fragmentation_in_percent >= 15 
		OR afp.avg_page_space_used_in_percent <= 60
		THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (SORT_IN_TEMPDB = ON);' 
		WHEN (afp.avg_fragmentation_in_percent < 15 AND afp.avg_fragmentation_in_percent >= 10)
		OR (afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
		THEN 'ALTER INDEX [' + i.name + '] ON [' + OBJECT_NAME(afp.OBJECT_ID) + '] REORGANIZE;'
	END
FROM	sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL, NULL, 'SAMPLED') AS afp
JOIN	sys.indexes AS i 
ON	(afp.OBJECT_ID = i.OBJECT_ID AND afp.index_id = i.index_id)
AND	afp.database_id = @DB_ID
AND	afp.index_type_desc IN ('NONCLUSTERED INDEX')
AND	(
		(afp.avg_fragmentation_in_percent >= 10 AND	afp.avg_fragmentation_in_percent < 15)
	OR	(afp.avg_page_space_used_in_percent > 60 AND afp.avg_page_space_used_in_percent < 75)
	)
AND	afp.page_count > 12
AND	afp.OBJECT_ID NOT IN	(	
					SELECT	OBJECT_ID 
					FROM	sys.dm_db_index_physical_stats (@DB_ID, NULL, NULL, NULL, 'SAMPLED') 
					WHERE	database_id = @DB_ID
					AND	index_type_desc IN ('CLUSTERED INDEX')
					AND	(avg_fragmentation_in_percent >= 15 OR avg_page_space_used_in_percent < 60)
					AND	page_count > 1
				)
ORDER BY [Инструкция T-SQL]

OPEN GLOBAL reindex
WHILE 1 = 1
BEGIN
	FETCH reindex INTO @SQL
	IF @@fetch_status <> 0 BREAK
--	EXEC(@SQL)
PRINT @SQL
END
CLOSE GLOBAL reindex
DEALLOCATE reindex