Руководящие принципы для операций с индексамиGuidelines for Online Index Operations

ПРИМЕНЯЕТСЯ к: ДаSQL ServerДабазы данных SQL Azureнехранилище данных SQL Azure не Параллельное хранилище данныхTHIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

При выполнении операций с индексами в сети придерживайтесь следующих правил.When you perform online index operations, the following guidelines apply:

  • Кластеризованные индексы должны создаваться, перестраиваться или удаляться в режиме вне сети, если базовая таблица содержит следующие типы данных больших объектов: image, ntextи text.Clustered indexes must be created, rebuilt, or dropped offline when the underlying table contains the following large object (LOB) data types: image, ntext, and text.

  • Неуникальные некластеризованные индексы могут создаваться в режиме в сети, если таблица содержит типы данных больших объектов (LOB), но при этом, ни один из этих столбцов не участвует в определении индекса, ни в качестве ключевого, ни в качестве неключевого столбца.Nonunique nonclustered indexes can be created online when the table contains LOB data types but none of these columns are used in the index definition as either key or nonkey (included) columns.

  • Индексы локальных временных таблиц не могут создаваться, перестраиваться и удаляться в режиме в сети.Indexes on local temp tables cannot be created, rebuilt, or dropped online. Это ограничение не относится к индексам глобальных временных таблиц.This restriction does not apply to indexes on global temp tables.

  • Индексы можно возобновить с места остановки после непредвиденного сбоя, отработки отказа базы данных или команды PAUSE.Indexes can be resumed from where it stopped after an unexpected failure, database failover, or a PAUSE command. См. раздел Alter Index.See Alter Index.

Примечание

Операции с индексами в сети доступны не во всех выпусках MicrosoftMicrosoft SQL ServerSQL Server.Online index operations are not available in every edition of MicrosoftMicrosoft SQL ServerSQL Server. Список функций, поддерживаемых различными выпусками SQL ServerSQL Server, см. в этой статье.For a list of features that are supported by the editions of SQL ServerSQL Server, see Features supported by editions.

В следующей таблице перечислены операции с индексами, которые могут быть выполнены в сети, а также индексы, которые исключаются из этих операций, и ограничения для возобновляемого индекса.The following table shows the index operations that can be performed online, the indexes that are excluded from these online operations, and resumable index restrictions. Кроме того, в ней указаны дополнительные ограничения.Additional restrictions are also included.

Операция с индексами в сетиOnline index operation Исключенные индексыExcluded indexes Прочие ограниченияOther restrictions
ALTER INDEX REBUILDALTER INDEX REBUILD Отключенный кластеризованный индекс или отключенное индексированное представлениеDisabled clustered index or disabled indexed view

XML-индексXML index

Индекс columnstoreColumnstore index

Индекс локальной временной таблицыIndex on a local temp table
Указание ключевого слова ALL может привести к ошибке выполнения операции, если таблица содержит исключенный индекс.Specifying the keyword ALL may cause the operation to fail when the table contains an excluded index.

На перестроение отключенных индексов налагаются дополнительные ограничения.Additional restrictions on rebuilding disabled indexes apply. Дополнительные сведения см. в статье Отключение индексов и ограничений.For more information, see Disable Indexes and Constraints.
CREATE INDEXCREATE INDEX XML-индексXML index

Исходные уникальные кластеризованные индексы представлений.Initial unique clustered index on a view

Индекс локальной временной таблицыIndex on a local temp table
CREATE INDEX WITH DROP_EXISTINGCREATE INDEX WITH DROP_EXISTING Отключенный кластеризованный индекс или отключенное индексированное представлениеDisabled clustered index or disabled indexed view

Индекс локальной временной таблицыIndex on a local temp table

XML-индексXML index
DROP INDEXDROP INDEX Отключенный индексDisabled index

XML-индексXML index

Некластеризованный индексNonclustered index

Индекс локальной временной таблицыIndex on a local temp table
В одной инструкции не может быть указано несколько индексов.Multiple indexes cannot be specified within a single statement.
ALTER TABLE ADD CONSTRAINT (PRIMARY KEY или ограничение UNIQUE)ALTER TABLE ADD CONSTRAINT (PRIMARY KEY or UNIQUE) Индекс локальной временной таблицыIndex on a local temp table

Кластеризованный индексClustered index
Допускается только одно вложенное предложение за раз.Only one subclause is allowed at a time. Например: нельзя добавлять и удалять ограничения PRIMARY KEY и UNIQUE в одной и той же инструкции ALTER TABLE.For example, you cannot add and drop PRIMARY KEY or UNIQUE constraints in the same ALTER TABLE statement.
ALTER TABLE DROP CONSTRAINT (PRIMARY KEY или ограничение UNIQUE)ALTER TABLE DROP CONSTRAINT (PRIMARY KEY or UNIQUE) Кластеризованный индексClustered index

Базовая таблица не может быть изменена, усечена или удалена, пока не завершилась операция с индексами в сети.The underlying table cannot be modified, truncated, or dropped while an online index operation is in process.

Настройка параметра ONLINE (ON или OFF), указанная при создании или удалении кластеризованного индекса, относится ко всем перестраиваемым некластеризованным индексам.The online option setting (ON or OFF) specified when you create or drop a clustered index is applied to any nonclustered indexes that must be rebuilt. Например: если кластеризованный индекс построен в режиме в сети с помощью инструкции CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, все связанные с ним некластеризованные индексы также повторно создаются в режиме в сети.For example, if the clustered index is built online by using CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, all associated nonclustered indexes are re-created online also.

При создании или перестроении в сети индекса UNIQUE построитель индексов и выполняющаяся одновременно пользовательская транзакция могут попытаться добавить одно и то же значение ключа, нарушая его уникальность.When you create or rebuild a UNIQUE index online, the index builder and a concurrent user transaction may try to insert the same key, therefore violating uniqueness. Если строка, введенная пользователем, вставляется в новый индекс прежде, чем исходная строка из таблицы-источника перемещается в новый индекс, операция с индексами вне сети завершится ошибкой.If a row entered by a user is inserted into the new index (target) before the original row from the source table is moved to the new index, the online index operation will fail.

С небольшой вероятностью операция с индексами в сети может вызвать взаимоблокировку при работе с базой данных, вызванную работой пользователя или приложения.Although not common, the online index operation can cause a deadlock when it interacts with database updates because of user or application activities. В этих редких случаях компонент Компонент SQL Server Database EngineSQL Server Database Engine определит жертвой взаимоблокировки активность пользователя или приложения.In these rare cases, the Компонент SQL Server Database EngineSQL Server Database Engine will select the user or application activity as a deadlock victim.

Можно выполнять одновременные фоновые DLL-операции индекса одной таблицы или представления только при создании нескольких некластеризованных индексов либо при реорганизации некластеризованных индексов.You can perform concurrent online index DDL operations on the same table or view only when you are creating multiple new nonclustered indexes, or reorganizing nonclustered indexes. Все остальные попытки выполнения операций с индексами в сети завершаются ошибкой.All other online index operations performed at the same time fail. Например: нельзя в режиме в сети создавать новый индекс во время перестроения существующего индекса для этой же таблицы.For example, you cannot create a new index online while rebuilding an existing index online on the same table.

Операцию в сети нельзя выполнить, если индекс содержит столбец с типом больших объектов, и операции в сети в той же транзакции предшествует операция обновления.An online operation cannot be performed when an index contains a column of the large object type, and in the same transaction there are update operations before this online operation. Для решения этой проблемы проводите операцию в сети либо вне такой транзакции, либо в транзакции, но перед операциями обновления.To work around this issue, place the online operation outside the transaction or place it before any updates in the transaction.

Рекомендации по месту на дискеDisk Space Considerations

Операции с индексами в режиме "в сети" требуют больше дискового пространства, чем автономные операции с индексами.Online index operations require more disk space requirements than offline index operations.

  • Для операций создания и перестроения индексов требуется дополнительное пространство.During index creation and index rebuild operations, additional space is required for the index being built (or rebuilt).
  • Кроме того, дисковое пространство используется для временного индекса сопоставления.In addition, disk space is required for the temporary mapping index. Этот временный индекс применяется в операциях с индексами в сети при создании, перестроении или удалении кластеризованных индексов.This temporary index is used in online index operations that create, rebuild, or drop a clustered index.
    • Удаление кластеризованного индекса в режиме "в сети" требует столько же места, сколько и его создание (или перестроение) в режиме "в сети".Dropping a clustered index online requires as much space as creating (or rebuilding) a clustered index online.

Дополнительные сведения см. в статье Disk Space Requirements for Index DDL Operations.For more information, see Disk Space Requirements for Index DDL Operations.

Вопросы производительностиPerformance Considerations

Хотя операции с индексами в сети допускают одновременную работу пользователей, в этом случае они выполняются тем дольше, чем интенсивнее происходит обновление данных.Although online index operations permit concurrent user update activity, the index operations will take longer if the update activity is very heavy. Обычно операции с индексами в сети выполняются медленнее, чем аналогичные операции вне сети, независимо от текущей интенсивности обновления данных.Typically, online index operations will be slower than equivalent offline index operations regardless of the concurrent update activity level.

Поскольку и исходная, и целевая структуры обслуживаются во время выполнения операции с индексами в сети, увеличивается потребление ресурсов при вставке, обновлении и удалении, и это увеличение может доходить до двукратного.Because both the source and target structures are maintained during the online index operation, the resource usage for insert, update, and delete transactions is increased, potentially up to double. Это может привести к снижению производительности и повышению нагрузки на систему, особенно ресурсов ЦП.This could cause a decrease in performance and greater resource usage, especially CPU time, during the index operation. Операции с индексами в сети полностью записываются в журнал.Online index operations are fully logged.

Несмотря на то, что рекомендуется выполнять операции с индексами в сети, необходимо предварительно оценить среду и определенные требования.Although we recommend online operations, you should evaluate your environment and specific requirements. Возможно, оптимальным решением может оказаться переключение в режим вне сети.It may be optimal to run index operations offline. При этом на время выполнения операции пользователи будут иметь ограниченный доступ к данным, но она быстрее закончится и в итоге займет меньше ресурсов.In doing this, users have restricted access to the data during the operation, but the operation finishes faster and uses fewer resources.

На многопроцессорных компьютерах с SQL Server 2016 индексные инструкции, как и любые другие запросы, могут использовать несколько процессоров для операций просмотра и сортировки, связанных с индексной инструкцией.On multiprocessor computers that are running SQL Server 2016, index statements may use more processors to perform the scan and sort operations associated with the index statement just like other queries do. Можно использовать параметр индекса MAXDOP с целью управления максимальным количеством процессоров, используемых для операции с индексами в сети.You can use the MAXDOP index option to control the number of processors dedicated to the online index operation. В этом случае можно распределить потребляемые операцией с индексом ресурсы таким образом, чтобы не пострадали одновременно работающие пользователи.In this way, you can balance the resources that are used by index operation with those of the concurrent users. Дополнительные сведения см. в статье Настройка параллельных операций с индексами.For more information, see Configure Parallel Index Operations. Дополнительные сведения о выпусках SQL Server, поддерживающих параллельные операции с индексами, см. в этой статье.For more information about the editions of SQL Server that support Parallel indexed operations, see Features Supported by editions.

Поскольку на финальной фазе удерживаются блокировки S-lock и Sch-M, будьте внимательны при выполнении операций с индексами в сети внутри явно объявленных пользовательских транзакций (например: в блоке BEGIN TRANSACTION...COMMIT),Because an S-lock or Sch-M lock is held in the final phase of the index operation, be careful when you run an online index operation inside an explicit user transaction, such as BEGIN TRANSACTION...COMMIT block. поскольку в этом случае блокировка будет удерживаться до окончания транзакции, мешая одновременной работе пользователей.Doing this causes the lock to be held until the end of the transaction, therefore impeding user concurrency.

Перестроение индекса в сети может привести к увеличению фрагментации, когда оно выполняется с параметрами MAX DOP > 1 и ALLOW_PAGE_LOCKS = OFF .Online index rebuilding may increase fragmentation when it is allowed to run with MAX DOP > 1 and ALLOW_PAGE_LOCKS = OFF options. Дополнительные сведения см. в статье Как это работает. Перестроение индекса в сети может привести к увеличению фрагментации.For more information, see How It Works: Online Index Rebuild - Can Cause Increased Fragmentation.

Вопросы, касающиеся журнала транзакцийTransaction Log Considerations

Масштабные операции с индексами, выполняемые в режиме в сети или вне сети, могут привести к формированию больших объемов данных, которые вызовут переполнение журнала транзакций.Large-scale index operations, performed offline or online, can generate large data loads that can cause the transaction log to quickly fill. Для гарантии возможности отката операций с индексами журнал транзакций не может быть усечен до завершения операции с индексом, однако может быть выполнено его резервное копирование.To make sure that the index operation can be rolled back, the transaction log cannot be truncated until the index operation has been completed; however, the log can be backed up during the index operation. Иными словами, журнал транзакций должен иметь достаточно места для сохранения и транзакции операции с индексом и текущих пользовательских транзакций на весь период выполнения операции с индексом.Therefore, the transaction log must have sufficient space to store both the index operation transactions and any concurrent user transactions for the duration of the index operation. Дополнительные сведения см. в статье Transaction Log Disk Space for Index Operations.For more information, see Transaction Log Disk Space for Index Operations.

Вопросы перестроения возобновляемого индексаResumable Index Rebuild Considerations

Примечание

Возобновляемый индекс применяется к SQL Server (начиная с SQL Server 2017) и базе данных SQL.The resumable index option applies to SQL Server (Starting with SQL Server 2017) and SQL Database. См. раздел Alter Index.See Alter Index.

При перестраивании возобновляемого индекса в режиме "в сети" следует учитывать следующие рекомендации:When you perform resumable online index rebuild the following guidelines apply:

  • Управление, планирование и разворачивание окна обслуживания индексов.Managing, planning and extending of index maintenance windows. Операцию перестраивания индексов в периоды обслуживания можно приостанавливать и снова запускать многократно.You can pause and restart an index rebuild operation multiple times to fit your maintenance windows.
  • Восстановление после сбоев при перестроении индекса (например, при переходе на другую базу данных или нехватке места на диске).Recovering from index rebuild failures (such as database failovers or running out of disk space).
  • При приостановке операции с индексами исходный и вновь созданный индекс требуют места на диске и обновления во время операций DML.When an index operation is paused, both the original index and the the newly created one require disk space and need to be updated during DML operations.

  • Возможность усекать журналы транзакций во время перестроения индекса (выполнение этой операции для регулярной операции с индексами в сети невозможно).Enables truncation of transaction logs during an index rebuild operation (this operation cannot be performed for a regular online index operation).

  • Параметр SORT_IN_TEMPDB = ON не поддерживается.SORT_IN_TEMPDB=ON option is not supported

Важно!

Возобновляемые перестроения не требуют открытых долгосрочных транзакций, что позволяет усекать журналы во время этой операции и управлять пространством в журнале более эффективно.Resumable rebuild does not require you to keep open a long running transaction, allowing log truncation during this operation and a better log space management. Новая структура позволяет хранить необходимые данные в базе данных вместе со всеми ссылками, необходимыми для перезапуска возобновляемой операции.With the new design, we managed to keep necessary data in a database together with all references required to restart the resumable operation.

Как правило, возобновляемые и невозобновляемые операции перестроения индекса в режиме "в сети" выполняются с одинаковой производительностью.Generally, there is no performance difference between resumable and non-resumable online index rebuild. Если возобновляемый индекс обновляется, пока операция перестроения индекса приостановлена:When you update a resumable index while an index rebuild operation is paused:

  • Для рабочей нагрузки, связанной в основном с чтением, производительность существенно не снижается.For read-mostly workloads, the performance impact is insignificant.
  • Для рабочей нагрузки, связанной с большим объемом обновлений, пропускная способность может упасть (наши тесты показали снижение производительности менее чем на 10 %).For update-heavy workloads, you may experience some throughput degradation (our testing shows less than 10% degradation).

Качество дефрагментации при возобновляемом и невозобновляемом перестроении индекса в режиме "в сети" обычно одинаково.Generally, there is no difference in defragmentation quality between resumable and non-resumable online index rebuild.

Об операциях с индексами в сетиHow Online Index Operations Work

Выполнение операции с индексами в сетиPerform Index Operations Online

ALTER INDEX (Transact-SQL)ALTER INDEX (Transact-SQL)

CREATE INDEX (Transact-SQL)CREATE INDEX (Transact-SQL)