Анализ фрагментации индексов
DECLARE @db_id smallint
DECLARE @object_id int
SET @db_id = DB_ID ( N 'AdventureWorks' )
SET @object_id = OBJECT_ID ( N 'AdventureWorks.Person.Address' )
IF @db_id IS NULL
BEGIN
PRINT N 'Invalid database'
END
ELSE IF @object_id IS NULL
BEGIN
PRINT N 'Invalid object'
END
SELECT *
FROM sys.dm_db_index_physical_stats
(@db_id
, @object_id
, NULL
, NULL
, NULL)
DBCC SHOWCONTIG ( [AdventureWorks.Person.Address] )
DBCC SHOWCONTIG scanning [AdventureWorks.Person.Address] table...
Table:[AdventureWorks.Person.Address] (962102468); index ID: 1, database ID: 7
TABLE level scan performed.
-Pages Scanned................................: 899
-Extents Scanned..............................: 121
-Extent Switches..............................: 897
-Avg. Pages per Extent........................: 7.4
-Scan Density [Best Count:Actual Count].......: 12.58% [113:898]
-Logical Scan Fragmentation ..................: 99.89%
-Extent Scan Fragmentation ...................: 80.99%
-Avg. Bytes Free per Page.....................: 2606.5
-Avg. Page Density (full).....................: 67.80%
Pages Scanned – Количество физических страницы базы данных, принадлежащих этому индексу. Дает Вам полный размер занятых этим индексом страниц (каждая страница - 8КБ);
Extents scanned - экстент - 8 страниц. Pages Scanned / 8. В этом примере мы имеем 121 экстентов, которые содержат 968 страниц. Так как индекс занимает только 899 страниц, мы делаем вывод, что ряд экстентов разделяемы между другими объектами. Это не всегда плохо, но это может натолкнуть Вас на мысль, что присутствует небольшая фрагментация данных. Конечно, Вы не знаете точный процент физической фрагментации, являющейся причиной увеличения времени исполнения запросов. Минимальное количество экстентов, которые могли бы быть заняты, для 899 страниц было бы 113 (899/8).
Extent Switches – Количество страниц, которые имеют связи со следующей, расположенной в другом экстенте страницей. Когда это значение близко к общему количеству страниц, Вы имеете довольно высокую фрагментацию. Вам нужно перестроить кластерный индекс.
Average Pages / Extent – Арифметическое деление Pages Scanned / Extents Scanned . Если бы экстенты были полностью заполненные, в результате было бы 8.
Scan Density [Best Count:Actual Count].......: 12.58% [113:898] - Показывает процент идеальности размещения страниц. В идеале должно было быть 113, но мы имеем 898, что составляет 12.58% от идеального. Здесь мы имеем обратное представление фрагментации. Чем ближе результат к 100%, тем меньше фрагментация. Вы можете видеть, что эта таблица довольно сильно фрагментирована. Сканирование постоянно использует переключение вперед и назад от одного экстента к другому, вместо использования только ссылки одной страницы на следующую в пределах экстента.
Logical Scan Fragmentation ..................: 99.89 % - Процент страниц в индексе, которые имеют указатель на следующую страницу, которая не является следующей страницей в последовательности расположения листьев. Этот процент имеет смысл только для кластерных индексов, поскольку данные (страницы листьев) должны быть физически размещены в порядке кластерного индекса. Для OLAP систем желательно низкое значение этого параметра, в OLTP системах приемлемо среднее значение.
Extent Scan Fragmentation ...................: 80.99 % - Показывает процент страниц, для которых следующая страница в индексе не расположена рядом (физически) с текущей страницей. Это говорит о том, что система I / O будет загружена на 80 % времени просмотра индекса, для поиска следующей страницы.
Avg. Bytes Free per Page.....................: 2606.5 - Среднее число байт, свободных на странице. Так как страница - 8096 байтов, для нашего примера заполнены приблизительно 68 % страниц. Это может быть как хорошо, так и плохо. Если это OLTP система с частыми вставками в таблицу, то большое количество свободного пространства в странице снижает вероятность разбиения страниц, что может существенно увеличить периодичность перестройки индексов. Если это OLAP система, то предпочтительнее иметь этот значение ближе к нолю, так как здесь желательно при чтении захватывать настолько большое количество данных, насколько это возможно для каждой страницы (чтобы уменьшить время сканирования индекса).
Avg . Page Density ( full ).....................: 67.80% - Это процентное значение основано на результате предыдущего параметра 2606.5 и разделённого на 8096 (размер страницы). Большой процент говорит о высокой фрагментации. Т.е. имеется большое количество переключений между экстентами, что вызывает большую утилизацию I / O при использовании индекса.
Как избежать фрагментации таблиц