Tips for DBA: Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes (no partitions / SQL Server 2005)

В электронной документации к 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

Published Mon, Mar 31 2008 8:31 by gladchenko
Filed under: ,

Comments

# re: Tips for DBA: Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes (no partitions / SQL Server 2005)

Ещё одно, комплексное решение, учитывающее наличие LOB-полей: "SQL Server 2005 - Backup, Integrity Check and Index Optimization" ( blog.ola.hallengren.com/.../3440068.html )

Tuesday, April 01, 2008 1:10 AM by gladchenko

# re: Tips for DBA: Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes (no partitions / SQL Server 2005)

Для работы dm_db_index_physical_stats уровень совместимости базы данных должен быть не ниже 90.

Thursday, April 03, 2008 7:23 AM by gladchenko

# re: Tips for DBA: Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes (no partitions / SQL Server 2005)

Александр, а есть ли толк в дефрагментации не кластеризованных индексов? ИМХО нет, можно оставить только кластеризованные.

И ещё, для работы в режиме совместимости 80, вместо DB_ID() достаточно задать её через переменную, например

DECLARE @DB_ID int;

SET @DB_ID = (SELECT DB_ID());

И дальше вместо DB_ID() использовать @DB_ID.

Monday, April 21, 2008 7:34 AM by ЧайникПолный

# re: Tips for DBA: Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes (no partitions / SQL Server 2005)

Дефрагментировать стоит любую материализуемую структуру :) Однако, если дефрагментируется кластеризованный индекс, это приводит к автоматическому пересозданию остальных индексов таблицы. Представленный тут сценарий это учитывает, и не затрагивает некластеризованные индексы, если дефрагментируется кластерный индекс.

Monday, April 21, 2008 9:05 AM by gladchenko

# re: Tips for DBA: Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes (no partitions / SQL Server 2005)

Александр, я конечно могу ошибаться...

>Дефрагментировать стоит любую материализуемую структуру :-)

Но, при перестроении индекса в куче (таблица, не имеющая кластеризованного индекса)фрагментация остаёться практически такой-же.

Здесь единственное (по крайней мере пока для меня единственное извесное) это создать в куче кластерный индекс и потом сразу его удалить, при создании он упорядочит данные и фрагментация уменьшиться.

Если база большая  ( > несколько сотен Гб), операции с индексами займут продолжительное время, во время перестройки индексов огромные очереди на дисках, работа базы сильно замедляеться, а если система 24/7 это не есть гуд, ИМХО лучше не тратить драгоценное время на перестроение некластерных индексов.

Tuesday, April 22, 2008 7:13 AM by ЧайникПолный

# re: Tips for DBA: Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes (no partitions / SQL Server 2005)

Дефрагментируя не кластерезованный индекс кучи, вы, тем самым, дефрагментируете страницы индекса. Данные затронуты не будут. Если индекс покрывающий, или селективность выборки высока, эффект от дефрагментации будет ощутим.

Размещение файлов и объектов среди файловых групп для больших баз данных нужно планировать так, чтобы нагрузка обслуживания индексов не оказывала чрезмерного воздействия на работу пользователей. Разумеется, для систем с поддержкой 365x24x7 необходимо учитывать требования высокой доступности, и использовать соответствующие решения. Реально, для этого нужен SQL Server 2005 Enterprise Edition, а новая версия  SQL Server 2008 привнесёт ещё больше свободы.

Кстати, для больших таблиц как раз выгоднее наличие короткого кластеризованного индекса, он поможет сгруппировать данные так, как выгодно с точки зрения производительной выборки… хотя бы заменит поиск – поиском по диапазону ключа ;)

Tuesday, April 22, 2008 7:56 AM by gladchenko

# re: Tips for DBA: Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes (no partitions / SQL Server 2005)

> Однако, если дефрагментируется кластеризованный индекс, это приводит к автоматическому пересозданию остальных индексов таблицы

небольшая поправочка: не приводит

www.sqlskills.com/.../SearchEngineQA19MisconceptionsAroundIndexRebuildsAllocationBULKLOGGEDModeLocking.aspx

Monday, July 21, 2008 1:13 PM by vgoncharenko

# re: Tips for DBA: Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes (no partitions / SQL Server 2005)

SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW
DECLARE @SQL nvarchar(2048), @db int, @@SQL2 nvarchar(max), @ErrorID int
SET @@SQL2 = 'DECLARE @SQL nvarchar(2048);
'
DECLARE usersdatabases CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR
SELECT database_id FROM sys.databases
WHERE (CAST(case when name in ('master','model','msdb','tempdb') then 1 else is_distributor end AS bit)=0
AND CAST(isnull(source_database_id, 0) AS bit)=0)
ORDER BY [name]

OPEN GLOBAL usersdatabases
WHILE 1 = 1
BEGIN
 FETCH usersdatabases INTO @db
 IF @@fetch_status <> 0 BREAK
 SET @@SQL2 = @@SQL2 + '
  USE ' + DB_NAME(@db) + ';
  DECLARE reindex' + CAST(@db AS nvarchar) + ' CURSOR GLOBAL FAST_FORWARD READ_ONLY FOR
  SELECT ''ALTER INDEX ALL ON ['' + DB_NAME(' + CAST(@db AS nvarchar) + ')+ '']..['' + OBJECT_NAME(afp.[OBJECT_ID]) + ''] REBUILD WITH (SORT_IN_TEMPDB = ON);'' AS [Инструкция T-SQL]
  FROM ' + DB_NAME(@db) + '.sys.dm_db_index_physical_stats (' + CAST(@db AS nvarchar) + ', NULL, NULL, NULL, ''SAMPLED'') AS afp
  WHERE afp.database_id = ' + CAST(@db AS nvarchar) + '
  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 ['' +  DB_NAME(' + CAST(@db AS nvarchar) + ') + '']..['' + 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 ['' +  DB_NAME(' + CAST(@db AS nvarchar) + ')  + '']..['' + OBJECT_NAME(afp.[OBJECT_ID]) + ''] REORGANIZE;''
   END
  FROM ' + DB_NAME(@db) + '.sys.dm_db_index_physical_stats (' + CAST(@db AS nvarchar) + ', NULL, NULL, NULL, ''SAMPLED'') AS afp
  JOIN ' + DB_NAME(@db) + '.sys.indexes AS i
  ON (afp.[OBJECT_ID] = i.[OBJECT_ID] AND afp.index_id = i.index_id)
  AND afp.database_id = ' + CAST(@db AS nvarchar) + '
  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 ' + DB_NAME(@db) + '.sys.dm_db_index_physical_stats (' + CAST(@db AS nvarchar) + ', NULL, NULL, NULL, ''SAMPLED'')
       WHERE database_id = ' + CAST(@db AS nvarchar) + '
       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]'

  SET @@SQL2 = @@SQL2 + '
  OPEN GLOBAL reindex' + CAST(@db AS nvarchar) + '
  WHILE 1 = 1
  BEGIN
   FETCH reindex' + CAST(@db AS nvarchar) + ' INTO @SQL
   IF @@fetch_status <> 0 BREAK
   EXEC(@SQL)
   PRINT @SQL
  END
  CLOSE GLOBAL reindex' + CAST(@db AS nvarchar) + '
  DEALLOCATE GLOBAL reindex' + CAST(@db AS nvarchar) + ''

--PRINT @db
END
CLOSE GLOBAL usersdatabases
DEALLOCATE usersdatabases
DECLARE @SQL1 nvarchar(500)
--PRINT @@SQL2
EXECUTE master..sp_executesql
    @@SQL2
    ,N'@SQL1 nvarchar(2048) OUTPUT'
    ,@SQL1 = @SQL1 OUTPUT
--PRINT('----------------------')
--PRINT @SQL1
GO

Thursday, November 13, 2008 11:04 AM by gladchenko

# re: Tips for DBA: Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes (no partitions / SQL Server 2005)

Ещё один сценарий: weblogs.asp.net/.../6819737.aspx

Monday, January 12, 2009 12:37 PM by gladchenko

# re: Tips for DBA: Using sys.dm_db_index_physical_stats in a script to rebuild or reorganize indexes (no partitions / SQL Server 2005)

Изменённый сценарий, который учитывает схемы, присланный Ириной Наумовой:

 SELECT      'ALTER INDEX ALL ON [' + OBJECT_SCHEMA_NAME (afp.OBJECT_ID)+'].['+OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (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_SCHEMA_NAME (afp.OBJECT_ID)+'].['+ OBJECT_NAME(afp.OBJECT_ID) + '] REBUILD WITH (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_SCHEMA_NAME (afp.OBJECT_ID)+'].['+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]

 

Friday, May 06, 2011 1:33 AM by gladchenko

Leave a Comment

(required) 
(required) 
(optional)
(required) 
If you can't read this number refresh your screen
Enter the numbers above: