Реорганизация и перестроение индексов

Статья
12/15/2008

Изменения: 17 июля 2006 г.

SQL Server 2005 Database Engine автоматически поддерживает состояние индексов при выполнении операций вставки, обновления или удаления в отношении базовых данных. Со временем эти изменения могут привести к тому, что данные в индексе окажутся разбросанными по базе данных (фрагментированными). Фрагментация имеет место в тех случаях, когда в индексах содержатся страницы, для которых логический порядок, основанный на значении ключа, не совпадает с физическим порядком в файле данных. Значительно фрагментированные индексы могут серьезно снижать производительность запросов и служить причиной замедления откликов приложения. Дополнительные сведения см. на веб-узле корпорации Майкрософт.

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

Выявление фрагментации

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

Алгоритм определения фрагментации SQL Server 2005 точнее, чем в SQL Server 2000. В результате этого значения фрагментации будут выше. Например, в SQL Server 2000 таблица не считается фрагментированной, если ее страницы 11 и 13 содержатся в одном экстенте, а страница 12 — нет. Однако доступ к этим двум страницам потребует выполнения двух физических операций ввода-вывода, поэтому в SQL Server 2005 это считается фрагментацией.

Результирующий набор, возвращаемый функцией sys.dm_db_index_physical_stats, включает следующие столбцы:

Столбец	Описание
avg_fragmentation_in_percent	Процентная доля логической фрагментации (неупорядоченные страницы в индексе).
fragment_count	Число фрагментов (физически последовательные конечные страницы) в индексе.
avg_fragment_size_in_pages	Среднее число страниц в одном фрагменте индекса.

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

Значение avg_fragmentation_in_percent	Корректирующая инструкция
> 5 % и <= 30 %	ALTER INDEX REORGANIZE
> 30%	ALTER INDEX REBUILD WITH (ONLINE = ON)*

* Индекс может быть перестроен как в оперативном, так и в автономном режимах. Реорганизация индекса всегда выполняется в оперативном режиме. Чтобы добиться доступности, подобной варианту с реорганизацией, следует перестраивать индексы в оперативном режиме.

Такие значения являются примерным руководством для определения положения, в которое необходимо переключиться между ALTER INDEX REORGANIZE и ALTER INDEX REBUILD. Однако фактические значения могут различаться в каждом конкретном случае. Важно определить наилучшее пороговое значение для используемой среды экспериментальным путем.

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

Примечание.
Перестроение или реорганизация малых индексов часто не приводит к уменьшению фрагментации. Страницы индексов малого размера хранятся в смешанных экстентах. Смешанные экстенты могут совместно использоваться восемью объектами, поэтому фрагментация в небольшом индексе может не сократиться после реорганизации или перестроения индекса. Дополнительные сведения о смешанных экстентах см. в разделе Страницы и экстенты.

Перестроение или реорганизация малых индексов часто не приводит к уменьшению фрагментации. Страницы индексов малого размера хранятся в смешанных экстентах. Смешанные экстенты могут совместно использоваться восемью объектами, поэтому фрагментация в небольшом индексе может не сократиться после реорганизации или перестроения индекса. Дополнительные сведения о смешанных экстентах см. в разделе Страницы и экстенты.

Примеры

Следующий пример запрашивает через функцию динамического управления sys.dm_db_index_physical_stats среднюю фрагментацию для всех индексов в таблице Production.Product. В соответствии с предыдущей таблицей, рекомендуемым решением проблемы будет реорганизация PK_Product_ProductID и перестроение других индексов.

USE AdventureWorks;
GO
SELECT a.index_id, name, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID(N'Production.Product'),
     NULL, NULL, NULL) AS a
    JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id;
GO

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

index_id    name                        avg_fragmentation_in_percent
----------- --------------------------- ----------------------------
1           PK_Product_ProductID        15.076923076923077
2           AK_Product_ProductNumber    50.0
3           AK_Product_Name             66.666666666666657
4           AK_Product_rowguid          50.0

(4 row(s) affected)

Реорганизация индекса

Чтобы реорганизовать один или несколько индексов, используйте инструкцию ALTER INDEX с предложением REORGANIZE. Эта инструкция заменяет инструкцию DBCC INDEXDEFRAG. Чтобы реорганизовать отдельную секцию в секционированном индексе, используйте предложение PARTITION в инструкции ALTER INDEX.

Реорганизация индекса дефрагментирует конечный уровень кластеризованных и некластеризованных индексов по таблицам и представлениям, физически упорядочивая страницы концевого уровня в соответствии с логическим порядком (слева направо) концевых узлов. Расположение страниц в правильном порядке улучшает производительность сканирования индекса. Индекс реорганизуется в пределах выделенных для него страниц; новые страницы не выделяются. Если индекс состоит из более чем одного файла, файлы реорганизуются поочередно. Страницы не перемещаются между файлами.

Кроме того, реорганизация сжимает страницы индекса. Пустые страницы, возникшие в результате этого сжатия, удаляются, освобождая место на диске. Сжатие основывается на коэффициенте заполнения в представлении каталога sys.indexes.

Процесс реорганизации использует минимальные системные ресурсы. Кроме того, реорганизация автоматически осуществляется в оперативном режиме. Процесс не требует долговременных блокировок и потому не препятствует выполнению запросов или обновлений.

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

Сжатие типов данных больших объектов

При реорганизации одного или нескольких индексов типы данных больших объектов (LOB), содержащиеся в кластеризованном индексе или базовой таблице, подвергаются сжатию по умолчанию. Типы данных image, text, ntext, varchar(max), nvarchar(max), varbinary(max) и xml являются типами данных больших объектов. Сжатие этих данных может способствовать более эффективному использованию места на диске.

Реорганизация указанного кластеризованного индекса подвергает сжатию все столбцы типа LOB, содержащиеся на конечном уровне (строки данных) в кластеризованном индексе.
Реорганизация некластеризованного индекса подвергает сжатию все столбцы типа LOB, являющиеся неключевыми (включенными) столбцами в индексе.
При указании ALL реорганизуются все индексы, связанные с указанной таблицей или представлением, и подвергаются сжатию все столбцы LOB, связанные с кластеризованным индексом, базовой таблицей или некластеризованным индексом с включенными столбцами.
Предложение LOB_COMPACTION пропускается, если отсутствуют столбцы LOB.

Перестроение индекса

При перестроении индекса происходит сброс старого индекса и создание нового. Таким образом, фрагментация устраняется, высвобождается место на диске за счет сжатия страниц с использованием указанного или существующего коэффициента заполнения, а строки индекса переупорядочиваются в последовательных страницах (с выделением новых страниц по мере необходимости). Это может улучшить производительность диска посредством уменьшения числа обращений к страницам, необходимых для извлечения запрашиваемых данных.

Нижеследующие методы могут использоваться для перестроения кластеризованных и некластеризованных индексов.

Инструкция ALTER INDEX с предложением REBUILD. Эта инструкция заменяет инструкцию DBCC DBREINDEX.
Инструкция CREATE INDEX с предложением DROP_EXISTING.

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

Функциональная возможность	ALTER INDEX REBUILD	CREATE INDEX WITH DROP_EXISTING
Определение индекса может быть изменено при помощи добавления или удаления ключевых столбцов, изменения порядка столбцов или изменения порядка сортировки столбцов.*	Нет	Да**
Параметры индекса могут быть установлены или изменены.	Да	Да
Более одного индекса можно перестроить выполнением одной транзакции.	Да	Нет
Индексы большинства типов могут быть перестроены в оперативном режиме, не препятствуя выполнению запросов или обновлений.	Да	Да
Секционированный индекс может быть секционирован заново.	Нет	Да
Индекс может быть перемещен в другую файловую группу.	Нет	Да
Требуется дополнительное временное место на диске.	Да	Да
При перестроении кластеризованного индекса выполняется перестроение соответствующих некластеризованных индексов.	Нет Если не указано ключевое слово ALL.	Нет Если не изменено определение индекса.
Индексы, обеспечивающие ограничения PRIMARY KEY и UNIQUE, могут быть перестроены без удаления и воссоздания ограничений.	Да	Да
Отдельная секция индекса может быть перестроена.	Да	Нет

* Некластеризованный индекс может быть преобразован в индекс кластеризованного типа, если указать в его определении предложение CLUSTERED. Эта операция должна производиться с установленным для параметра ONLINE значением OFF. Преобразование кластеризованного индекса в некластеризованный не поддерживается вне зависимости от значения параметра ONLINE.

** Если индекс воссоздается с использованием того же имени, столбцов и порядка сортировки, то операция сортировки может быть пропущена. В процессе перестроения индекса обеспечивается сортировка строк.

Можно также перестроить индекс, сначала удалив индекс при помощи инструкции DROP INDEX, а затем воссоздав его отдельной инструкцией CREATE INDEX. Выполнение этих операций отдельными инструкциями имеет некоторые недостатки, и использовать этот вариант не рекомендуется.

Отключение некластеризованных индексов для экономии места на диске при выполнении операций перестроения

Когда некластеризованный индекс отключается, строки данных индекса удаляются, однако определение индекса сохраняется в метаданных. Индекс включается, когда завершается его перестроение. Если некластеризованный индекс не отключен, операция перестроения требует столько временного места на диске, сколько необходимо для хранения обоих индексов, старого и нового. Тем не менее в случае отключения и перестроения некластеризованного индекса отдельными транзакциями, место на диске, оказавшееся доступным за счет отключения индекса, может быть использовано для последующего перестроения или любой другой операции. Дополнительное место на диске не требуется, за исключением места, выделяемого на время выполнения сортировки, которое обычно составляет 20 процентов от размера индекса. Если некластеризованный индекс выстроен по первичному ключу, любые активные ограничения, ссылающиеся на FOREIGN KEY, будут автоматически отключены. Эти ограничения необходимо включить вручную по окончании перестроения индекса. Дополнительные сведения см. в разделах Отключение индексов и Правила включения индексов и ограничений.

Перестроение больших индексов

Перестроение индексов с более чем 128 экстентами осуществляется двумя отдельными этапами: логическое перестроение и физическое перестроение. На этапе логического перестроения существующие единицы размещения, используемые индексом, помечаются для освобождения, строки данных копируются и сортируются, а затем перемещаются в новые единицы размещения, созданные для хранения перестроенного индекса. На этапе физического перестроения единицы размещения, ранее помеченные для освобождения, физически сбрасываются посредством выполняемых в фоновом режиме коротких транзакций, и многочисленные блокировки для этого не требуются. Дополнительные сведения см. в разделе Удаление и повторная сборка больших объектов.

Установка параметров индекса

Параметры индекса не могут быть указаны при реорганизации индекса. Однако нижеследующие параметры индекса могут быть установлены при перестроении индекса с использованием ALTER INDEX REBUILD или CREATE INDEX WITH DROP_EXISTING:

PAD_INDEX	DROP_EXISTING (только CREATE INDEX)
FILLFACTOR	ONLINE
SORT_IN_TEMPDB	ALLOW_ROW_LOCKS
IGNORE_DUP_KEY	ALLOW_PAGE_LOCKS
STATISTICS_NORECOMPUTE	MAXDOP

Примечание.
Если выполнение сортировки не требуется или если сортировка может быть выполнена в памяти, параметр SORT_IN_TEMPDB пропускается.

Кроме того, предложение SET в инструкции ALTER INDEX позволяет устанавливать нижеследующие параметры индекса без перестроения индекса:

ALLOW_PAGE_LOCKS	IGNORE_DUP_KEY
ALLOW_ROW_LOCKS	STATISTICS_NORECOMPUTE

Дополнительные сведения см. в разделе Установка параметров индекса.

Перестроение или реорганизация индекса

ALTER INDEX (Transact-SQL)

Перестроение индекса удалением и воссозданием индекса за одну операцию

CREATE INDEX (Transact-SQL)

Примеры

А. Перестроение индекса

В нижеследующем примере производится перестроение отдельного индекса.

USE AdventureWorks;
GO
ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;
GO

Б. Перестроение всех индексов по таблице и указание параметров

В нижеследующем примере указывается ключевое слово ALL. Тем самым выполняется перестроение всех индексов, связанных с таблицей. Указываются три параметра.

USE AdventureWorks;
GO
ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
              STATISTICS_NORECOMPUTE = ON);
GO

В. Реорганизация индекса со сжатием LOB

В нижеследующем примере производится реорганизация отдельного кластеризованного индекса. Поскольку индекс содержит тип данных LOB на конечном уровне, инструкция также подвергает сжатию все страницы, в которых содержатся данные больших объектов. Обратите внимание на то, что указывать параметр WITH (LOB_Compaction) необязательно, так как значением по умолчанию является ON.

USE AdventureWorks;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO

См. также

Журнал изменений

Версия	Журнал
17 июля 2006 г.	Добавления Добавлены сведения об алгоритме подсчета фрагментации в разделе «Выявление фрагментации».
5 декабря 2005 г.	Добавления Добавлено руководство по обращению с очень низкими уровнями фрагментации в разделе «Выявление фрагментации».

Реорганизация и перестроение индексов

Выявление фрагментации

Примеры

Реорганизация индекса

Сжатие типов данных больших объектов

Перестроение индекса

Отключение некластеризованных индексов для экономии места на диске при выполнении операций перестроения

Перестроение больших индексов

Установка параметров индекса

Примеры

А. Перестроение индекса

Б. Перестроение всех индексов по таблице и указание параметров

В. Реорганизация индекса со сжатием LOB

См. также

Основные понятия

Другие ресурсы

Справка и поддержка

Журнал изменений

Дополнительные ресурсы