Реорганизация и перестроение индексов
Компонент SQL Server Database Engine автоматически поддерживает состояние индексов при выполнении операций вставки, обновления или удаления в отношении базовых данных. Со временем эти изменения могут привести к тому, что данные в индексе окажутся разбросанными по базе данных (фрагментированными). Фрагментация имеет место в тех случаях, когда в индексах содержатся страницы, для которых логический порядок, основанный на значении ключа, не совпадает с физическим порядком в файле данных. Значительно фрагментированные индексы могут серьезно снижать производительность запросов и служить причиной замедления откликов приложения. Дополнительные сведения см. на веб-сайте Майкрософт.
Можно устранить фрагментацию путем реорганизации или перестроения индекса. Для секционированных индексов, построенных на основе схемы секций, можно использовать любой из этих методов в отношении всего индекса целиком или отдельной его секции.
Выявление фрагментации
Первым шагом в определении, какой метод дефрагментации следует использовать, будет анализ индекса на предмет степени фрагментации. Системная функция sys.dm_db_index_physical_stats позволяет выявить фрагментацию конкретного индекса, всех индексов в таблице или индексированном представлении, всех индексов в базе данных или всех индексов во всех базах данных. Для секционированных индексов sys.dm_db_index_physical_stats также предоставляет сведения о фрагментации каждой секции.
Результирующий набор, возвращаемый функцией 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 %) эти команды использоваться не должны, так как выгода от дефрагментации столь низкого уровня почти всегда в достаточной степени компенсируется за счет реорганизации или перестроения индекса.
.gif "Примечание") Примечание |
|---|
Обычно фрагментация небольших индексов является неконтролируемой. Страницы индексов малого размера хранятся в смешанных экстентах. Смешанные экстенты могут совместно использоваться восемью объектами, поэтому фрагментация в небольшом индексе может не сократиться после реорганизации или перестроения индекса. Дополнительные сведения о смешанных экстентах см. в разделе Страницы и экстенты. |
Пример
Следующий пример запрашивает через функцию динамического управления sys.dm_db_index_physical_stats среднюю фрагментацию для всех индексов в таблице Production.Product. В соответствии с предыдущей таблицей, рекомендуемым решением проблемы будет реорганизация PK_Product_ProductID и перестроение других индексов.
USE AdventureWorks2008R2;
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 |
Дополнительные сведения см. в разделе Установка параметров индекса.
Перестроение или реорганизация индекса
Перестроение индекса удалением и повторным созданием индекса за один шаг
Примеры
А. Перестроение индекса
В нижеследующем примере производится перестроение отдельного индекса.
USE AdventureWorks2008R2;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO
Б. Перестроение всех индексов по таблице и указание параметров
В нижеследующем примере указывается ключевое слово ALL. Тем самым выполняется перестроение всех индексов, связанных с таблицей. Указываются три параметра.
USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
GO
ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;
GO
См. также