Поделиться через

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

  • Статья

В этом разделе описывается реорганизация или перестройка фрагментированного индекса в SQL Server 2014 с помощью SQL Server Management Studio или Transact-SQL. Ядро СУБД SQL Server автоматически поддерживает индексы при каждом выполнении операций вставки, обновления или удаления базовых данных. Со временем эти изменения могут привести к тому, что данные в индексе окажутся разбросанными по базе данных (фрагментированными). Фрагментация имеет место в тех случаях, когда в индексах содержатся страницы, для которых логический порядок, основанный на значении ключа, не совпадает с физическим порядком в файле данных. Значительно фрагментированные индексы могут серьезно снижать производительность запросов и служить причиной замедления откликов приложения.

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

В этом разделе

Перед началом

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

Первым шагом в определении, какой метод дефрагментации следует использовать, будет анализ индекса на предмет степени фрагментации. Системная функция 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) 1

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

Совет

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

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

Примечание

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

Рекомендации по дефрагментации индексов

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

Автоматическое перестроение всех некластеризованных индексов в таблице происходит в следующих ситуациях:

  • создание кластеризованного индекса для таблицы;
  • удаление кластеризованного индекса, в результате которого таблица сохраняется как куча;
  • включение столбцов в ключ кластеризации или исключение столбцов из него.

Автоматическое перестроение всех некластеризованных индексов в таблице не требуется в следующих ситуациях:

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

Ограничения

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

Инструкция ALTER INDEX REORGANIZE требует, чтобы в файле данных, где содержится индекс, было свободное пространство, потому что операция может выделять временные рабочие страницы только в том же файле (а не в другом файле файловой группы, к примеру). Поэтому у пользователя все равно может возникнуть ошибка 1105, даже если в файловой группе есть свободные страницы: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Создание и перестройка невыровненных индексов для таблицы, количество секций в которой превышает 1000, возможны, но не рекомендуются. Это может привести к снижению производительности или чрезмерному потреблению памяти во время таких операций.

Индекс нельзя реорганизовать или перестроить, если файловая группа, в которой он расположен, находится в автономном режиме или предназначена только для чтения. Если указывается ключевое слово ALL, а один индекс или несколько расположены в файловой группе, которая находится в автономном режиме или предназначена только для чтения, то выполнить инструкцию не удастся.

Безопасность

Разрешения

Необходимо разрешение ALTER для таблицы или представления. Пользователь должен быть членом предопределенной роли сервера sysadmin или предопределенных ролей базы данных db_ddladmin и db_owner.

Использование среды SQL Server Management Studio

Проверка фрагментации индекса

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

  2. Разверните папку Таблицы.

  3. Разверните таблицу, в которой нужно проверить фрагментацию индекса.

  4. Разверните папку Индексы.

  5. Щелкните правой кнопкой мыши индекс, для которого нужно проверить фрагментацию, и выберите пункт Свойства.

  6. В разделе Выбор страницывыберите пункт Фрагментация.

    На странице Фрагментация доступны следующие сведения.

    Заполненность страниц
    Отображает среднее заполнение страниц индекса, в процентах. 100 % означает, что страницы индекса полностью заполнены. 50 % означает, что каждая страница индекса заполнена в среднем наполовину.

    Общая фрагментация
    Процент логической фрагментации. Отображает количество страниц индекса, хранимых не в порядке.

    Средний размер строки
    Средний размер строки конечного уровня.

    Depth
    Количество уровней индекса, включая конечный уровень.

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

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

    Тип индекса
    Тип индекса. Возможными значениями являются Кластеризованный индекс, Некластеризованный индекси Первичный XML. Таблицы могут также сохраняться в виде кучи (без индексов), однако после этого данная страница «Свойства индекса» не может быть открыта.

    Строки конечного уровня
    Количество строк конечного уровня.

    Максимальный размер строки
    Максимальный размер строки конечного уровня.

    Минимальный размер строки
    Минимальный размер строки конечного уровня.

    Страницы
    Общее число страниц данных.

    Partition ID
    Идентификатор секции сбалансированного дерева, содержащего индекс.

    Фантомные строки версии
    Количество фантомных записей, которые сохраняются в памяти из-за незавершенной транзакции изоляции моментального снимка.

Использование Transact-SQL

Проверка фрагментации индекса

  1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine.

  2. На стандартной панели выберите пункт Создать запрос.

  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.

    USE AdventureWorks2012;  
GO  
-- Find the average fragmentation percentage of all indexes  
-- in the HumanResources.Employee table.   
SELECT a.index_id, name, avg_fragmentation_in_percent  
FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2012'), OBJECT_ID(N'HumanResources.Employee'), 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_Employee_BusinessEntityID                          0  
2           IX_Employee_OrganizationalNode                        0  
3           IX_Employee_OrganizationalLevel_OrganizationalNode    0  
5           AK_Employee_LoginID                                   66.6666666666667  
6           AK_Employee_NationalIDNumber                          50  
7           AK_Employee_rowguid                                   0  

(6 row(s) affected)

Дополнительные сведения см. в разделе sys.dm_db_index_physical_stats (Transact-SQL).

Использование среды SQL Server Management Studio

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

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

  2. Разверните папку Таблицы.

  3. Разверните таблицу, в которой нужно реорганизовать индекс.

  4. Разверните папку Индексы.

  5. Щелкните правой кнопкой мыши индекс, который требуется реорганизовать, и выберите пункт Реорганизовать.

  6. В диалоговом окне Реорганизация индексов убедитесь, что нужный индекс приведен в сетке Индексы для реорганизации , и нажмите кнопку ОК.

  7. Установите флажок Сжать данные в столбцах больших объектов , чтобы указать, что также сжимаются все страницы, содержащие данные больших объектов.

  8. Нажмите кнопку ОК.

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

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

  2. Разверните папку Таблицы.

  3. Разверните таблицу, в которой нужно реорганизовать индексы.

  4. Щелкните правой кнопкой мыши папку Индексы и выберите команду Реорганизовать все.

  5. В диалоговом окне Реорганизация индексов убедитесь, что нужные индексы приведены в сетке Индексы для реорганизации. Для удаления индекса из сетки Индексы для реорганизации выделите индекс и нажмите клавишу DELETE.

  6. Установите флажок Сжать данные в столбцах больших объектов , чтобы указать, что также сжимаются все страницы, содержащие данные больших объектов.

  7. Нажмите кнопку ОК.

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

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

  2. Разверните папку Таблицы.

  3. Разверните таблицу, в которой нужно реорганизовать индекс.

  4. Разверните папку Индексы.

  5. Щелкните правой кнопкой мыши индекс, который требуется реорганизовать, и выберите пункт Реорганизовать.

  6. В диалоговом окне Перестроение индексов убедитесь, что нужный индекс приведен в сетке Индексы для перестроения, и нажмите кнопку ОК.

  7. Установите флажок Сжать данные в столбцах больших объектов , чтобы указать, что также сжимаются все страницы, содержащие данные больших объектов.

  8. Нажмите кнопку ОК.

Использование Transact-SQL

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

  1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine.

  2. На стандартной панели выберите пункт Создать запрос.

  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.

    USE AdventureWorks2012;   
GO  
-- Reorganize the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table.   

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode ON HumanResources.Employee  
REORGANIZE ;   
GO

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

  1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine.

  2. На стандартной панели выберите пункт Создать запрос.

  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.

    USE AdventureWorks2012;   
GO  
-- Reorganize all indexes on the HumanResources.Employee table.  
ALTER INDEX ALL ON HumanResources.Employee  
REORGANIZE ;   
GO

Перестроение дефрагментированного индекса

  1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine.

  2. На стандартной панели выберите пункт Создать запрос.

  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить. В этом примере перестраивается один индекс в таблице Employee .

    USE AdventureWorks2012;
GO
ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD;
GO

Перестроение всех индексов в таблице

  1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database Engine.

  2. На стандартной панели выберите пункт Создать запрос.

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

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

Дополнительные сведения см. в разделе ALTER INDEX (Transact-SQL).

См. также:

Рекомендации по дефрагментации индексов Microsoft SQL Server 2000