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

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL AzureдаХранилище данных SQL AzureдаParallel Data WarehouseAPPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

В этой статье описывается, как выполнить реорганизацию или перестроение фрагментированного индекса в SQL ServerSQL Server с помощью среды SQL Server Management StudioSQL Server Management Studio или Transact-SQLTransact-SQL.This article describes how to reorganize or rebuild a fragmented index in SQL ServerSQL Server by using SQL Server Management StudioSQL Server Management Studio or Transact-SQLTransact-SQL. Компонент SQL Server Database EngineSQL Server Database Engine автоматически изменяет индексы при вставке, обновлении или удалении базовых данных.The Компонент SQL Server Database EngineSQL Server Database Engine automatically modifies indexes whenever insert, update, or delete operations are made to the underlying data. Со временем эти изменения могут привести к тому, что данные в индексе окажутся разбросанными по базе данных (фрагментированными).Over time these modifications can cause the information in the index to become scattered in the database (fragmented). Фрагментация имеет место в тех случаях, когда в индексах содержатся страницы, для которых логический порядок, основанный на значении ключа, не совпадает с физическим порядком в файле данных.Fragmentation exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file. Существенно фрагментированные индексы могут серьезно снижать производительность запросов и служить причиной замедления отклика приложения, особенно операций сканирования.Heavily fragmented indexes can degrade query performance and cause your application to respond slowly, especially scan operations.

Можно устранить фрагментацию путем реорганизации или перестроения индекса.You can remedy index fragmentation by reorganizing or rebuilding an index. Для секционированных индексов, построенных на основе схемы секционирования, можно использовать любой из этих методов для всего индекса или отдельной его секции.For partitioned indexes built on a partition scheme, you can use either of these methods on a complete index or a single partition of an index. При перестроении старый индекс удаляется, и создается новый.Rebuilding an index drops and re-creates the index. Таким образом, устраняется фрагментация, восстанавливается место на диске путем сжатия страниц с учетом указанного или существующего коэффициента заполнения, переупорядочиваются индексные строки в последовательных страницах.This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. Если ALL указано, то все индексы в таблице удаляются и перестраиваются в ходе одной транзакции.When ALL is specified, all indexes on the table are dropped and rebuilt in a single transaction. Для реорганизации индекса требуется минимальный объем системных ресурсов.Reorganizing an index uses minimal system resources. При реорганизации концевой уровень кластеризованных и некластеризованных индексов на таблицах и представлениях дефрагментируется путем физической реорганизации страниц конечного уровня, в результате чего они выстраиваются в соответствии с логическим порядком конечных узлов (слева направо).It defragments the leaf level of clustered and nonclustered indexes on tables and views by physically reordering the leaf-level pages to match the logical, left to right, order of the leaf nodes. Кроме того, реорганизация сжимает страницы индекса.Reorganizing also compacts the index pages. Их сжатие производится в соответствии с текущим значением коэффициента заполнения.Compaction is based on the existing fill factor value.

Перед началомBefore You Begin

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

Первым шагом в определении, какой метод дефрагментации следует использовать, будет анализ индекса на предмет степени фрагментации.The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation. Системная функция sys.dm_db_index_physical_statsпозволяет выявить фрагментацию конкретного индекса, всех индексов в таблице или индексированном представлении, всех индексов в базе данных или всех индексов во всех базах данных.By using the system function sys.dm_db_index_physical_stats, you can detect fragmentation in a specific index, all indexes on a table or indexed view, all indexes in a database, or all indexes in all databases. Для секционированных индексов sys.dm_db_index_physical_stats также предоставляет сведения о фрагментации каждой секции.For partitioned indexes, sys.dm_db_index_physical_stats also provides fragmentation information for each partition.

Результирующий набор, возвращаемый функцией sys.dm_db_index_physical_stats , включает следующие столбцы:The result set returned by the sys.dm_db_index_physical_stats function includes the following columns.

СтолбецColumn ОписаниеDescription
avg_fragmentation_in_percentavg_fragmentation_in_percent Процентная доля логической фрагментации (неупорядоченные страницы в индексе).The percent of logical fragmentation (out-of-order pages in the index).
fragment_countfragment_count Число фрагментов (физически последовательные конечные страницы) в индексе.The number of fragments (physically consecutive leaf pages) in the index.
avg_fragment_size_in_pagesavg_fragment_size_in_pages Среднее число страниц в одном фрагменте индекса.Average number of pages in one fragment in an index.

Выяснив степень фрагментации, используйте нижеследующую таблицу для определения наиболее подходящего метода устранения фрагментации.After the degree of fragmentation is known, use the following table to determine the best method to correct the fragmentation.

Значениеavg_fragmentation_in_percentavg_fragmentation_in_percent value Корректирующая инструкцияCorrective statement
> 5% и < = 30%> 5% and < = 30% ALTER INDEX REORGANIZEALTER INDEX REORGANIZE
> 30%> 30% ALTER INDEX REBUILD WITH (ONLINE = ON) 1ALTER INDEX REBUILD WITH (ONLINE = ON) 1

1 Индекс может быть перестроен как в режиме "в сети", так и в режиме "вне сети".1 Rebuilding an index can be executed online or offline. Реорганизация индекса всегда выполняется в режиме "в сети".Reorganizing an index is always executed online. Чтобы добиться доступности, подобной варианту с реорганизацией, следует перестраивать индексы в режиме "в сети".To achieve availability similar to the reorganize option, you should rebuild indexes online.

Эти значения дают примерное представление об определении точки, в которой необходимо переключаться между ALTER INDEX REORGANIZE и ALTER INDEX REBUILD.These values provide a rough guideline for determining the point at which you should switch between ALTER INDEX REORGANIZE and ALTER INDEX REBUILD. Однако фактические значения могут различаться в каждом конкретном случае.However, the actual values may vary from case to case. Важно определить наилучшее пороговое значение для используемой среды экспериментальным путем.It is important that you experiment to determine the best threshold for your environment. При очень низких уровнях фрагментации (менее 5 %) эти команды, как правило, использоваться не должны, так как выгода от дефрагментации столь низкого уровня почти всегда в достаточной степени компенсируется за счет реорганизации или перестроения индекса.Very low levels of fragmentation (less than 5 percent) should typically not be addressed by either of these commands, because the benefit from removing such a small amount of fragmentation is almost always vastly outweighed by the cost of reorganizing or rebuilding the index. Дополнительные сведения о ALTER INDEX REORGANIZE и ALTER INDEX REBUILD см. в разделе ALTER INDEX (Transact-SQL).For more information about ALTER INDEX REORGANIZE and ALTER INDEX REBUILD, refer to ALTER INDEX (Transact-SQL).

Примечание

Перестроение или реорганизация малых индексов часто не приводит к уменьшению фрагментации.Rebuilding or reorganizing small indexes often does not reduce fragmentation. Страницы индексов малого размера хранятся в смешанных экстентах.The pages of small indexes are sometimes stored on mixed extents. Смешанные экстенты могут находиться в общем пользовании у восьми объектов, поэтому фрагментацию в малом индексе нельзя уменьшить путем его реорганизации или перестроения.Mixed extents are shared by up to eight objects, so the fragmentation in a small index might not be reduced after reorganizing or rebuilding it.

ОграниченияLimitations and Restrictions

Перестроение индексов с более чем 128 экстентами осуществляется в два этапа: логическое и физическое перестроение.Indexes with more than 128 extents are rebuilt in two separate phases: logical and physical. На этапе логического перестроения существующие единицы распределения, используемые индексом, помечаются для освобождения, строки данных копируются и сортируются, а затем перемещаются в новые единицы распределения, созданные для хранения перестроенного индекса.In the logical phase, the existing allocation units used by the index are marked for deallocation, the data rows are copied and sorted, then moved to new allocation units created to store the rebuilt index. На этапе физического перестроения единицы распределения, ранее помеченные для освобождения, физически удаляются посредством выполняемых в режиме в сети коротких транзакций, и многочисленные блокировки для этого не требуются.In the physical phase, the allocation units previously marked for deallocation are physically dropped in short transactions that happen in the background, and do not require many locks. Дополнительные сведения об экстентах см. в разделе Руководство по архитектуре страниц и экстентов.For more information about extents, refer to the Pages and Extents Architecture Guide.

Инструкция ALTER INDEX REORGANIZE требует, чтобы в файле данных, где содержится индекс, было свободное пространство, потому что операция может выделять временные рабочие страницы только в том же файле (а не в другом файле файловой группы, к примеру).The ALTER INDEX REORGANIZE statement requires the data file containing the index to have space available, because the operation can only allocate temporary work pages on the same file, not another file within the filegroup. Поэтому у пользователя все равно может возникнуть ошибка 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.So although the filegroup might have free pages available, the user can still encounter error 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, возможны, но не рекомендуются.Creating and rebuilding non-aligned indexes on a table with more than 1,000 partitions is possible, but is not recommended. Это может привести к снижению производительности или чрезмерному потреблению памяти во время таких операций.Doing so may cause degraded performance or excessive memory consumption during these operations.

Индекс нельзя реорганизовать или перестроить, если файловая группа, в которой он находится, размещена вне сети или предназначена только для чтения.An index cannot be reorganized or rebuilt if the filegroup in which it is located is offline or set to read-only. Если указывается ключевое слово ALL, а один индекс или несколько находятся в файловой группе, которая размещена вне сети или предназначена только для чтения, то выполнить инструкцию не удастся.When the keyword ALL is specified and one or more indexes are in an offline or read-only filegroup, the statement fails.

Важно!

При создании или перестроении индекса в SQL ServerSQL Server статистические данные создаются или обновляются путем сканирования всех строк таблицы.When an index is created or rebuilt in SQL ServerSQL Server, statistics are created or updated by scanning all the rows in the table.

Однако начиная с выпуска SQL Server 2012 (11.x)SQL Server 2012 (11.x) статистические данные не создаются и не обновляются путем сканирования всех строк таблицы при создании или перестроении секционированного индекса.However, starting with SQL Server 2012 (11.x)SQL Server 2012 (11.x), statistics are not created or updated by scanning all the rows in the table when a partitioned index is created or rebuilt. Вместо этого оптимизатор запросов использует для создания статистики алгоритм выборки по умолчанию.Instead, the query optimizer uses the default sampling algorithm to generate these statistics. Для получения статистики по секционированным индексам путем сканирования всех строк таблицы используйте инструкции CREATE STATISTICS или UPDATE STATISTICS с предложением FULLSCAN.To obtain statistics on partitioned indexes by scanning all the rows in the table, use CREATE STATISTICS or UPDATE STATISTICS with the FULLSCAN clause.

безопасностьSecurity

PermissionsPermissions

Необходимо разрешение ALTER для таблицы или представления.Requires ALTER permission on the table or view. Пользователь должен входить хотя бы в одну из следующих ролей:User must be a member of at least one of the following roles:

  • роль базы данных db_ddladmin 1;db_ddladmin database role 1
  • роль базы данных db_owner;db_owner database role
  • роль сервера sysadmin;sysadmin server role

1Роль базы данных db_ddladmin — наименее привилегированная.1db_ddladmin database role is the least privileged.

Проверка фрагментации индексов с помощью SQL Server Management StudioSQL Server Management StudioCheck index fragmentation using SQL Server Management StudioSQL Server Management Studio

Проверка фрагментации индексаTo check the fragmentation of an index

  1. В обозревателе объектов разверните базу данных, содержащую таблицу, в которой необходимо проверить фрагментацию индекса.In Object Explorer, Expand the database that contains the table on which you want to check an index's fragmentation.

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

  3. Разверните таблицу, в которой нужно проверить фрагментацию индекса.Expand the table on which you want to check an index's fragmentation.

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

  5. Щелкните правой кнопкой мыши индекс, для которого нужно проверить фрагментацию, и выберите пункт Свойства.Right-click the index of which you want to check the fragmentation and select Properties.

  6. В разделе Выбор страницывыберите пункт Фрагментация.Under Select a page, select Fragmentation.

    На странице Фрагментация доступны следующие сведения.The following information is available on the Fragmentation page:

    Заполненность страниц. Отображает среднее заполнение страниц индекса в процентах.Page fullness Indicates average fullness of the index pages, as a percentage. 100 % означает, что страницы индекса полностью заполнены.100% means the index pages are completely full. 50 % означает, что каждая страница индекса заполнена в среднем наполовину.50% means that, on average, each index page is half full.

    Общая фрагментация. Процент логической фрагментации.Total fragmentation The logical fragmentation percentage. Отображает количество страниц индекса, хранимых не в порядке.This indicates the number of pages in an index that are not stored in order.

    Средний размер строки. Средний размер строки конечного уровня.Average row size The average size of a leaf level row.

    Глубина. Количество уровней индекса, включая конечный уровень.Depth The number of levels in the index, including the leaf level.

    Перенаправленные записи. Количество записей в куче с указателями на данные в других расположениях.Forwarded records The number of records in a heap that have forward pointers to another data location. (Такое состояние возникает во время обновления, когда не хватает места для сохранения новой строки в исходном расположении.)(This state occurs during an update, when there is not enough room to store the new row in the original location.)

    Фантомные строки. Количество строк, помеченных как удаленные, но фактически не удаленных.Ghost rows The number of rows that are marked as deleted but not yet removed. Эти строки будут удалены потоком очистки, когда сервер будет не загружен.These rows will be removed by a clean-up thread, when the server is not busy. Это значение не включает в себя строки, сохраняемые из-за ожидающей выполнения транзакции изоляции моментальных снимков.This value does not include rows that are being retained due to an outstanding snapshot isolation transaction.

    Тип индекса. Тип индекса.Index type The type of index. Возможными значениями являются Кластеризованный индекс, Некластеризованный индекси Первичный XML.Possible values are Clustered index, Nonclustered index, and Primary XML. Таблицы могут также сохраняться в виде кучи (без индексов), однако после этого данная страница «Свойства индекса» не может быть открыта.Tables can also be stored as a heap (without indexes), but then this Index Properties page cannot be opened.

    Строки конечного уровня. Количество строк конечного уровня.Leaf-level rows The number of leaf level rows.

    Максимальный размер строки. Максимальный размер строки конечного уровня.Maximum row size The maximum leaf-level row size.

    Минимальный размер строки. Минимальный размер строки конечного уровня.Minimum row size The minimum leaf-level row size.

    Страницы. Общее количество страниц данных.Pages The total number of data pages.

    Идентификатор секции. Идентификатор секции сбалансированного дерева, содержащего индекс.Partition ID The partition ID of the b-tree containing the index.

    Фантомные строки версии. Количество фантомных записей, которые сохраняются в памяти из-за незавершенной транзакции изоляции моментального снимка.Version ghost rows The number of ghost records that are being retained due to an outstanding snapshot isolation transaction.

Проверка фрагментации индексов с помощью Transact-SQLTransact-SQLCheck index fragmentation using Transact-SQLTransact-SQL

Проверка фрагментации индексаTo check the fragmentation of an index

В следующем примере показан средний процент фрагментации всех индексов в таблице HumanResources.Employee базы данных AdventureWorks.The following example Find the average fragmentation percentage of all indexes in the HumanResources.Employee table in the AdventureWorks database.

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;

Предыдущая инструкция возвращает результирующий набор, как показано ниже.The previous statement returns a result set similar to the following.

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).For more information, see sys.dm_db_index_physical_stats.

Устранение фрагментации с помощью SQL Server Management StudioSQL Server Management StudioRemove fragmentation using SQL Server Management StudioSQL Server Management Studio

Реорганизация или перестроение индексаTo reorganize or rebuild an index

  1. В обозревателе объектов разверните базу данных, содержащую таблицу, в которой необходимо реорганизовать индекс.In Object Explorer, Expand the database that contains the table on which you want to reorganize an index.
  2. Разверните папку Таблицы.Expand the Tables folder.
  3. Разверните таблицу, в которой нужно реорганизовать индекс.Expand the table on which you want to reorganize an index.
  4. Разверните папку Индексы.Expand the Indexes folder.
  5. Щелкните правой кнопкой мыши индекс, который требуется реорганизовать, и выберите пункт Реорганизовать.Right-click the index you want to reorganize and select Reorganize.
  6. В диалоговом окне Реорганизация индексов убедитесь, что нужный индекс приведен в сетке Индексы для реорганизации , и нажмите кнопку ОК.In the Reorganize Indexes dialog box, verify that the correct index is in the Indexes to be reorganized grid and click OK.
  7. Установите флажок Сжать данные в столбцах больших объектов, чтобы указать, что также сжимаются все страницы, содержащие данные больших объектов.Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.
  8. Нажмите кнопку ОК.Click OK.

Реорганизация всех индексов в таблицеTo reorganize all indexes in a table

  1. В обозревателе объектов разверните базу данных, содержащую таблицу, в которой необходимо реорганизовать индексы.In Object Explorer, Expand the database that contains the table on which you want to reorganize the indexes.
  2. Разверните папку Таблицы.Expand the Tables folder.
  3. Разверните таблицу, в которой нужно реорганизовать индексы.Expand the table on which you want to reorganize the indexes.
  4. Щелкните правой кнопкой мыши папку Индексы и выберите команду Реорганизовать все.Right-click the Indexes folder and select Reorganize All.
  5. В диалоговом окне Реорганизация индексов убедитесь, что нужные индексы приведены в сетке Индексы для реорганизации.In the Reorganize Indexes dialog box, verify that the correct indexes are in the Indexes to be reorganized. Для удаления индекса из сетки Индексы для реорганизации выделите индекс и нажмите клавишу DELETE.To remove an index from the Indexes to be reorganized grid, select the index and then press the Delete key.
  6. Установите флажок Сжать данные в столбцах больших объектов , чтобы указать, что также сжимаются все страницы, содержащие данные больших объектов.Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.
  7. Нажмите кнопку ОК.Click OK.

Перестроение индексаTo rebuild an index

  1. В обозревателе объектов разверните базу данных, содержащую таблицу, в которой необходимо реорганизовать индекс.In Object Explorer, Expand the database that contains the table on which you want to reorganize an index.
  2. Разверните папку Таблицы.Expand the Tables folder.
  3. Разверните таблицу, в которой нужно реорганизовать индекс.Expand the table on which you want to reorganize an index.
  4. Разверните папку Индексы.Expand the Indexes folder.
  5. Щелкните правой кнопкой мыши индекс, который требуется реорганизовать, и выберите команду Перестроить.Right-click the index you want to reorganize and select Rebuild.
  6. В диалоговом окне Перестроение индексов убедитесь, что нужный индекс приведен в сетке Индексы для перестроения, и нажмите кнопку ОК.In the Rebuild Indexes dialog box, verify that the correct index is in the Indexes to be rebuilt grid and click OK.
  7. Установите флажок Сжать данные в столбцах больших объектов, чтобы указать, что также сжимаются все страницы, содержащие данные больших объектов.Select the Compact large object column data check box to specify that all pages that contain large object (LOB) data are also compacted.
  8. Нажмите кнопку ОК.Click OK.

Устранение фрагментации с помощью Transact-SQLTransact-SQLRemove fragmentation using Transact-SQLTransact-SQL

Реорганизация фрагментированного индексаTo reorganize a fragmented index

В следующем примере показано, как реорганизовать индекс IX_Employee_OrganizationalLevel_OrganizationalNode в таблице HumanResources.Employee базы данных AdventureWorks.The following example reorganizes the IX_Employee_OrganizationalLevel_OrganizationalNode index on the HumanResources.Employee table in the AdventureWorks database.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
   ON HumanResources.Employee
   REORGANIZE
;

Реорганизация всех индексов в таблицеTo reorganize all indexes in a table

В следующем примере показано, как реорганизовать индексы в таблице HumanResources.Employee базы данных AdventureWorks.The following example Reorganize all indexes on the HumanResources.Employee table in the AdventureWorks database.

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE
;

Перестройка фрагментированного индексаTo rebuild a fragmented index

В следующем примере показано, как перестроить единственный индекс в таблице Employee базы данных AdventureWorks.The following example rebuilds a single index on the Employee table in the AdventureWorks database.

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

Перестроение всех индексов в таблицеTo rebuild all indexes in a table

В следующем примере показано, как перестроить все индексы, связанные с таблицей базы данных AdventureWorks, используя ключевое слово ALL.The following example rebuilds all indexes associated with the table in the AdventureWorks database using the ALL keyword. Указываются три параметра.Three options are specified.

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

Подробные сведения см. в статье ALTER INDEX (Transact-SQL).For more information, see ALTER INDEX.

Автоматическое управление индексами и статистикойAutomatic index and statistics management

Используйте такие решения, как Адаптивная дефрагментация индексов, чтобы автоматически управлять дефрагментацией индексов и обновлениями статистики для одной базы данных или нескольких.Leverage solutions such as Adaptive Index Defrag to automatically manage index defragmentation and statistics updates for one or more databases. Эта процедура автоматически выбирает, следует ли перестроить или реорганизовать индекс, сверяясь с уровнем фрагментации и другими параметрами, и обновляет статистику на основе линейных пороговых значений.This procedure automatically chooses whether to rebuild or reorganize an index according to its fragmentation level, amongst other parameters, and update statistics with a linear threshold.

См. также:See Also