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

ОБЛАСТЬ ПРИМЕНЕНИЯ: даSQL Server даБаза данных SQL Azure даAzure Synapse Analytics (хранилище данных SQL) даParallel Data Warehouse APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) 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:

  • При реорганизации индекса используется минимальный объем системных ресурсов. Эта операция выполняется в оперативном режиме.Reorganizing an index uses minimal system resources and is an online operation. Это означает, что долгосрочные блокировки таблицы не удерживаются и запросы или обновления базовой таблицы могут продолжаться во время выполнения транзакции ALTER INDEX REORGANIZE.This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.

    • Для индексов rowstore дефрагментируется конечный уровень кластеризованных и некластеризованных индексов в таблицах и представлениях путем физической реорганизации страниц конечного уровня. В результате они выстраиваются в соответствии с логическим порядком конечных объектов (слева направо).For rowstore indexes, 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. Увидеть коэффициент заполнения можно в таблице sys.indexes.To view the fill factor setting, use sys.indexes.
    • Иногда при использовании индексов columnstore после загрузки данных в разностном хранилище находится несколько небольших групп строк.When using columnstore indexes, it is possible that after loading data the delta store has multiple small rowgroups. При реорганизации индекса columnstore все группы строк принудительно отправляются в columnstore, а затем объединяются в меньшее число групп строк с большим количеством строк внутри.Reorganizing the columnstore index forces all of the rowgroups into the columnstore, and then combines the rowgroups into fewer rowgroups with more rows. Операция реорганизации также приведет к удалению строк, которые были удалены из columnstore.The reorganize operation will also remove rows that have been deleted from the columnstore. Реорганизация изначально потребует дополнительных ресурсов ЦП для сжатия данных, что может снизить общую производительность системы.Reorganizing will initially require additional CPU resources to compress the data, which could slow overall system performance. Однако после сжатия данных производительность запросов может возрасти.However, as soon as the data is compressed, query performance can improve.
  • При перестроении старый индекс удаляется и создается новый.Rebuilding an index drops and re-creates the index. В зависимости от типа индекса и версии Компонент Database EngineDatabase Engine это можно сделать в оперативном или автономном режимах.Depending on the type of index and Компонент Database EngineDatabase Engine version, this can be done online or offline.

    • Для индексов rowstore при перестроении устраняется фрагментация, восстанавливается место на диске путем сжатия страниц с учетом указанного или существующего коэффициента заполнения и переупорядочиваются индексные строки в последовательных страницах.For rowstore indexes, rebuilding 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. Ограничения FOREIGN KEY не обязательно отменять заранее.Foreign key constraints do not have to be dropped in advance. Если перестраиваются индексы с 128 или большим числом экстентов, то компонент Компонент Database EngineDatabase Engine откладывает процедуры освобождения страниц и связанные с ними блокировки до фиксации транзакции.When indexes with 128 extents or more are rebuilt, the Компонент Database EngineDatabase Engine defers the actual page deallocations, and their associated locks, until after the transaction commits.
    • Для индексов columnstore при перестроении устраняется фрагментация, все строки перемещаются в columnstore и освобождается место на диске за счет физического удаления строк, которые логически удалены из таблицы.For columnstore indexes, rebuilding removes fragmentation, moves all rows into the columnstore, and reclaims disk space by physically deleting rows that have been logically deleted from the table. Начиная с SQL Server 2016 (13.x)SQL Server 2016 (13.x) перестраивать индекс columnstore обычно не требуется, так как инструкция REORGANIZE выполняет необходимые для перестройки действия в фоновом и оперативном режиме.Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x), rebuilding the columnstore index is usually not needed since REORGANIZE performs the essentials of a rebuild in the background as an online operation.

В предыдущих версиях SQL ServerSQL Server иногда можно было перестроить некластеризованный индекс rowstore, чтобы исправить несоответствия, вызванные сбоями оборудования.In earlier versions of SQL ServerSQL Server, you could sometimes rebuild a rowstore nonclustered index to correct inconsistencies caused by hardware failures.
Начиная с SQL Server 2008SQL Server 2008 можно также скорректировать такие несоответствия между индексом и кластеризованным индексом, перестроив некластеризованный индекс в автономном режиме.Starting with SQL Server 2008SQL Server 2008, you may still be able to repair such inconsistencies between the index and the clustered index by rebuilding a nonclustered index offline. Однако невозможно устранить несоответствия некластеризованного индекса, перестроив индекс в оперативном режиме, потому что механизм перестроения в этом режиме будет использовать существующий некластеризованный индекс в качестве основы для перестроения и тем самым закрепит несоответствие.However, you cannot repair nonclustered index inconsistencies by rebuilding the index online, because the online rebuild mechanism will use the existing nonclustered index as the basis for the rebuild and thus persist the inconsistency. При автономном перестроении индекса иногда может принудительно запускаться проверка кластеризованного индекса (или кучи), и в результате устраняются несоответствия.Rebuilding the index offline can sometimes force a scan of the clustered index (or heap) and so remove the inconsistency. Чтобы обеспечить перестроение из кластеризованного индекса, удалите и повторно создайте некластеризованный индекс.To assure a rebuild from the clustered index, drop and recreate the nonclustered index. Как и в предыдущих версиях, рекомендованным методом устранения несоответствий является восстановление затронутых данных из резервных копий, однако исправить несоответствия индекса можно, перестроив некластеризованный индекс в автономном режиме.As with earlier versions, we recommend recovering from inconsistencies by restoring the affected data from a backup; however, you may be able to repair the index inconsistencies by rebuilding the nonclustered index offline. Дополнительные сведения см. в разделе DBCC CHECKDB (Transact-SQL).For more information, see DBCC CHECKDB (Transact-SQL).

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

Первым шагом в определении, какой метод дефрагментации следует использовать, будет анализ индекса на предмет степени фрагментации.The first step in deciding which defragmentation method to use is to analyze the index to determine the degree of fragmentation.

Выявление фрагментации индексов rowstoreDetecting fragmentation on rowstore indexes

Системная функция 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. Дополнительные сведения см. в статье Выполнение операции с индексами в сети.For more information, see Perform Index Operations 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. Например, если индекс используется преимущественно для операций сканирования, устранение фрагментации позволяет повысить производительность при их выполнении.For example, if a given index is used mainly for scan operations, removing fragmentation can improve performance of these operations. Выигрыш в производительности не так заметен в случае с индексами, которые используются в первую очередь для операций поиска.The performance benefit is less noticeable for indexes that are used primarily for seek operations. Аналогичным образом устранение фрагментации в куче (таблице без кластеризованного индекса) особенно полезно для операций сканирования некластеризованного индекса, но мало влияет на операции поиска.Similarly, removing fragmentation in a heap (a table with no clustered index) is especially useful for nonclustered index scan operations, but has little effect in lookup operations.

При очень низких уровнях фрагментации (менее 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).

Примечание

Перестроение или реорганизация малых индексов rowstore часто не приводит к понижению уровня фрагментации.Rebuilding or reorganizing small rowstore 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.

Выявление фрагментации индексов columnstoreDetecting fragmentation on columnstore indexes

С помощью динамического административного представления sys.dm_db_column_store_row_group_physical_stats можно определить процент удаленных строк, что служит хорошим показателем фрагментации в группе строк.By using the DMV sys.dm_db_column_store_row_group_physical_stats you can determine the percentage of deleted rows, which is a good measure for the fragmentation in a rowgroup. С помощью этих сведений можно вычислить фрагментацию в определенном индексе, всех индексах в таблице, всех индексах в базе данных или всех индексах во всех базах данных.Use this information to compute the fragmentation in a specific index, all indexes on a table, all indexes in a database, or all indexes in all databases.

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

СтолбецColumn ОписаниеDescription
total_rowstotal_rows Количество строк, которые физически хранятся в группе строк.Number of rows physical stored in the row group. Для сжатых групп строк учитываются строки, помеченные как удаленные.For compressed row groups, this includes the rows that are marked deleted.
deleted_rowsdeleted_rows Количество строк, физически хранящихся в сжатой группе строк и помеченных для удаления.Number of rows physically stored in a compressed row group that are marked for deletion. Для групп строк, которые находятся в разностном хранилище, значение равно 0.0 for row groups that are in the delta store.

Его можно использовать для вычисления фрагментации с помощью формулы 100*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0).This can be used to compute the fragmentation using the formula 100*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0). Выяснив степень фрагментации, используйте нижеследующую таблицу для определения наиболее подходящего метода устранения фрагментации.After the degree of fragmentation is known, use the following table to determine the best method to correct the fragmentation.

Значение вычисленной фрагментации в процентахcomputed fragmentation in percent value Применимо к версииApplies to version Корректирующая инструкцияCorrective statement
> = 20 %> = 20% SQL Server 2012 (11.x)SQL Server 2012 (11.x) и SQL Server 2014 (12.x)SQL Server 2014 (12.x)and SQL Server 2014 (12.x)SQL Server 2014 (12.x) ALTER INDEX REBUILDALTER INDEX REBUILD
> = 20 %> = 20% Начиная с SQL Server 2016 (13.x)SQL Server 2016 (13.x)Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) ALTER INDEX REORGANIZEALTER INDEX REORGANIZE

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

В определенных условиях перестроение кластеризованного индекса автоматически приводит к перестроению всех некластеризованных индексов, на которые ссылается ключ кластеризации, если физические или логические идентификаторы в записях некластеризованного индекса должны изменяться.Under certain conditions, rebuilding a clustered index will automatically rebuild any nonclustered index that reference the clustering key, if the physical or logical identifiers contained in the nonclustered index records need to change.

Автоматическое перестроение всех некластеризованных индексов rowstore в таблице происходит в следующих ситуациях:Scenarios that force all rowstore nonclustered indexes to be automatically rebuilt on a table:

  • создание кластеризованного индекса для таблицы;Creating a clustered index on a table
  • удаление кластеризованного индекса, в результате которого таблица сохраняется как куча;Removing a clustered index, causing the table to be stored as a heap
  • включение столбцов в ключ кластеризации или исключение столбцов из него.Changing the clustering key to include or exclude columns

Автоматическое перестроение всех некластеризованных индексов rowstore в таблице не требуется в следующих ситуациях:Scenarios that do not require all rowstore nonclustered indexes to be automatically rebuilt on a table:

  • перестроение уникального кластеризованного индекса;Rebuilding a unique clustered index
  • перестроение не уникального кластеризованного индекса;Rebuilding a non-unique clustered index
  • изменение схемы индекса, например применение схемы секционирования к кластеризованному индексу или перемещение кластеризованного индекса в другую файловую группу.Changing the index schema, such as applying a partitioning scheme to a clustered index or moving the clustered index to a different filegroup

Важно!

Индекс нельзя реорганизовать или перестроить, если файловая группа, в которой он расположен, находится в автономном режиме или предназначена только для чтения.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.

Важно!

При перестроении индекса на физическом носителе должно быть достаточно места для хранения двух копий индекса.While an index rebuild occurs, the physical media must have enough space to store two copies of the index. По завершении перестроения SQL ServerSQL Server удаляет исходный индекс.When the rebuild is finished, SQL ServerSQL Server deletes the original index.

Если указано ключевое слово ALL с инструкцией ALTER INDEX, реорганизуются реляционные кластеризованные и некластеризованные индексы и XML-индексы для таблицы.When ALL is specified with the ALTER INDEX statement, relational indexes, both clustered and nonclustered, and XML indexes on the table are reorganized.

Вопросы, связанные с перестроением индекса columnstoreConsiderations specific to rebuilding a columnstore index

При перестроении индекса columnstore Компонент Database EngineDatabase Engine считывает все данные из исходного индекса columnstore, включая разностное хранилище.When rebuilding a columnstore index, the Компонент Database EngineDatabase Engine reads all data from the original columnstore index, including the delta store. Объединяет данные в новые группы строк и сжимает columnstore в группы строк.It combines the data into new rowgroups, and compresses the rowgroups into the columnstore. Компонент Database EngineDatabase Engine дефрагментирует таблицу columnstore, физически удаляя строки, которые были логически удалены из таблиц. Удаленные байты освобождают место на диске.The Компонент Database EngineDatabase Engine defragments the columnstore by physically deleting rows that have been logically deleted from the table; the deleted bytes are reclaimed on the disk.

Перестраивайте секцию, а не всю таблицу:Rebuild a partition instead of the entire table:

  • Если индекс большой, то перестроение всей таблицы занимает много времени и на диске должно хватать места для сохранения дополнительной копии во время перестроения.Rebuilding the entire table takes a long time if the index is large, and it requires enough disk space to store an additional copy of the index during the rebuild. Обычно бывает необходимо перестроить только недавно использованную секцию.Usually it is only necessary to rebuild the most recently used partition.

  • Для секционированных таблиц нет необходимости перестраивать весь индекс columnstore, поскольку фрагментация вероятна только в секциях, которые были недавно изменены.For partitioned tables, you do not need to rebuild the entire columnstore index because fragmentation is likely to occur in only the partitions that have been modified recently. Таблицы фактов и большие таблицы измерений обычно бывают секционированы для выполнения операций резервного копирования и управления с фрагментами данных таблицы.Fact tables and large dimension tables are usually partitioned in order to perform backup and management operations on chunks of the table.

Перестраивайте секцию после масштабных операций DML:Rebuild a partition after heavy DML operations:

  • Перестроение секции дефрагментирует ее и уменьшит занимаемое место на диске.Rebuilding a partition will defragment the partition and reduce disk storage. При перестройке из columnstore будут удалены все строки, помеченные для удаления, а все группы строк будут перемещены из разностного хранилища в columnstore.Rebuilding will delete all rows from the columnstore that are marked for deletion, and it will move all rowgroups from the delta store into the columnstore. Обратите внимание, что в разностном хранилище может быть несколько групп строк, каждая из которых может содержать менее миллиона строк.Note, there can be multiple rowgroups in the delta store that have less than one million rows.

Перестраивайте секцию после загрузки данных:Rebuild a partition after loading data:

  • Это гарантирует, что все данные будут храниться в columnstore.This ensures all data is stored in the columnstore. Если при каждом параллельном процессе одновременно загружается менее 100 000 строк в одну и ту же секцию, в итоге в секции может оказаться несколько разностных хранилищ.When concurrent processes each load less than 100,000 rows into the same partition at the same time, the partition can end up with multiple delta stores. При перестроении все строки перемещаются из разностного хранилища в columnstore.Rebuilding will move all delta store rows into the columnstore.

Вопросы, связанные с реорганизацией индекса columnstoreConsiderations specific to reorganizing a columnstore index

При реорганизации индекса columnstore Компонент Database EngineDatabase Engine сжимает каждую разностную группу строк CLOSED в columnstore в виде сжатой группы строк.When reorganizing a columnstore index, the Компонент Database EngineDatabase Engine compresses each CLOSED delta rowgroup into the columnstore as a compressed rowgroup. Начиная с SQL Server 2016 (13.x)SQL Server 2016 (13.x) и в решении База данных SQL AzureAzure SQL Database с помощью команды REORGANIZE в оперативном режиме выполняются следующие дополнительные действия по дефрагментационной оптимизации:Starting with SQL Server 2016 (13.x)SQL Server 2016 (13.x) and in База данных SQL AzureAzure SQL Database, the REORGANIZE command performs the following additional defragmentation optimizations online:

  • Физически удаляет строки из группы строк, если были логически удалено 10 % или более строк.Physically removes rows from a rowgroup when 10% or more of the rows have been logically deleted. Удаленные байты освобождают место на физическом носителе.The deleted bytes are reclaimed on the physical media. Например, если в сжатой группе из 1 миллиона строк удалено 100 тысяч строк, SQL Server удалит эти строки и выполнит повторное сжатие группы с 900 тыс. строк.For example, if a compressed row group of 1 million rows has 100K rows deleted, SQL Server will remove the deleted rows and recompress the rowgroup with 900k rows. Группа будет сохранена в хранилище за счет удаления удаленных строк.It saves on the storage by removing deleted rows.

  • Объединяет одну или несколько сжатых группах строк для увеличения числа строк для каждой группы до максимального значения, составляющего 1 024 576 строк.Combines one or more compressed rowgroups to increase rows per rowgroup up to the maximum of 1,024,576 rows. Например, при массовом импорте 5 пакетов с 102 400 строками вы получите 5 сжатых групп строк.For example, if you bulk import 5 batches of 102,400 rows you will get 5 compressed rowgroups. При выполнении команды REORGANIZE эти групп строк будут объединены в 1 сжатую группу строк, содержащую 512 000 строк.If you run REORGANIZE, these rowgroups will get merged into 1 compressed rowgroup of size 512,000 rows. Предполагается отсутствие ограничений на размер словаря или объем памяти.This assumes there were no dictionary size or memory limitations.

  • Компонент Database EngineDatabase Engine попытается объединить группу строк, в которой 10 % строк или больше были логически удалены, с одной или несколькими группами строк.For rowgroups in which 10% or more of the rows have been logically deleted, the Компонент Database EngineDatabase Engine will try to combine this rowgroup with one or more rowgroups. Например, группа строк 1 сжимается с 500 000 строками, а группа строк 21 сжимается с максимум 1 048 576 строками.For example, rowgroup 1 is compressed with 500,000 rows and rowgroup 21 is compressed with the maximum of 1,048,576 rows. В группе строк 21 удалено 60 % строк и осталось 409 830 строк.Rowgroup 21 has 60% of the rows deleted which leaves 409,830 rows. Компонент Database EngineDatabase Engine объединяет эти две группы строк для сжатия новой группы строк, содержащей 909 830 строк.The Компонент Database EngineDatabase Engine favors combining these two rowgroups to compress a new rowgroup that has 909,830 rows.

После загрузки данных в разностном хранилище может находиться несколько небольших групп строк.After performing data loads, you can have multiple small rowgroups in the delta store. С помощью инструкции ALTER INDEX REORGANIZE можно принудительно отправить все группы строк в columnstore, а затем объединить их в меньшее число групп строк с большим количеством строк внутри.You can use ALTER INDEX REORGANIZE to force all of the rowgroups into the columnstore, and then to combine the rowgroups into fewer rowgroups with more rows. Операция реорганизации также приведет к удалению строк, которые были удалены из columnstore.The reorganize operation will also remove rows that have been deleted from the columnstore.

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

Перестроение индексов rowstore с более чем 128 экстентами осуществляется в два этапа — это логическое и физическое перестроение.Rowstore 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 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. Если количество секций превышает 1000, рекомендуется использовать только выровненные индексы.Microsoft recommends using only aligned indexes when the number of partitions exceed 1,000.

Индекс нельзя реорганизовать или перестроить, если файловая группа, в которой он расположен, находится в автономном режиме или настроена только для чтения.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.

Когда индекс реорганизуется в SQL ServerSQL Server, статистика не обновляется.When an index is reorganized in SQL ServerSQL Server, statistics are not updated.

Индекс нельзя реорганизовать, если для ALLOW_PAGE_LOCKS задано состояние OFF.An index cannot be reorganized when ALLOW_PAGE_LOCKS is set to OFF.

В версиях, предшествующих SQL Server 2017 (14.x)SQL Server 2017 (14.x), перестроение кластеризованного индекса columnstore выполняется в автономном режиме.Up to SQL Server 2017 (14.x)SQL Server 2017 (14.x), rebuilding a clustered columnstore index is an offline operation. Компонент Database EngineDatabase Engine должен получить монопольную блокировку в таблице или секции на время выполнения перестроения.The Компонент Database EngineDatabase Engine has to acquire an exclusive lock on the table or partition while the rebuild occurs. Данные находятся в автономном режиме и недоступны во время перестроения даже при использовании NOLOCK, изоляции моментальных снимков с уровнем READ COMMITED (RCSI) или обычной изоляции моментальных снимков.The data is offline and unavailable during the rebuild even when using NOLOCK, Read-committed Snapshot Isolation (RCSI), or Snapshot Isolation.
Начиная с SQL Server 2019 (15.x)SQL Server 2019 (15.x) кластеризованный индекс columnstore можно перестраивать с помощью параметра ONLINE=ON.Starting with SQL Server 2019 (15.x)SQL Server 2019 (15.x), a clustered columnstore index can be rebuilt using the ONLINE=ON option.

Для таблицы Хранилища данных SQL Azure с упорядоченным кластеризованным индексом columnstore ALTER INDEX REBUILD будет выполнять повторную сортировку данных с помощью tempdb.For an Azure SQL Data Warehouse table with an ordered clustered columnstore index, ALTER INDEX REBUILD will re-sort the data using TempDB. Отслеживайте базу данных tempdb во время операций перестроения.Monitor TempDB during rebuild operations. Если вам необходимо больше места в базе данных tempdb, можно увеличить объем хранилища данных.If you need more TempDB space, scale up the data warehouse. Масштаб можно вернуть обратно после перестроения индекса.Scale back down once the index rebuild is complete.

Для таблицы Хранилища данных SQL Azure с упорядоченным кластеризованным индексом columnstore ALTER INDEX REORGANIZE не будет выполнять повторную сортировку данных.For an Azure SQL Data Warehouse table with an ordered clustered columnstore index, ALTER INDEX REORGANIZE does not re-sort the data. Для повторной сортировки данных используйте ALTER INDEX REBUILD.To resort the data use ALTER INDEX REBUILD.

безопасность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

Примечание

Среда Management StudioManagement Studio нельзя использовать для вычисления фрагментации индексов columnstore.cannot be used to compute fragmentation of columnstore indexes. См. пример с Transact-SQLTransact-SQL далее.Use the Transact-SQLTransact-SQL example below.

  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

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

В приведенном ниже примере рассчитывается средний процент фрагментации всех индексов в таблице HumanResources.Employee базы данных AdventureWorks2016.The following example finds the average fragmentation percentage of all indexes in the HumanResources.Employee table in the AdventureWorks2016 database.

SELECT a.object_id, object_name(a.object_id) AS TableName,
      a.index_id, name AS IndedxName, avg_fragmentation_in_percent
FROM sys.dm_db_index_physical_stats
    (DB_ID (N'AdventureWorks2016_EXT')
        , OBJECT_ID(N'HumanResources.Employee')
        , NULL
        , NULL
        , NULL) AS a
INNER JOIN sys.indexes AS b
    ON a.object_id = b.object_id
    AND a.index_id = b.index_id;
GO

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

object_id   TableName    index_id    IndexName                                             avg_fragmentation_in_percent
----------- ------------ ----------- ----------------------------------------------------- ------------------------------
1557580587  Employee     1           PK_Employee_BusinessEntityID                          0
1557580587  Employee     2           IX_Employee_OrganizationalNode                        0
1557580587  Employee     3           IX_Employee_OrganizationalLevel_OrganizationalNode    0
1557580587  Employee     5           AK_Employee_LoginID                                   66.6666666666667
1557580587  Employee     6           AK_Employee_NationalIDNumber                          50
1557580587  Employee     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.

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

В приведенном ниже примере рассчитывается средний процент фрагментации всех индексов в таблице dbo.FactResellerSalesXL_CCI базы данных AdventureWorksDW2016.The following example finds the average fragmentation percentage of all indexes in the dbo.FactResellerSalesXL_CCI table in the AdventureWorksDW2016 database.

SELECT i.object_id,   
    object_name(i.object_id) AS TableName,   
    i.index_id,   
    i.name AS IndexName,  
    100*(ISNULL(SUM(CSRowGroups.deleted_rows),0))/NULLIF(SUM(CSRowGroups.total_rows),0) AS 'Fragmentation'
FROM sys.indexes AS i  
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS CSRowGroups  
    ON i.object_id = CSRowGroups.object_id 
      AND i.index_id = CSRowGroups.index_id   
WHERE object_name(i.object_id) = 'FactResellerSalesXL_CCI'  
GROUP BY i.object_id, i.index_id, i.name 
ORDER BY object_name(i.object_id), i.name;

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

object_id   TableName                   index_id    IndexName                       Fragmentation
----------- --------------------------- ----------- ------------------------------- ---------------
114099447   FactResellerSalesXL_CCI     1           IndFactResellerSalesXL_CCI      0

(1 row(s) affected)

Устранение фрагментации с помощью 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.

Примечание

Реорганизация индекса columnstore с помощью Среда Management StudioManagement Studio приведет к объединению групп строк COMPRESSED, но не к сжатию всех групп строк в columnstore.Reorganizing a columnstore index using Среда Management StudioManagement Studio will combine COMPRESSED rowgroups together, but does not force all rowgroups to be compressed into the columnstore. Группы строк CLOSED будут сжаты в columnstore в отличие от групп строк OPEN.CLOSED rowgroups will be compressed but OPEN rowgroups will not be compressed into the columnstore. Чтобы сжать все групп строк, см. пример с Transact-SQLTransact-SQL далее.To compress all rowgroups, use the Transact-SQLTransact-SQL example below.

Реорганизация всех индексов в таблице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

Примечание

Дополнительные примеры использования Transact-SQLTransact-SQL для перестроения или реорганизации индексов см. в разделе Примеры: индексы columnstore и Примеры: индексы rowstore.For more examples about using Transact-SQLTransact-SQL to rebuild or reorganize indexes, see ALTER INDEX Examples: Columnstore Indexes and ALTER INDEX Examples: Rowstore Indexes.

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

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

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
   ON HumanResources.Employee
   REORGANIZE;

В приведенном ниже примере показано, как реорганизовать индекс columnstore IndFactResellerSalesXL_CCI в таблице dbo.FactResellerSalesXL_CCI базы данных AdventureWorksDW2016.The following example reorganizes the IndFactResellerSalesXL_CCI columnstore index on the dbo.FactResellerSalesXL_CCI table in the AdventureWorksDW2016 database.

-- This command will force all CLOSED and OPEN rowgroups into the columnstore.  
ALTER INDEX IndFactResellerSalesXL_CCI 
   ON FactResellerSalesXL_CCI   
   REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON); 

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

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

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

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

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

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

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

В приведенном ниже примере показано, как перестроить все индексы, связанные с таблицей базы данных AdventureWorks2016, используя ключевое слово ALL.The following example rebuilds all indexes associated with the table in the AdventureWorks2016 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 (Transact-SQL).

Автоматическое управление индексами и статистикой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

Руководство по архитектуре и разработке индексов SQL Server SQL Server Index Architecture and Design Guide
Выполнение операций с индексами в оперативном режимеPerform Index Operations Online
ALTER INDEX (Transact-SQL) ALTER INDEX (Transact-SQL)
Адаптивная дефрагментация индексов Adaptive Index Defrag
CREATE STATISTICS (Transact-SQL) CREATE STATISTICS (Transact-SQL)
UPDATE STATISTICS (Transact-SQL) UPDATE STATISTICS (Transact-SQL)
Производительность запросов индексов columnstore Columnstore Indexes Query Performance
Начало работы с Columnstore для получения операционной аналитики в реальном времени Get started with Columnstore for real-time operational analytics
Индексы сolumnstore для хранилищ данных Columnstore Indexes for Data Warehousing
Индексы columnstore и политика слияния для групп строкColumnstore indexes and the merge policy for rowgroups