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

  • Статья

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

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

  • Кластеризованные индексы должны создаваться, перестраиваться или удаляться в режиме вне сети, если базовая таблица содержит следующие типы данных больших объектов: image, ntextи text.
  • Неуникальные некластеризованные индексы могут создаваться в режиме в сети, если таблица содержит типы данных больших объектов (LOB), но при этом, ни один из этих столбцов не участвует в определении индекса, ни в качестве ключевого, ни в качестве неключевого столбца.
  • Индексы локальных временных таблиц не могут создаваться, перестраиваться и удаляться в интерактивном режиме. Это ограничение не относится к индексам глобальных временных таблиц.
  • Индексы можно возобновить, когда он остановился после неожиданного сбоя, отработки отказа базы данных или PAUSE команды. Ознакомьтесь со сведениями об инструкциях Create Index и Alter Index.

Примечание.

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

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

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

XML-индекс

Индекс columnstore

Индекс локальной временной таблицы		 Указание ключевого слова 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 индекса в сети построитель индексов и параллельная транзакция пользователя могут попытаться вставить один и тот же ключ, поэтому нарушая уникальность. Если строка, введенная пользователем, вставляется в новый индекс (целевой) до перемещения исходной строки из исходной таблицы в новый индекс, операция онлайн-индекса завершается ошибкой.

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

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

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

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

Операции с индексами в режиме "в сети" требуют больше дискового пространства, чем автономные операции с индексами.

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

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

Замечания, связанные с быстродействием

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

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

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

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

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

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

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

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

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

Рекомендации по возобновляемому индексу

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

При создании или перестроении возобновляемого индекса в режиме "в сети" следует учитывать следующие рекомендации:

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

Внимание

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

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

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

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

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

Примечание.

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

Msg 10637, Level 16, State 1, Line 32: Cannot perform this operation on 'object' with ID (objectid) 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 параметры конфигурации с заданной областью базы данных. Параметры по умолчанию позволяют избежать случайного выполнения операций, из-за которых таблица базы данных может стать недоступна. Оба варианта вызывают автоматическое повышение уровня определенных операций в режиме "в сети" или возобновление выполнения.
Вы можете задать любой параметр как , или OFF с помощью команды ALTER DATABASE SCOPED CONFIGURATION. WHEN_SUPPORTEDFAIL_UNSUPPORTED Для режима "в сети" и возобновляемого выполнения можно настроить разные значения.

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

Примечание.

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

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