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

Рекомендации по операциям с индексами в режиме "в сети"

  • Статья

Область применения:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureБаза данных SQL в Microsoft Fabric

При выполнении операций с индексами в сети придерживайтесь следующих правил.

  • Кластеризованные индексы должны создаваться, перестраиваться или удаляться в режиме вне сети, если базовая таблица содержит следующие типы данных больших объектов: image, ntextи text.
  • Неуникальные некластеризованные индексы можно создавать в режиме онлайн, если таблица содержит столбцы с типами данных LOB, но ни один из этих столбцов не используется в определении индекса ни в качестве ключевых, ни в качестве включенных столбцов.
  • Индексы локальных временных таблиц не могут создаваться, перестраиваться и удаляться в интерактивном режиме. Это ограничение не относится к индексам глобальных временных таблиц.
  • Вы можете запустить операцию индекса в сети в качестве повторной операции с помощью предложения RESUMABLECREATE INDEX или alter INDEX ALTER INDEX. Операция индекса, поддерживающая возобновление, может перезапуститься после непредвиденного сбоя, переключения базы данных на отказоустойчивый режим или команды ALTER INDEX PAUSE и продолжить работу с места её прерывания.

Примечание.

Операции с индексами в Сети недоступны в каждом выпуске Microsoft SQL Server. Список функций, поддерживаемых выпусками SQL Server, см. в выпусках и поддерживаемых функциях SQL Server 2022.

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

Операция с индексами в сети Исключенные индексы Прочие ограничения
ALTER INDEX REBUILD Отключенный кластеризованный индекс или отключенное индексированное представление

XML-индекс

Индекс локальной временной таблицы		 Указание ключевого слова ALL может привести к сбою операции, если таблица содержит исключенный индекс.

На перестроение отключенных индексов налагаются дополнительные ограничения. Дополнительные сведения см. в статье Отключение индексов и ограничений.
CREATE INDEX XML-индекс

Первоначальный уникальный кластеризованный индекс представления.

Индекс на локальной временной таблице
CREATE INDEX WITH DROP_EXISTING Отключенный кластеризованный индекс или отключенное индексированное представление

Индекс локальной временной таблицы

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

XML-индекс

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

Индекс в локальной временной таблице		 Не удается указать несколько индексов в одной инструкции.
ALTER TABLE ADD CONSTRAINT (PRIMARY KEY или UNIQUE) Индекс локальной временной таблицы

Кластеризованный индекс		 Допускается только одно придаточное предложение за раз. Например, нельзя добавлять или удалять ограничения для PRIMARY KEY и UNIQUE в этой же инструкции ALTER TABLE.
ALTER TABLE DROP CONSTRAINT (PRIMARY KEY или UNIQUE) Кластеризованный индекс

Базовая таблица не может быть изменена, усечена или удалена во время выполнения операции индекса в сети.

Опция онлайн настройки (ON или OFF), указанная при создании или удалении кластерного индекса, применяется к любым некластерным индексам, которые необходимо перестроить. Например, если кластеризованный индекс создается в сети с помощью CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, все связанные некластеризованные индексы также повторно создаются в Сети.

При создании или перестроении UNIQUE индекса онлайн построитель индексов и параллельная транзакция пользователя могут попытаться вставить один и тот же ключ, что приводит к нарушению уникальности. Если строка, введенная пользователем, вставляется в новый индекс (целевой) до перемещения исходной строки из исходной таблицы в новый индекс, операция онлайн-индекса завершается ошибкой.

Хотя это редкость, онлайн операция индексации может вызвать взаимоблокировку при взаимодействии с обновлениями базы данных из-за действий пользователя или приложения. В этих редких случаях в качестве жертвы взаимоблокировки выбирается активность пользователя или приложения.

Вы можете выполнять параллельные онлайн-операции DDL индекса на одной и той же таблице или представлении только при условии создания нескольких новых некластеризованных индексов или реорганизации некластеризованных индексов. Все остальные попытки выполнения операций с индексами в сети завершаются ошибкой. Например, невозможно создать новый индекс в сети при перестроении существующего индекса в той же таблице.

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

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

Для операций с индексами в сети требуется больше места на диске, чем в автономном режиме.

  • Для операций создания и перестроения индексов требуется дополнительное пространство для создаваемого или перестраиваемого индекса. Как правило, это дополнительное пространство совпадает с текущим пространством, занятое индексом, но оно может быть больше или меньше в зависимости от сжатия, используемого в текущем или перестроенном индексе.
  • Кроме того, дисковое пространство используется для временного индекса сопоставления. Этот временный индекс применяется в операциях с индексами в сети при создании, перестроении или удалении кластеризованных индексов.
  • Удаление кластеризованного индекса в режиме "в сети" требует столько же места, сколько и его создание (или перестроение) в режиме "в сети".

Дополнительные сведения см. в разделе требования к дисковому пространству для операций DDL индекса.

Вопросы, касающиеся производительности

Хотя онлайн-операции с индексами допускают одновременное выполнение операций обновления, эти операции могут занимать больше времени, если активность обновления высокая. Как правило, операции с индексами в сети медленнее, чем эквивалентные автономные операции индексов независимо от уровня параллельного обновления.

Поскольку исходные и целевые структуры сохраняются во время операции онлайн-индексации, использование ресурсов для вставки, обновления и удаления транзакций увеличивается, потенциально удвоено. Это может привести к снижению производительности и увеличению использования ресурсов, особенно времени ЦП, во время операции индексации. Операции с индексами в сети полностью записываются в журнал.

Хотя мы рекомендуем онлайн операции, вам следует оценить вашу среду и конкретные требования. Это может быть оптимально для запуска операций индексов в автономном режиме. При этом доступ пользователей к данным ограничен во время операции, но операция завершается быстрее и использует меньше ресурсов.

На компьютерах с несколькими обработчиками, работающих под управлением SQL Server 2016 (13.x) и более поздних версий, операции с индексами могут использовать параллелизм для выполнения операций сканирования и сортировки, связанных с инструкцией индекса. Можно использовать параметр MAXDOP индекса для управления степенью параллелизма операции онлайн-индекса. Таким образом, можно сбалансировать ресурсы, используемые операцией индекса с ресурсами одновременных пользователей. Дополнительные сведения см. в разделе Настройка параллельных операций индексов. Дополнительные сведения о выпусках SQL Server, поддерживающих параллельные операции индексов, см. в разделе Выпуски и поддерживаемые функции SQL Server 2022.

Так как общая блокировка (S) или блокировка изменения схемы (Sch-M) проводится на заключительном этапе операции индекса, будьте осторожны при выполнении операции онлайн-индекса внутри явной транзакции пользователя, например в блоке BEGIN TRANSACTION ... COMMIT. Это приводит к тому, что блокировки будут храниться до конца транзакции, потенциально блокируя другие рабочие нагрузки.

Если блокировки страниц индекса отключены с помощью ALLOW_PAGE_LOCKS=OFF, онлайн перестроение индексов может увеличить фрагментацию индекса при выполнении с MAXDOP больше 1. Дополнительные сведения см. в статье Как это работает. Перестроение индекса в сети может привести к увеличению фрагментации.

Рекомендации по журналу транзакций

Крупномасштабные операции индексов, выполняемые в автономном режиме или в сети, могут создавать большие объемы журнала транзакций. Это связано с тем, что операции перестроения индексов в автономном режиме и в сети полностью регистрируются. Чтобы убедиться, что операция индекса может быть откатена, журнал транзакций не может быть усечен до завершения операции индекса; однако журнал можно сохранить в резервной копии во время операции индекса.

Таким образом, журнал транзакций должен иметь достаточно места для хранения как транзакций операций индекса, так и любых параллельных транзакций пользователей во время операции индекса. Дополнительные сведения см. в разделе Дисковое пространство журнала транзакций для операций с индексами.

Операции с индексами онлайн не приводят к значительному росту журнала транзакций, если включено ускоренное восстановление базы данных (ADR).

Специфика хранилища устойчивых версий

Если ADR включен, создание или перестроение большого индекса в Сети может значительно увеличить размер хранилища постоянных версий (PVS) во время выполнения операции индекса. Убедитесь, что база данных имеет достаточно свободного места для роста PVS. Дополнительные сведения см. в статье Мониторинг и устранение неполадок с ускорением восстановления базы данных.

Соображения по возобновляемым индексам

Параметр индекса RESUMABLE для CREATE INDEX и ALTER INDEX применяется к SQL Server (ALTER INDEX начиная с SQL Server 2017 (14.x) и CREATE INDEX начиная с SQL Server 2019 (15.x)), Базы данных SQL Azure и Управляемого экземпляра SQL Azure. Дополнительные сведения см. в разделе CREATE INDEX и ALTER INDEX.

Чтобы использовать параметр RESUMABLE, необходимо также использовать параметр ONLINE. При выполнении возобновляемого создания или перестроения индекса применяются следующие указания.

  • У вас есть лучший контроль над управлением, планированием и расширением периодов обслуживания индекса. Вы можете многократно приостанавливать и возобновлять операцию создания или перестроения индекса, чтобы она соответствовала вашим временным окнам обслуживания.
  • Вы можете восстановиться после сбоев при создании или перестроении индексов (таких как аварийное переключение базы данных или нехватка места на диске), без необходимости начинать операцию индекса заново.
  • При приостановке операции с индексами исходный и вновь созданный индекс требуют места на диске и обновления во время операций DML.
  • Параметр SORT_IN_TEMPDB=ON не поддерживается.
  • Отключенные индексы не поддерживаются.

Совет

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

Как правило, нет разницы в производительности между возобновляемыми и невозобновляемыми онлайн-операциями индексирования. Для возобновляемых операций CREATE INDEXесть постоянные накладные расходы, которые могут привести к заметно более медленным операциям на небольших таблицах.

При приостановке возобновляемой операции индекса:

  • В основном ориентированные на чтение нагрузки сталкиваются с незначительным снижением производительности.
  • Для рабочих нагрузок с большим количеством обновлений может возникнуть некоторое снижение пропускной способности в зависимости от специфики рабочей нагрузки.

Как правило, нет разницы в качестве дефрагментации между созданием или перестроением возобновляемого и не возобновляемого онлайн индекса.

Примечание.

Хотя операция индекса в сети приостановлена, любая транзакция, требующая монопольной блокировки на уровне таблицы (X) таблицы, содержащей приостановленный индекс, завершается сбоем. Например, это может произойти с операциями INSERT ... WITH (TABLOCK). В этом случае вы получите ошибку 10637:

Cannot perform this operation on '<object name>' with ID (<object ID>) as one or more indexes are currently in resumable index rebuild state. Please refer to sys.index_resumable_operations for more details.

Чтобы устранить ошибку 10637, удалите TABLOCK подсказку из транзакции или возобновите операцию индекса и дождитесь ее завершения, прежде чем повторно попытаться выполнить транзакцию.

Параметры режима "в сети" по умолчанию

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

Можно задать любой параметр как FAIL_UNSUPPORTED, WHEN_SUPPORTEDили OFF. Можно задать разные значения для ELEVATE_ONLINE и ELEVATE_RESUMABLE. Дополнительные сведения см. в ALTER DATABASE SCOPED CONFIGURATION.

ELEVATE_ONLINE и ELEVATE_RESUMABLE применяются только к инструкциям DDL, которые поддерживают онлайн и возобновляемый синтаксис соответственно. Например, если вы пытаетесь создать XML-индекс с ELEVATE_ONLINE=FAIL_UNSUPPORTED, операция выполняется в автономном режиме, так как XML-индексы не поддерживают параметр ONLINE. Параметры влияют только на инструкции DDL, отправленные без указания параметра ONLINE или RESUMABLE. Например, отправляя инструкцию с ONLINE=OFF или RESUMABLE=OFF, пользователь может переопределить параметр FAIL_UNSUPPORTED и запустить инструкцию в автономном режиме и/или без возможности возобновления.

Примечание.

ELEVATE_ONLINE и ELEVATE_RESUMABLE не применяются к операциям XML-индекса.

Связанный контент