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

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

  • Статья

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

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

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

  • Перед началом работы.

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

    Ограничения

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

  • Для проверки фрагментации индекса используется:

    Среда SQL Server Management Studio

    Transact-SQL

  • Для реорганизации или перестроения индекса используется:

    Среда SQL Server Management Studio

    Transact-SQL

Перед началом работы

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

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

ПримечаниеПримечание

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

Ограничения

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

  • Параметры индекса не могут быть указаны при реорганизации индекса.

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

Разрешения

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

Значок стрелки, используемый со ссылкой «В начало»[В начало]

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Значок стрелки, используемый со ссылкой «В начало»[В начало]

Использование 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