Руководящие принципы для операций с индексами
При выполнении операций с индексами в сети придерживайтесь следующих правил.
Кластеризованные индексы необходимо создавать, перестраивать или удалять в автономном режиме, если базовая таблица содержит следующие типы данных больших объектов ( LOB):
image, ntext иtext.Индексы локальных временных таблиц не могут создаваться, перестраиваться и удаляться в режиме в сети. Это ограничение не относится к индексам глобальных временных таблиц.
Примечание
Операции с индексами в сети доступны не во всех выпусках MicrosoftSQL Server. Список функций, поддерживаемых выпусками SQL Server, см. в разделе Функции, поддерживаемые выпусками SQL Server 2014.
В следующей таблице перечислены операции с индексами, которые могут быть выполнены в режиме в сети и индексы, которые исключаются из этих операций. Кроме того, в ней указаны дополнительные ограничения.
| Операция с индексами в сети | Исключенные индексы | Другие ограничения |
|---|---|---|
| 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. |
Базовая таблица не может быть изменена, усечена или удалена, пока не завершилась операция с индексами в сети.
Настройка параметра ONLINE (ON или OFF), указанная при создании или удалении кластеризованного индекса, относится ко всем перестраиваемым некластеризованным индексам. Например: если кластеризованный индекс построен в режиме в сети с помощью инструкции CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, все связанные с ним некластеризованные индексы также повторно создаются в режиме в сети.
При создании или перестроении в сети индекса UNIQUE построитель индексов и выполняющаяся одновременно пользовательская транзакция могут попытаться добавить одно и то же значение ключа, нарушая его уникальность. Если строка, введенная пользователем, вставляется в новый индекс прежде, чем исходная строка из таблицы-источника перемещается в новый индекс, операция с индексами вне сети завершится ошибкой.
С небольшой вероятностью операция с индексами в сети может вызвать взаимоблокировку при работе с базой данных, вызванную работой пользователя или приложения. В этих редких случаях компонент Компонент SQL Server Database Engine определит жертвой взаимоблокировки активность пользователя или приложения.
Можно выполнять одновременные фоновые DLL-операции индекса одной таблицы или представления только при создании нескольких некластеризованных индексов либо при реорганизации некластеризованных индексов. Все остальные попытки выполнения операций с индексами в сети завершаются ошибкой. Например: нельзя в режиме в сети создавать новый индекс во время перестроения существующего индекса для этой же таблицы.
Операцию в сети нельзя выполнить, если индекс содержит столбец с типом больших объектов, и операции в сети в той же транзакции предшествует операция обновления. Для решения этой проблемы проводите операцию в сети либо вне такой транзакции, либо в транзакции, но перед операциями обновления.
Рекомендации по месту на диске
В общем случае требования к свободному месту на диске при работе с индексами и в режиме в сети и в режиме вне сети одинаковы. Исключением является дополнительное место, необходимое для временного сопоставления индекса. Этот временный индекс применяется в операциях с индексами в сети при создании, перестроении или удалении кластеризованных индексов. Удаление кластеризованного индекса в режиме в сети требует столько же места, сколько и его создание в режиме в сети. Дополнительные сведения см. в статье Disk Space Requirements for Index DDL Operations.
Вопросы производительности
Хотя операции с индексами в сети допускают одновременную работу пользователей, в этом случае они выполняются тем дольше, чем интенсивнее происходит обновление данных. Обычно операции с индексами в сети выполняются медленнее, чем аналогичные операции вне сети, независимо от текущей интенсивности обновления данных.
Поскольку и исходная, и целевая структуры обслуживаются во время выполнения операции с индексами в сети, увеличивается потребление ресурсов при вставке, обновлении и удалении, и это увеличение может доходить до двукратного. Это может привести к снижению производительности и повышению нагрузки на систему, особенно ресурсов ЦП. Операции с индексами в сети полностью записываются в журнал.
Несмотря на то, что рекомендуется выполнять операции с индексами в сети, необходимо предварительно оценить среду и определенные требования. Возможно, оптимальным решением может оказаться переключение в режим вне сети. При этом на время выполнения операции пользователи будут иметь ограниченный доступ к данным, но она быстрее закончится и в итоге займет меньше ресурсов.
На многопроцессорных компьютерах, работающих под управлением SQL Server 2014, инструкции index могут использовать больше процессоров для выполнения операций сканирования и сортировки, связанных с инструкцией index, так же, как и другие запросы. Можно использовать параметр индекса MAXDOP с целью управления максимальным количеством процессоров, используемых для операции с индексами в сети. В этом случае можно распределить потребляемые операцией с индексом ресурсы таким образом, чтобы не пострадали одновременно работающие пользователи. Дополнительные сведения см. в статье Настройка параллельных операций с индексами. Дополнительные сведения о выпусках SQL Server, поддерживающих параллельные индексированные операции, см. в разделе Функции, поддерживаемые выпусками SQL Server 2014.
Поскольку на финальной фазе удерживаются блокировки S-lock и Sch-M, будьте внимательны при выполнении операций с индексами в сети внутри явно объявленных пользовательских транзакций (например: в блоке BEGIN TRANSACTION...COMMIT), поскольку в этом случае блокировка будет удерживаться до окончания транзакции, мешая одновременной работе пользователей.
Перестроение индекса в сети может привести к увеличению фрагментации, когда оно выполняется с параметрами MAX DOP > 1 и ALLOW_PAGE_LOCKS = OFF . Дополнительные сведения см. в статье Как это работает. Перестроение индекса в сети может привести к увеличению фрагментации.
Вопросы, касающиеся журнала транзакций
Масштабные операции с индексами, выполняемые в режиме в сети или вне сети, могут привести к формированию больших объемов данных, которые вызовут переполнение журнала транзакций. Для гарантии возможности отката операций с индексами журнал транзакций не может быть усечен до завершения операции с индексом, однако может быть выполнено его резервное копирование. Иными словами, журнал транзакций должен иметь достаточно места для сохранения и транзакции операции с индексом и текущих пользовательских транзакций на весь период выполнения операции с индексом. Дополнительные сведения см. в статье Transaction Log Disk Space for Index Operations.
См. также
Об операциях с индексом в сети