• Статья

Правила выполнения фоновых операций с индексами

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

  • Кластеризованные индексы должны создаваться, перестраиваться или удаляться в автономном режиме, если базовые таблицы содержат типы данных больших объектов: image, ntext, text, varchar(max), nvarchar(max), varbinary(max) и xml.

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

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

ПримечаниеПримечание

Фоновые операции с индексами доступны только в выпусках SQL Server Enterprise Edition, Developer Edition и Evaluation Edition.

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

Фоновая операция с индексами

Исключенные индексы

Прочие ограничения

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)

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

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

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

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

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

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

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

В общем случае требования к свободному месту на диске при работе с индексами в оперативном и в автономном режиме одинаковы. Исключением является дополнительное место, необходимое для временного сопоставления индекса. Этот временный индекс применяется в фоновых операциях с индексами при создании, перестроении или удалении кластеризованных индексов. Удаление кластеризованного индекса в оперативном режиме требует столько же места, сколько и его создание в оперативном режиме. Дополнительные сведения см. в разделе Требования к месту на диске для DDL-операций индекса.

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

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

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

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

На многопроцессорных компьютерах под управлением выпуска SQL Server Enterprise инструкции индексирования могут потребовать дополнительных ЦП для выполнения операций просмотра и сортировки, как и любые другие запросы. Для управления количеством ЦП, выделенных для выполнения конкретных фоновых операций с индексами, можно воспользоваться параметром индексирования MAXDOP. В этом случае можно распределить потребляемые операцией с индексом ресурсы таким образом, чтобы не пострадали одновременно работающие пользователи. Дополнительные сведения см. в разделе Настройка параллельных операций с индексами.

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

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

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