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

ОБЛАСТЬ ПРИМЕНЕНИЯ ЭТОЙ СТАТЬИ:даSQL Server (начиная с 2008)даБаза данных SQL AzureнетХранилище данных SQL AzureнетParallel Data WarehouseTHIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)yesAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

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

  • Кластеризованные индексы должны создаваться, перестраиваться или удаляться в режиме вне сети, если базовая таблица содержит следующие типы данных больших объектов: image, ntextи text.

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

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

  • Индексы можно возобновить с места остановки после непредвиденного сбоя, отработки отказа базы данных или команды PAUSE. См. раздел Alter Index.

Примечание

Операции с индексами в сети доступны не во всех выпусках MicrosoftMicrosoft SQL ServerSQL Server. Список функций, поддерживаемых различными выпусками SQL ServerSQL Server, см. в этой статье.

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

Операция с индексами в сети Исключенные индексы Прочие ограничения
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) Кластеризованный индекс

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примечание

См. раздел Alter Index.

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

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

  • Возможность усекать журналы усечений во время операции перестроения индекса (выполнение этой операции для регулярной операции с индексами в сети невозможно).

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

Важно!

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

Как правило, возобновляемые и невозобновляемые операции перестроения индекса в режиме "в сети" выполняются с одинаковой производительностью. Если возобновляемый индекс обновляется, пока операция перестроения индекса приостановлена:

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

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

Об операциях с индексами в сети

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

ALTER INDEX (Transact-SQL)

CREATE INDEX (Transact-SQL)