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

ОБЛАСТЬ ПРИМЕНЕНИЯ ЭТОЙ СТАТЬИ: даSQL Server (начиная с 2008)даБаза данных SQL AzureдаХранилище данных SQL AzureдаParallel Data WarehouseTHIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Материалы по предыдущим версиям SQL Server см. в разделе Реорганизация и перестроение индексов.For content related to previous versions of SQL Server, see Reorganize and Rebuild Indexes.

В этом разделе описывается реорганизация или перестроение фрагментированного индекса в SQL Server 2017SQL Server 2017 с помощью среды SQL Server Management StudioSQL Server Management Studio или Transact-SQLTransact-SQL.This topic describes how to reorganize or rebuild a fragmented index in SQL Server 2017SQL Server 2017 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 maintains 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.

Можно устранить фрагментацию путем реорганизации или перестроения индекса.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_percent avg_fragmentation_in_percent value Корректирующая инструкцияCorrective statement
> 5% и < = 30%> 5% and < = 30% ALTER INDEX REORGANIZEALTER INDEX REORGANIZE
> 30%> 30% ALTER INDEX REBUILD WITH (ONLINE = ON)ALTER INDEX REBUILD WITH (ONLINE = ON)

* Индекс может быть перестроен как в сети, так и вне сети.* 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 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.

Примечание

Обычно фрагментация небольших индексов является неконтролируемой.In general, fragmentation on small indexes is often not controllable. Страницы индексов малого размера хранятся в смешанных экстентах.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 the index.

Ограничения 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.

  • Параметры индекса не могут быть указаны при реорганизации индекса.Index options cannot be specified when reorganizing an index.

  • Инструкция 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 "Не удалось выделить место для объекта <имя индекса>.<имя таблицы> в базе данных <имя базы данных>, поскольку файловая группа PRIMARY переполнена".So although the filegroup might have free pages available, the user can still encounter error 1105 "Could not allocate space for object <index name>.<table name> in database <database name> because the 'PRIMARY' filegroup is full."

  • Создание и перестройка невыровненных индексов для таблицы, количество секций в которой превышает 1000, возможны, но не поддерживаются.Creating and rebuilding nonaligned indexes on a table with more than 1,000 partitions is possible, but is not supported. Это может привести к снижению производительности или чрезмерному потреблению памяти во время таких операций.Doing so may cause degraded performance or excessive memory consumption during these operations.

Примечание

Начиная с выпуска SQL Server 2012SQL Server 2012статистические данные не создаются путем сканирования всех строк таблицы при создании или перестроении секционированного индекса.Starting with SQL Server 2012SQL Server 2012, statistics are not created 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 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

Разрешения Permissions

Необходимо разрешение ALTER для таблицы или представления.Requires ALTER permission on the table or view. Пользователь должен быть членом предопределенной роли сервера sysadmin или предопределенных ролей базы данных db_ddladmin и db_owner .User must be a member of the sysadmin fixed server role or the db_ddladmin and db_owner fixed database roles.

Использование среды SQL Server Management Studio Using SQL 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 IDPartition 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-SQL Using Transact-SQL

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

  1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database EngineDatabase Engine.In Object Explorer, connect to an instance of Компонент Database EngineDatabase Engine.

  2. На стандартной панели выберите пункт Создать запрос.On the Standard bar, click New Query.

  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.Copy and paste the following example into the query window and click Execute.

    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  
    

    Эта инструкция должна возвратить результирующий набор, подобный приведенному ниже.The statement above might return 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 (Transact-SQL).

Использование среды SQL Server Management Studio Using SQL 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-SQL Using Transact-SQL

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

  1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database EngineDatabase Engine.In Object Explorer, connect to an instance of Компонент Database EngineDatabase Engine.

  2. На стандартной панели выберите пункт Создать запрос.On the Standard bar, click New Query.

  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.Copy and paste the following example into the query window and click Execute.

    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  
    

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

  1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database EngineDatabase Engine.In Object Explorer, connect to an instance of Компонент Database EngineDatabase Engine.

  2. На стандартной панели выберите пункт Создать запрос.On the Standard bar, click New Query.

  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.Copy and paste the following example into the query window and click Execute.

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

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

  1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database EngineDatabase Engine.In Object Explorer, connect to an instance of Компонент Database EngineDatabase Engine.

  2. На стандартной панели выберите пункт Создать запрос.On the Standard bar, click New Query.

  3. Скопируйте следующий пример в окно запроса и нажмите кнопку Выполнить.Copy and paste the following example into the query window and click Execute. В этом примере перестраивается один индекс в таблице Employee .The example rebuilds a single index on the Employee table.

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

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

  1. В обозревателе объектовподключитесь к экземпляру компонента Компонент Database EngineDatabase Engine.In Object Explorer, connect to an instance of Компонент Database EngineDatabase Engine.

  2. На стандартной панели выберите пункт Создать запрос.On the Standard bar, click New Query.

  3. Скопируйте следующий пример в запрос. В примере указывается ключевое слово ALL.Copy and paste the following example into the query The example specifies the keyword ALL. Тем самым выполняется перестроение всех индексов, связанных с таблицей.This rebuilds all indexes associated with the table. Указываются три параметра.Three options are specified.

    USE AdventureWorks2012;
    GO
    ALTER INDEX ALL ON Production.Product
    REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
                  STATISTICS_NORECOMPUTE = ON);
    GO
    
    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).For more information, see ALTER INDEX (Transact-SQL).

См. также:See Also

Руководство по проектированию индексов SQL ServerSQL Server Index Design Guide