ALTER INDEX (Transact-SQL)

Применимо: SQL Server База данных SQL Azure Управляемый экземпляр SQL Azureбазе данных SQL Azure Synapse Analytics Analytics Platform System (PDW)в Microsoft Fabric

Изменяет существующий индекс таблицы или представления (rowstore, columnstore или XML) посредством его отключения, перестройки или реорганизации либо посредством настройки параметров индекса.

Соглашения о синтаксисе Transact-SQL

Синтаксис

Синтаксис для SQL Server, База данных SQL Azure и Управляемый экземпляр SQL Azure.

ALTER INDEX { index_name | ALL } ON <object>
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> [ , ...n ] ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> [ , ...n ] ) ] ]
      }
    | DISABLE
    | REORGANIZE  [ PARTITION = partition_number ] [ WITH ( <reorganize_option>  ) ]
    | SET ( <set_index_option> [ , ...n ] )
    | RESUME [ WITH (<resumable_index_option> [ , ...n ] ) ]
    | PAUSE
    | ABORT
}
[ ; ]

<object> ::=
{
    { database_name.schema_name.table_or_view_name | schema_name.table_or_view_name | table_or_view_name }
}

<rebuild_index_option> ::=
{
      PAD_INDEX = { ON | OFF }
    | FILLFACTOR = fillfactor
    | SORT_IN_TEMPDB = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | STATISTICS_INCREMENTAL = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [ MINUTES ]
    | ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ] }

<single_partition_rebuild_index_option> ::=
{
      SORT_IN_TEMPDB = { ON | OFF }
    | MAXDOP = max_degree_of_parallelism
    | RESUMABLE = { ON | OFF }
    | MAX_DURATION = <time> [ MINUTES ]
    | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
    | ONLINE = { ON [ ( <low_priority_lock_wait> ) ] | OFF }
}

<reorganize_option> ::=
{
       LOB_COMPACTION = { ON | OFF }
    |  COMPRESS_ALL_ROW_GROUPS =  { ON | OFF }
}

<set_index_option> ::=
{
      ALLOW_ROW_LOCKS = { ON | OFF }
    | ALLOW_PAGE_LOCKS = { ON | OFF }
    | OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
    | IGNORE_DUP_KEY = { ON | OFF }
    | STATISTICS_NORECOMPUTE = { ON | OFF }
    | COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
}

<resumable_index_option> ::=
 {
    MAXDOP = max_degree_of_parallelism
    | MAX_DURATION = <time> [ MINUTES ]
    | <low_priority_lock_wait>
 }

<low_priority_lock_wait> ::=
{
    WAIT_AT_LOW_PRIORITY ( MAX_DURATION = <time> [ MINUTES ] ,
                          ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS } )
}

Синтаксис для Системы платформы Azure Synapse Analytics и Analytics (PDW).

ALTER INDEX { index_name | ALL }
    ON [ schema_name. ] table_name
{
      REBUILD {
            [ PARTITION = ALL [ WITH ( <rebuild_index_option> ) ] ]
          | [ PARTITION = partition_number [ WITH ( <single_partition_rebuild_index_option> ) ] ]
      }
    | DISABLE
    | REORGANIZE [ PARTITION = partition_number ]
}
[ ; ]

<rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
    | XML_COMPRESSION = { ON | OFF }
        [ ON PARTITIONS ( { <partition_number> [ TO <partition_number> ] } [ , ...n ] ) ]
}

<single_partition_rebuild_index_option> ::=
{
    DATA_COMPRESSION = { COLUMNSTORE | COLUMNSTORE_ARCHIVE }
    | XML_COMPRESSION = { ON | OFF }
}

Аргументы

index_name

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

ВСЕ

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

Использование ключевого слова ALL с этой операцией	Отказывает, если в таблице имеется один или несколько
REBUILD WITH ONLINE = ON	XML-индекс Пространственный индекс Индекс Columnstore только в SQL Server 2017 (14.x) и более ранних версиях. Более поздние версии поддерживают перестроение индексов columnstore по сети.
REBUILD PARTITION = <partition_number>	Несекционированный, пространственный, отключенный индекс или XML-индекс
REORGANIZE	Индексы с заданным значением ALLOW_PAGE_LOCKSOFF
REORGANIZE PARTITION = <partition_number>	Несекционированный, пространственный, отключенный индекс или XML-индекс
IGNORE_DUP_KEY = ON	XML-индекс Пространственный индекс Индекс Columnstore 1
ONLINE = ON	XML-индекс Пространственный индекс Индекс Columnstore 1
RESUMABLE = ON	Возобновление индексов, не поддерживаемых ключевым словом ALL

Если ALL задано значение, PARTITION = <partition_number>все индексы должны быть выровнены. Следовательно, они секционируются на основе эквивалентных функций секционирования. Использование ALL с PARTITION причиной перестроения или реорганизации всех секций индекса с одинаковыми <partition_number> . Дополнительные сведения о секционированных индексах см. в разделах Секционированные таблицы и индексы.

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

database_name

Имя базы данных.

schema_name

Имя схемы, которой принадлежит таблица или представление.

table_or_view_name

Имя таблицы или представления, связанного с индексом. Чтобы просмотреть сведения о индексе для таблицы или представления, используйте представление каталога sys.indexes.

База данных SQL Azure поддерживает формат трех частей <database_name>.<schema_name>.<object_name>, если <database_name> является текущим именем базы данных или <database_name>tempdb и <object_name> начинается с # или ##. Если имя схемы dbo, <schema_name> можно опустить.

ПЕРЕСТРОЕНИЯ [ WITH ( <rebuild_index_option [ ,...>n ] ] ]

применимо к: SQL Server 2012 (11.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

Указывает, что индекс перестроен с помощью одинаковых столбцов, типа индекса, атрибута уникальности и порядка сортировки. REBUILD включает отключенный индекс. Перестроение кластеризованного индекса не перестраивает связанные некластеризованные индексы, если ключевое слово ALL не указано. Если параметры индекса не указаны, применяются существующие значения параметров индекса в sys.indexes. Для любого параметра индекса, значение которого не отображается в sys.indexes, используется значение по умолчанию, указанное в определении аргумента параметра.

Если указана ALL и базовая таблица является кучей, операция перестроения не влияет на кучу. Перестраиваются все некластеризованные индексы, ассоциированные с таблицей.

Операция REBUILD может быть минимально зарегистрирована, если модель восстановления базы данных является массовой или простой.

При перестроении первичного XML-индекса индексированная пользовательская таблица недоступна в течение действия операции с индексами.

Для индексов columnstore операция перестроения:

• Повторно сжимает все данные в columnstore. Во время выполнения операции перестроения существуют две копии индекса columnstore. После завершения перестроения ядро СУБД удаляет исходный индекс columnstore.
• Не сохраняет порядок сортировки, если таковой имеется. Чтобы перестроить индекс columnstore и сохранить или ввести порядок сортировки, используйте инструкцию CREATE [CLUSTERED] COLUMNSTORE INDEX ... ORDER (...) ... WITH (DROP_EXISTING = ON).

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

СЕКЦИЯ

Указывает, что только одна секция индекса перестроена или реорганизована. PARTITION Невозможно указать, если index_name не является секционированных индексом.

PARTITION = ALL перестраивает все секции.

Предупреждение

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

• partition_number

  Количество секций секционированного индекса, который необходимо перестроить или реорганизовать. Аргумент partition_number является постоянным выражением, которое может обращаться к переменным. Ими являются переменные пользовательских типов или функции и определяемые пользователем функции, но не инструкция Transact-SQL. partition_numberдолжен существовать, или выполнение инструкции завершится с ошибкой.

• WITH ( <single_partition_rebuild_index_option> )

  SORT_IN_TEMPDB, MAXDOP, DATA_COMPRESSIONи XML_COMPRESSION — это параметры, которые можно указать при перестроении одной секции с помощью синтаксиса (PARTITION = partition_number). XML-индексы нельзя указать в одной операции перестроения секций.

DISABLE

Помечает индекс как отключенный и недоступный для использования ядро СУБД. Любой индекс может быть отключен. Определение отключенного индекса остается в системном каталоге без базовых индексных данных. Отключение кластеризованного индекса блокирует доступ пользователя к данным базовой таблицы. Чтобы включить индекс, используйте ALTER INDEX REBUILD или CREATE INDEX WITH DROP_EXISTING. Дополнительные сведения см. в разделе Отключение индексов и ограничений и Включение индексов и ограничений.

Операция REORGANIZE для индекса rowstore

Для индексов REORGANIZE rowstore указывает для реорганизации конечного уровня индекса. Операция REORGANIZE :

• всегда выполняется в сети. Это означает, что долгосрочные блоки таблицы не хранятся, а запросы или обновления данных в базовой таблице могут продолжаться во время ALTER INDEX REORGANIZE транзакции.
• не разрешается для отключенного индекса;
• Не допускается, если ALLOW_PAGE_LOCKS задано значение OFF.
• Не откатывается при выполнении транзакции, а транзакция откатывается.

Примечание.

Когда ALTER INDEX REORGANIZE использует явные транзакции (например, ALTER INDEX внутри BEGIN TRAN ... COMMIT/ROLLBACK) вместо режима неявных транзакций по умолчанию, поведение блокировки REORGANIZE становится более строгим, что может привести к блокировке. Дополнительные сведения о неявных транзакциях см. в разделе SET IMPLICIT_TRANSACTIONS.

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

REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )

Применяется к индексам rowstore.

• DNS

  • Указывает сжатие всех страниц, содержащих данные следующих типов данных больших объектов (LOB): image, text, ntext, varchar(max), nvarchar(max), varbinary(max) и xml. Сжатие этих данных может привести к уменьшению размера данных на диске.
  • Для кластеризованного индекса сжимаются все столбцы LOB, содержащиеся в таблице.
  • Для некластеризованного индекса сжимаются все столбцы LOB, являющиеся неключевыми столбцами, включенными в индекс.
  • REORGANIZE ALL выполняет сжатие бизнес-объектов для всех индексов. Для каждого индекса сжимаются все столбцы LOB в кластеризованном индексе, базовой таблице или включенные столбцы в некластеризованном индексе.

• ВЫКЛ.

  • Страницы, содержащие данные большого объекта, не сжимаются.
  • OFF не влияет на кучу.

Операция REORGANIZE для индекса columnstore

Для индексов columnstore REORGANIZE сжимает каждую закрытую разностную группу строк в columnstore в виде сжатой группы строк. Операция REORGANIZE всегда выполняется в режиме "в сети". Это означает, что долгосрочные блокировки таблицы не удерживаются и запросы или обновления базовой таблицы могут продолжаться во время выполнения транзакции ALTER INDEX REORGANIZE.

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

• REORGANIZE не требуется для перемещения закрытых разностных групп строк в сжатые группы строк. Фоновый процесс перемещения кортежей (TM) периодически просыпается, чтобы сжать закрытые разностные группы строк. Мы рекомендуем использовать, REORGANIZE когда кортеж-перемещение отстает. REORGANIZE может сжимать группы строк более агрессивно.
• Чтобы сжать все открытые и закрытые группы строк, см. REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS).

Для индексов columnstore в SQL Server 2016 (13.x) и более поздних версиях, Базе данных SQL Azure и Управляемом экземпляре SQL Azure REORGANIZE выполняет следующие дополнительные оптимизации дефрагментации в Сети:

• Физически удаляет удаленные строки из группы строк, когда 10% или более строк были логически удалены. Удаленные байты освобождают место на физическом носителе. Например, если сжатая группа строк из 1 миллиона строк содержит 100 000 строк, ядро СУБД удаляет удаленные строки и повторно сжимает группу строк с 900 000 строк.

• Объединяет одну или несколько сжатых групп строк для увеличения числа строк для каждой группы до максимального значения, составляющего 1 048 576 строк. Например, при массовом импорте 5 пакетов из 102 400 строк вы получите 5 сжатых групп строк. При выполнении REORGANIZEэти группы строк объединяются в 1 сжатую группу строк с 512 000 строками. Предполагается, что размер словаря или ограничения памяти отсутствуют.

• Для групп строк, в которых 10% или более строк были логически удалены, ядро СУБД пытается объединить эту группу строк с одной или несколькими группами строк. Например, группа строк 1 сжимается с 500 000 строками, а группа строк 21 сжимается с максимум 1 048 576 строками. В группе строк 21 удалено 60 % строк и осталось 409 830 строк. Ядро СУБД предпочитает объединять эти две группы строк для сжатия новой группы строк с 909 830 строками.

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

Применимо к индексам columnstore.

применяется: SQL Server 2016 (13.x) и более поздних версий, Базы данных SQL Azure и Управляемого экземпляра SQL Azure

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

• DNS

  Принудительно выполняет все группы строк в columnstore независимо от размера и состояния (закрытого или открытого).

• ВЫКЛ.

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

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

SET ( <параметр> set_index [ ,... n ] )

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

PAD_INDEX = { ON | OFF }

Определяет разреженность индекса. Значение по умолчанию — OFF.

• DNS

  Процент свободного пространства, указанного коэффициентом заполнения, применяется к страницам промежуточного уровня индекса. Если FILLFACTOR не указано в то же время, PAD_INDEX задано значение ON, используется значение коэффициента заполнения в sys.indexes.

• ВЫКЛ.

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

Дополнительные сведения см. в разделе CREATE INDEX.

FILLFACTOR = fillfactor

Указывает процент, указывающий, насколько полный ядро СУБД должен сделать конечный уровень каждой страницы индекса во время создания или изменения индекса. Значение fillfactor должно быть целым числом от 1 до 100. По умолчанию установлено значение 0. Значения коэффициентов заполнения 0 и 100 идентичны.

Явный параметр FILLFACTOR применяется, только если индекс создается впервые или перестраивается. Ядро СУБД не сохраняет динамически указанный процентный объем свободного места на страницах. Дополнительные сведения см. в разделе CREATE INDEX.

Увидеть коэффициент заполнения можно в таблице fill_factor в sys.indexes.

Внимание

Создание индекса с FILLFACTOR менее 100 увеличивает объем места в хранилище, которое занимает ядро СУБД, так как ядро СУБД распределяет данные в соответствии с коэффициентом заполнения при создании или перестроении индекса.

SORT_IN_TEMPDB = { ON | OFF }

Указывает, следует ли хранить временные сортировки в tempdb. Значение по умолчанию за OFF исключением База данных SQL Azure гипермасштабирования. Для всех операций сборки индекса в гипермасштабировании SORT_IN_TEMPDB всегда ON, если не используется повторная сборка индекса. Для возобновления сборки индекса SORT_IN_TEMPDB всегда OFF.

• DNS

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

• ВЫКЛ.

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

Если операция сортировки не требуется или сортировка может быть выполнена в памяти, параметр SORT_IN_TEMPDB игнорируется.

Дополнительные сведения см. в разделе SORT_IN_TEMPDB параметра индексов.

IGNORE_DUP_KEY = { ON | OFF }

Определяет ответ на ошибку, случающуюся, когда операция вставки пытается вставить в уникальный индекс повторяющиеся значения ключа. Параметр IGNORE_DUP_KEY применяется только к операциям вставки, производимым после создания или перестроения индекса. Значение по умолчанию — OFF.

• DNS

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

• ВЫКЛ.

  Если в уникальный индекс вставляются повторяющиеся значения ключа, выводится сообщение об ошибке. Выполняется откат всей INSERT операции.

IGNORE_DUP_KEY Нельзя задать ON для индексов, созданных в представлении, не уникальных индексов, XML-индексов, пространственных индексов и отфильтрованных индексов.

Чтобы просмотреть параметр IGNORE_DUP_KEY индекса, используйте столбец ignore_dup_key в представлении каталога sys.indexes.

Для обратной совместимости синтаксиса аргумент WITH IGNORE_DUP_KEY эквивалентен WITH IGNORE_DUP_KEY = ON.

STATISTICS_NORECOMPUTE = { ON | OFF }

Отключите или включите параметр автоматического обновления статистики AUTO_STATISTICS_UPDATEдля статистики по индексу. Значение по умолчанию — OFF.

• DNS

  Автоматические обновления статистики отключаются после перестроения индекса.

• ВЫКЛ.

  Автоматические обновления статистики включены после перестроения индекса.

Чтобы восстановить автоматическое обновление статистики, задайте STATISTICS_NORECOMPUTE значение OFFили выполните без предложения.UPDATE STATISTICSNORECOMPUTE

Предупреждение

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

Установка STATISTICS_NORECOMPUTE для ON не предотвращает обновление статистики индекса, которая возникает во время операции перестроения индекса.

STATISTICS_INCREMENTAL = { ON | OFF }

применимо к: SQL Server 2014 (12.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

При ONстатистика, созданная на индексе, выполняется по статистике секций. При OFFсуществующие статистические данные удаляются, а ядро СУБД перекомпьютирует статистику. Значение по умолчанию — OFF.

Если статистика секции не поддерживается, параметр игнорируется и создается предупреждение. Добавочная статистика не поддерживается в следующих случаях:

• Статистика, созданная с индексами, невыровненными по секциям для базовой таблицы
• Статистика, созданная в базах данных вторичных реплик для чтения для групп доступности.
• Статистика, созданная в базах данных, доступных только для чтения.
• Статистика, созданная по фильтрованным индексам.
• Статистика, созданная по представлениям.
• Статистика, созданная по внутренним таблицам.
• Статистика, созданная с пространственными индексами или XML-индексами.

ONLINE = { ON | OFF }

Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF.

Для XML-индекса или пространственного индекса поддерживается только ONLINE = OFF ошибка.ONLINEON

Внимание

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

• DNS

  Долгосрочные блокировки таблиц не хранятся в течение операции индекса. На главном этапе операции индекса в исходной таблице хранится только блокировка намерения (IS). Это позволяет выполнять запросы или обновления базовой таблицы и индексов. В начале операции общая блокировка (S) хранится в исходном объекте в течение короткого периода времени. В конце операции в течение короткого периода времени общая блокировка (S) приобретается в объекте, если создается некластеризованный индекс. Блокировка изменения схемы (Sch-M) приобретается при создании или удалении кластеризованного индекса в сети, а также при перестроении кластеризованного или некластеризованного индекса. ONLINE Невозможно задать ON значение, если индекс создается в локальной временной таблице.

  Примечание.

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

• ВЫКЛ.

  Блокировки таблиц применяются в течение операции индекса. Автономная операция индекса, которая создает, перестраивает или удаляет кластеризованный, пространственный или XML-индекс или перестраивает или удаляет некластеризованный индекс, получает блокировку изменения схемы (Sch-M) в таблице. Это предотвращает доступ всех пользователей к базовой таблице в течение всей операции. Автономная операция индекса, которая создает некластеризованный индекс, изначально получает общую блокировку (S) в таблице. Это предотвращает изменение определения базовой таблицы, но позволяет считывать и изменять данные в таблице во время выполнения сборки индекса.

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

Индексы, в том числе индексы глобальных временных таблиц, могут быть перестроены при подключении к сети, за исключением следующего:

• XML-индекс
• Индекс локальной временной таблицы
• Исходные уникальные кластеризованные индексы представлений.
• Отключенные кластеризованные индексы
• Кластеризованные индексы columnstore в SQL Server 2017 (14.x)) и более ранних версиях
• Некластеризованные индексы columnstore в SQL Server 2016 (13.x)) и более ранних версиях
• Кластеризованные индексы, если базовая таблица содержит типы данных LOB: image, ntext, text и пространственные типы данных.
• varchar(max) и столбцы varbinary(max) не могут быть частью ключа индекса. В SQL Server (начиная с SQL Server 2012 (11.x)) в Базе данных SQL Azure и Управляемом экземпляре SQL Azure, если таблица содержит varchar(max) или varbinary(max) столбцов, кластеризованный индекс, содержащий другие столбцы, можно создать или перестроить с помощью параметра ONLINE.

Дополнительные сведения см. в статье Как работают операции с индексами в сети.

RESUMABLE = { ON | OFF}

применимо к: SQL Server 2017 (14.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

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

• DNS

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

• ВЫКЛ.

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

MAX_DURATION = ONLINE = ON )

применимо к: SQL Server 2017 (14.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

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

ALLOW_ROW_LOCKS = { ON | OFF }

Указывает, разрешена ли блокировка строк. Значение по умолчанию — ON.

• DNS

  Блокировки строк допустимы при доступе к индексу. Ядро СУБД определяет, когда используются блокировки строк.

• ВЫКЛ.

  Блокировки строк не используются.

ALLOW_PAGE_LOCKS = { ON | OFF }

Указывает, разрешена ли блокировка страниц. Значение по умолчанию — ON.

• DNS

  Блокировки страниц допустимы при доступе к индексу. Ядро СУБД определяет, когда используются блокировки страниц.

• ВЫКЛ.

  Блокировки страниц не используются.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

применяется к: SQL Server 2019 (15.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

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

MAXDOP = max_degree_of_parallelism

Переопределяет параметр конфигурации максимальной степени параллелизма для операции индекса. Дополнительные сведения см. в разделе Настройка параметра конфигурации сервера с максимальной степенью параллелизма. Используйте MAXDOP, чтобы ограничить степень параллелизма и результирующее потребление ресурсов для операции сборки индекса.

Хотя параметр MAXDOP синтаксически поддерживается для всех XML-индексов и пространственных индексов, ALTER INDEX в настоящее время использует только один процессор.

Параметр max_degree_of_parallelism может иметь одно из следующих значений:

• 1

  Подавляет параллельное создание плана.

• >1

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

• 0 (по умолчанию)

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

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

Примечание.

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

COMPRESSION_DELAY = { 0 | длительность [ минуты ] }

применимо к: SQL Server (начиная с SQL Server 2016 (13.x)), Базы данных SQL Azure и Управляемого экземпляра SQL Azure

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

Значение по умолчанию — 0 минут.

Рекомендации по использованию COMPRESSION_DELAYсм. в статье Начало работы с columnstore дляоперационной аналитики в режиме реального времени.

DATA_COMPRESSION

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

• NONE

  Индекс или указанные секции не сжимаются. Это не относится к индексам columnstore.

• ROW

  Для индекса или заданных секций производится сжатие строк. Это не относится к индексам columnstore.

• СТРАНИЦА

  Для индекса или заданных секций производится сжатие страниц. Это не относится к индексам columnstore.

• COLUMNSTORE

  применимо к: SQL Server 2014 (12.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

  Применяется только к индексам columnstore, включая некластеризованные и кластеризованные индексы columnstore. Указание COLUMNSTORE удаляет все остальные сжатия данных, включая COLUMNSTORE_ARCHIVE.

• COLUMNSTORE_ARCHIVE

  применимо к: SQL Server 2014 (12.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

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

Дополнительные сведения о сжатии см. в разделе Сжатие данных.

XML_COMPRESSION

применяется к: SQL Server 2022 (16.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

Задает параметр сжатия XML для указанного индекса, содержащего один или несколько столбцов с типом данных xml. Существуют следующие варианты выбора.

• DNS

  Для индекса или заданных секций производится сжатие XML.

• ВЫКЛ.

  Индекс или указанные секции не сжимаются.

ON PARTITIONS ( { <partition_number_expression> | <range> } [ ,... n ] )

Указывает разделы, к которым применяется параметр DATA_COMPRESSION или XML_COMPRESSION. Если индекс не секционирован, ON PARTITIONS аргумент создает ошибку. Если не указано предложение ON PARTITIONS, параметр DATA_COMPRESSION или XML_COMPRESSION применяется ко всем секциям секционированного индекса.

<partition_number_expression> можно указать одним из следующих способов.

• указав номер секции, например ON PARTITIONS (2);
• указав номера нескольких секций, разделив их запятыми, например ON PARTITIONS (1, 5);
• указав как диапазоны секций, так и отдельные секции, например ON PARTITIONS (2, 4, 6 TO 8).

<range> можно указать как номера секций, разделенные словом TO, например: ON PARTITIONS (6 TO 8).

Чтобы для разных секций задать разные типы сжатия данных, укажите параметр DATA_COMPRESSION несколько раз, например следующим образом:

REBUILD WITH
(
DATA_COMPRESSION = NONE ON PARTITIONS (1),
DATA_COMPRESSION = ROW ON PARTITIONS (2, 4, 6 TO 8),
DATA_COMPRESSION = PAGE ON PARTITIONS (3, 5)
);

Параметр XML_COMPRESSION можно указывать несколько раз, например:

REBUILD WITH
(
XML_COMPRESSION = OFF ON PARTITIONS (1),
XML_COMPRESSION = ON ON PARTITIONS (2, 4, 6 TO 8),
XML_COMPRESSION = OFF ON PARTITIONS (3, 5)
);

RESUME

применимо к: SQL Server 2017 (14.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

Возобновляет операцию индекса, приостановленную вручную, так как достигается максимальная длительность или из-за сбоя.

• MAX_DURATION

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

• WAIT_AT_LOW_PRIORITY

  Возобновление операции сборки индекса после приостановки должно получить необходимые блокировки. WAIT_AT_LOW_PRIORITY указывает, что операция сборки индекса получает блокировки с низким приоритетом, что позволяет другим операциям продолжаться во время выполнения операции сборки индекса. Пропуск параметра WAIT_AT_LOW_PRIORITY эквивалентен WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE). Дополнительные сведения см. в разделе WAIT_AT_LOW_PRIORITY.

PAUSE

применимо к: SQL Server 2017 (14.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

Приостанавливает повторную операцию сборки индекса.

ABORT

применимо к: SQL Server 2017 (14.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

Прерывает запущенную или приостановленную операцию сборки индекса, которая была запущена как возобновляемая. Чтобы завершить повторную операцию сборки индекса, необходимо явно выполнить команду ABORT. Сбой или пауза в повторной операции индекса не завершает его выполнение; скорее, он оставляет операцию в неопределенном состоянии приостановки.

Замечания

ALTER INDEX не может использоваться для повторного секционирования индекса или его перемещения в другую файловую группу. Эта инструкция не может использоваться для изменения определения индекса, в том числе для добавления или удаления столбцов или изменения их порядка. Для выполнения этих операций используйте CREATE INDEX с предложением DROP_EXISTING.

Если параметр не указан явно, тогда применяется текущий параметр. Например, если FILLFACTOR параметр не указан в предложении, значение коэффициента заполнения, хранящегося в REBUILD системном каталоге, используется во время процесса перестроения. Для просмотра текущего параметра индекса следует использовать таблицу sys.indexes.

Значения для ONLINE, MAXDOP и SORT_IN_TEMPDB не хранятся в системном каталоге. Если значение некоторого параметра не указано в инструкции индекса, то используется значение по умолчанию.

На компьютерах с несколькими процессорами инструкция ALTER INDEX REBUILD, как и другие запросы, использует больше процессоров для операций просмотра и сортировки, связанных с изменением индекса. И наоборот, ALTER INDEX REORGANIZE является одной потоковой операцией. Дополнительные сведения см. в разделе Настройка параллельных операций индексов.

В базе данных SQL в Microsoft Fabric ALTER INDEX ALL не поддерживается, но ALTER INDEX <index name> не поддерживается.

Перестроение индексов

При перестроении старый индекс удаляется, и создается новый. Таким образом, устраняется фрагментация, восстанавливается место на диске путем сжатия страниц с учетом указанного или существующего коэффициента заполнения, переупорядочиваются индексные строки в последовательных страницах. Если ALL указано, то все индексы в таблице удаляются и перестраиваются в ходе одной транзакции. Ограничения внешнего ключа не обязательно удалять заранее. При перестроении индексов с 128 экстентами или более ядро СУБД откладывает фактическое размещение сделки страниц и связанные с ними блокировки до тех пор, пока транзакция не будет зафиксирована. Дополнительные сведения см. в отложенной сделки.

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

Реорганизация индексов

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

Если указывается ключевое слово ALL, то реляционные индексы, как кластеризованные, так и некластеризованные, и XML-индексы для таблицы реорганизуются. Некоторые ограничения применяются при указании ALL.

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

Примечание.

Для таблицы с упорядоченным индексом columnstore ALTER INDEX REORGANIZE не выполняет повторную сортировку данных. Для повторной сортировки данных используйте ALTER INDEX REBUILD.

Отключение индексов

Отключение индексов предотвращает доступ пользователя к индексам в случае использования кластеризованных индексов к данным базовой таблицы. Определение индекса остается в системном каталоге. Отключение некластеризованных индексов или кластеризованных индексов в представлении физически удаляет данные индекса. При отключении кластеризованного индекса блокируется доступ к данным, но данные остаются необслуживаемыми в сбалансированном дереве до тех пор, пока индекс не будет удален или перестроен. Чтобы узнать, отключен ли индекс, используйте столбец is_disabled в представлении каталога sys.indexes.

Примечание.

В документации термин B-tree обычно используется в ссылке на индексы. В индексах rowstore ядро СУБД реализует дерево B+. Это не относится к индексам columnstore или индексам в таблицах, оптимизированных для памяти. Дополнительные сведения см. в руководстве по архитектуре и проектированию индексов SQL Sql Server и Azure.

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

С помощью инструкции ALTER INDEX REBUILD или CREATE INDEX WITH DROP_EXISTING включите индекс. Перестроение отключенного кластеризованного индекса невозможно выполнить с заданным параметром ONLINEON. Дополнительные сведения см. в разделе "Отключение индексов и ограничений".

Задание параметров

Можно задать параметры ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, OPTIMIZE_FOR_SEQUENTIAL_KEYIGNORE_DUP_KEYи STATISTICS_NORECOMPUTE для указанного индекса без перестроения или реорганизации этого индекса. Измененные значения немедленно применяются к индексу. Чтобы просмотреть эти параметры, используйте sys.indexes. Дополнительные сведения см. в разделе Настройка параметров индекса.

Параметры блокировок строк и страниц

Когда присвоены значения ALLOW_ROW_LOCKS = ON и ALLOW_PAGE_LOCK = ON, при доступе к индексу допустимы блокировки на уровне строк, страниц и таблиц. Ядро СУБД выбирает соответствующую блокировку и может передавать блокировку из строки или блокировки страницы на блокировку таблицы.

Если присвоены значения ALLOW_ROW_LOCKS = OFF и ALLOW_PAGE_LOCK = OFF, при доступе к индексу допустима только блокировка на уровне таблиц.

Если при установке параметров блокировки строки или страницы указывается ключевое слово ALL, параметры применяются ко всем индексам. Если базовая таблица представляет собой кучу, установки применяются следующими способами:

Вариант	Применимо к
ALLOW_ROW_LOCKS = ON или OFF	Куча и все связанные некластеризованные индексы.
ALLOW_PAGE_LOCKS = ON	Куча и все связанные некластеризованные индексы.
ALLOW_PAGE_LOCKS = OFF	Некластеризованные индексы, в которых запрещены все блокировки страниц. Для кучи не допускаются только общие (S), обновления (U) и монопольные (X) блокировки страниц. Ядро СУБД по-прежнему может получать блокировки страниц намерений (IS, IUили IX) для внутренних целей.

Предупреждение

Не рекомендуется отключить блокировки строк или страниц в индексе. Проблемы, связанные с параллелизмом, могут возникнуть, и некоторые функции могут быть недоступны. Например, индекс нельзя упорядочить, если для ALLOW_PAGE_LOCKS задано значение OFF.

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

При перестроении индекса и параметре ONLINE задано значение ON, данные в индексе, связанной с ней таблице и другие индексы в той же таблице доступны для запросов и изменений. Можно также перестроить в режиме «в сети» часть индекса, находящегося в одной секции. Монопольные блокировки таблиц хранятся только в течение короткого периода времени в конце перестроения индекса.

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

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

• создание нескольких некластеризованных индексов;
• реорганизация различных индексов в одной таблице;
• реорганизация различных индексов при перестройке неперекрывающихся индексов в одной таблице.

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

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

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

применимо к: SQL Server 2017 (14.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

Вы можете сделать перестроение индекса в сети повторно. Это означает, что перестроение индекса может быть остановлено и позже перезапущено с точки ее остановки. Чтобы выполнить перестроение индекса как возобновление, укажите параметр RESUMABLE = ON.

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

• Чтобы использовать параметр RESUMABLE, необходимо также использовать параметр ONLINE.
• Параметр RESUMABLE не сохраняется в метаданных заданного индекса и применяется только к длительности текущей инструкции DDL. Таким образом, для включения возобновляемости предложение RESUMABLE = ON должно быть указано явным образом.
• Параметр MAX_DURATION можно указать в двух контекстах:
  • MAX_DURATION для параметра RESUMABLE указывает интервал времени для создаваемого индекса. После этого истекает время, и если сборка индекса по-прежнему запущена, она приостановлена. Вы решите, когда можно возобновить сборку для приостановленного индекса. Время в минутах для MAX_DURATION должно быть больше 0 минут и меньше или равно одной неделе (7 * 24 * 60 = 10080 минут). Длинная пауза в операции индекса может заметно повлиять на производительность DML в определенной таблице, а также емкость диска базы данных, так как исходный индекс и только что созданный индекс требуют места на диске и должны быть обновлены операциями DML. Если параметр MAX_DURATION опущен, операция индекса продолжается до завершения или до тех пор, пока не произойдет сбой.
  • MAX_DURATION для параметра WAIT_AT_LOW_PRIORITY указывает время ожидания при блокировке с низким приоритетом, если операция индекса заблокирована, прежде чем принимать меры. Дополнительные сведения см. в WAIT_AT_LOW_PRIORITY с операциями с индексами в сети.
• Чтобы немедленно приостановить операцию индекса, можно выполнить команду ALTER INDEX PAUSE или выполнить команду KILL <session_id>.
• Повторное выполнение исходной инструкции ALTER INDEX REBUILD с теми же параметрами возобновляет приостановленную операцию перестроение индексов. Возобновить приостановленную операцию перестроения индексов можно также путем выполнения инструкции ALTER INDEX RESUME.
• Команда ABORT убивает сеанс, выполняющий сборку индекса, и отменяет операцию индекса. Невозможно возобновить операцию индекса, которая была прервана.
• При возобновлении операции перестроения индекса, приостановленной, можно изменить значение MAXDOP на новое значение. Если MAXDOP не указан при возобновлении операции индекса, приостановленной, используется значение MAXDOP, используемое для последнего резюме. Если параметр MAXDOP не указан вообще для операции перестроения индекса, используется значение по умолчанию.

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

Чтобы узнать, выполняется ли операция индекса в качестве повторной операции и чтобы проверить текущее состояние выполнения, используйте представление каталога sys.index_resumable_operations.

Ресурсы

Для возобновления операций с индексами требуются следующие ресурсы:

• Дополнительное пространство, необходимое для сохранения сборки индекса, включая время приостановки сборки.
• Дополнительная пропускная способность журнала во время этапа сортировки. Общее использование пространства журнала для повторного индекса меньше по сравнению с регулярным перестроением индекса в сети и позволяет усечение журнала во время этой операции.
• Инструкции DDL, пытающиеся изменить индекс, который выполняется перестроение или связанная с ней таблица, пока операция индекса приостановлена, не допускаются.
• Очистка призрака блокируется в индексе сборки в течение длительности операции как при приостановке, так и во время выполнения операции.
• Если таблица содержит столбцы бизнес-объектов, повторной кластеризованной сборке индекса требуется блокировка изменения схемы (Sch-M) в начале операции.

Текущие функциональные ограничения

Повторное перестроение индексов имеет следующие ограничения:

• Параметр SORT_IN_TEMPDB = ON не поддерживается для операций возобновления индексов.
• Команда DDL с RESUMABLE = ON не может выполняться внутри явной транзакции.
• Не удается создать возобновление индекса, содержащего:
  • Вычисляемые или метки времени/строковой столбцы в качестве ключевых столбцов.
  • Бизнес-столбец в качестве включенного столбца.
• Для возобновления операций с индексами не поддерживаются:
  • Команда ALTER INDEX REBUILD ALL
  • Команда ALTER TABLE REBUILD
  • Индексы columnstore
  • Отфильтрованные индексы
  • Отключенные индексы

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

применимо к: SQL Server 2014 (12.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

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

Чтобы избежать этих проблем, параметр WAIT_AT_LOW_PRIORITY позволяет управлять поведением S или Sch-M блокировок, необходимых для запуска и завершения операции индекса в Сети, выбрав три варианта. Во всех случаях, если во время ожидания, указанное MAX_DURATION = n [minutes], нет блокировки, которая включает операцию индекса, операция индекса выполняется немедленно.

WAIT_AT_LOW_PRIORITY выполняет ожидание операции индекса в сети с использованием блокировок с низким приоритетом, что позволяет другим операциям использовать обычные блокировки приоритета, которые будут продолжаться в то же время. Пропуск параметра WAIT_AT_LOW_PRIORITY эквивалентен WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = время [MINUTES]

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

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS ]

• NONE: продолжайте ожидание блокировки с обычным приоритетом.
• SELF. Выход из операции онлайн-индекса, выполняемой в данный момент, без каких-либо действий. Параметр SELF нельзя использовать, если MAX_DURATION равен 0.
• BLOCKERS: убивайте все пользовательские транзакции, которые блокируют операцию онлайн-индекса, чтобы операция продолжалось. Параметр BLOCKERS требует, чтобы субъект, выполняющий инструкцию CREATE INDEX или ALTER INDEX, должен иметь разрешение ALTER ANY CONNECTION.

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

• lock_request_priority_state
• process_killed_by_abort_blockers
• ddl_with_wait_at_low_priority

Ограничения пространственного индекса

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

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

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

Чтобы изменить параметры, относящиеся к пространственному индексу, например BOUNDING_BOX или GRID, можно либо использовать инструкцию CREATE SPATIAL INDEX, указывающую DROP_EXISTING = ON, либо удалить пространственный индекс и создать новый. Пример см. в разделе CREATE SPATIAL INDEX.

Сжатие данных

Дополнительные сведения об сжатиях данных см. в разделе "Сжатие данных".

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

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

Следующие рекомендации применяют перестроение секционированных индексов:

• Невозможно изменить параметр сжатия одной секции, если в таблице есть неустраченные индексы.
• Синтаксис ALTER INDEX <index> ... REBUILD PARTITION ... WITH DATA_COMPRESSION = ... перестраивает указанную секцию индекса с указанным параметром сжатия. Если предложение WITH DATA_COMPRESSION опущено, используется существующий параметр сжатия.
• Синтаксис ALTER INDEX <index> ... REBUILD PARTITION = ALL перестраивает все разделы индекса с помощью существующих параметров сжатия.
• Синтаксис ALTER INDEX <index> ... REBUILD PARTITION = ALL (WITH ...) производит перестроение всех секций индекса. Вы можете выбрать другое сжатие для разных секций с помощью предложения DATA_COMPRESSION = ... ON PARTITIONS ( ...).

Чтобы оценить, как изменение PAGE и ROW сжатие влияют на таблицу, индекс или секцию, используйте хранимую процедуру sp_estimate_data_compression_savings .

Статистика

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

Разрешения

Требуется ALTER разрешение на таблицу или представление.

Заметки о версии

• База данных SQL Azure не поддерживает файловые группы, отличные от PRIMARY.
• База данных SQL Azure и Управляемый экземпляр SQL Azure не поддерживают параметры FILESTREAM.
• Индексы columnstore недоступны до SQL Server 2012 (11.x).
• В SQL Server 2017 (14.x) и более поздних версиях, базе данных SQL Azure и Управляемом экземпляре SQL Azure доступны повторное использование индексов.

Пример простого синтаксиса

ALTER INDEX index1 ON table1 REBUILD;
ALTER INDEX ALL ON table1 REBUILD;
ALTER INDEX ALL ON dbo.table1 REBUILD;

Примеры: индексы Columnstore

Эти примеры относятся к индексам columnstore.

А. Демонстрация REORGANIZE

В этом примере показано, как работает команда ALTER INDEX REORGANIZE. В нем создается таблица с несколькими группами строк и показано использование команды REORGANIZE для объединения этих групп строк.

-- Create a database
CREATE DATABASE [columnstore];
GO

-- Create a rowstore staging table
CREATE TABLE [staging] (
    AccountKey INT NOT NULL,
    AccountDescription NVARCHAR(50),
    AccountType NVARCHAR(50),
    AccountCodeAlternateKey INT
);

-- Insert 10 million rows into the staging table.
DECLARE @loop INT;
DECLARE @AccountDescription VARCHAR(50);
DECLARE @AccountKey INT;
DECLARE @AccountType VARCHAR(50);
DECLARE @AccountCode INT;

SELECT @loop = 0

BEGIN TRANSACTION

WHILE (@loop < 300000)
BEGIN
    SELECT @AccountKey = CAST(RAND() * 10000000 AS INT);
    SELECT @AccountDescription = 'accountdesc ' + CONVERT(VARCHAR(20), @AccountKey);
    SELECT @AccountType = 'AccountType ' + CONVERT(VARCHAR(20), @AccountKey);
    SELECT @AccountCode = CAST(RAND() * 10000000 AS INT);

    INSERT INTO staging
    VALUES (
        @AccountKey,
        @AccountDescription,
        @AccountType,
        @AccountCode
     );

    SELECT @loop = @loop + 1;
END

COMMIT

-- Create a table for the clustered columnstore index
CREATE TABLE cci_target (
    AccountKey INT NOT NULL,
    AccountDescription NVARCHAR(50),
    AccountType NVARCHAR(50),
    AccountCodeAlternateKey INT
);

-- Convert the table to a clustered columnstore index named inxcci_cci_target;
CREATE CLUSTERED COLUMNSTORE INDEX idxcci_cci_target ON cci_target;

Используйте параметр TABLOCK для параллельной вставки строк. Начиная с SQL Server 2016 (13.x), INSERT INTO операция может выполняться параллельно при TABLOCK использовании.

INSERT INTO cci_target WITH (TABLOCK)
SELECT TOP 300000 * FROM staging;

Выполните эту команду, чтобы просмотреть OPEN разностные группы строк. Количество групп строк зависит от уровня параллелизма.

SELECT *
FROM sys.dm_db_column_store_row_group_physical_stats
WHERE object_id  = object_id('cci_target');

Выполните эту команду, чтобы принудительно перевести все CLOSED группы OPEN строк в columnstore.

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

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

ALTER INDEX idxcci_cci_target ON cci_target REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

B. Сжатие разностных групп строк CLOSED в columnstore

В этом примере используется REORGANIZE параметр сжатия каждой CLOSED разностной группы строк в columnstore в виде сжатой группы строк. Это не обязательно, но полезно, если кортеж-перемещение не сжимает CLOSED группы строк достаточно быстро.

Вы можете запустить оба примера в образце базы данных AdventureWorksDW2022.

Этот пример выполняется REORGANIZE во всех секциях.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

Этот пример выполняется REORGANIZE в определенном разделе.

-- REORGANIZE a specific partition
ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0;

В. Сжатие всех разностных групп строк OPEN и CLOSED в columnstore

применяется: SQL Server 2016 (13.x) и более поздних версий, Базы данных SQL Azure и Управляемого экземпляра SQL Azure

Команда REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON) сжимает каждую OPEN и CLOSED разностную группу строк в columnstore в виде сжатой группы строк. При этом очищается хранилище deltastore и все строки принудительно сжимаются в columnstore. Это особенно полезно после выполнения множества операций вставки, так как они хранят строки в одном или нескольких разностных группах строк.

REORGANIZE объединяет группы строк для заполнения групп строк до максимального количества строк <= 1024 576. Таким образом, при сжатие всех OPEN и CLOSED групп строк в конечном итоге не требуется много сжатых групп строк, которые имеют только несколько строк в них. Чтобы сократить размер в сжатом виде и повысить производительность запросов, группы строк следует заполнить как можно плотнее.

В следующих примерах используется база данных AdventureWorksDW2022.

В этом примере все OPEN и CLOSED разностные группы строк перемещаются в индекс columnstore.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

В этом примере все OPEN и CLOSED разностные группы строк перемещаются в индекс columnstore для определенной секции.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE PARTITION = 0 WITH (COMPRESS_ALL_ROW_GROUPS = ON);

D. Дефрагментация индекса columnstore в режиме "в сети"

Не применяется: SQL Server 2012 (11.x) и SQL Server 2014 (12.x).

Начиная с SQL Server 2016 (13.x), REORGANIZE больше, чем сжатие разностных групп строк в columnstore. Эта команда также выполняет дефрагментацию в режиме "в сети". Сначала она уменьшает размер columnstore путем физического удаления удаленных строк, если в группе строк было удалено 10 % или более строк. Затем она объединяет группы строк для формирования более крупных групп, каждая из которых может содержать до 1 024 576 строк. Все измененные группы строк проходят повторное сжатие.

Примечание.

Начиная с SQL Server 2016 (13.x), перестроение индекса columnstore больше не требуется в большинстве случаев, так как REORGANIZE физически удаляет удаленные строки и объединяет группы строк. Параметр COMPRESS_ALL_ROW_GROUPS заставляет все OPEN или CLOSED разностные группы строк в columnstore, которые ранее можно было сделать только при перестроении. REORGANIZE находится в сети и происходит в фоновом режиме, чтобы запросы могли продолжаться по мере выполнения операции.

В следующем примере выполняется REORGANIZE дефрагментация индекса путем физического удаления строк, логически удаленных из таблицы, и объединения групп строк.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REORGANIZE;

Е. Перестроение кластеризованного индекса columnstore в режиме "вне сети"

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

Совет

Начиная с SQL Server 2016 (13.x) и в База данных SQL Azure мы рекомендуем использовать ALTER INDEX REORGANIZE вместо ALTER INDEX REBUILD индексов columnstore.

Примечание.

В SQL Server 2012 (11.x) и SQL Server 2014 (12.x) REORGANIZE используется только для сжатия CLOSED групп строк в columnstore. Единственным способом выполнения операций дефрагментация и принудительной отправки всех разностных групп строк в columnstore является перестроение индекса.

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

CREATE TABLE dbo.FactInternetSales2 (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL);

CREATE CLUSTERED COLUMNSTORE INDEX cci_FactInternetSales2
ON dbo.FactInternetSales2;

INSERT INTO dbo.FactInternetSales2
SELECT ProductKey, OrderDateKey, DueDateKey, ShipDateKey
FROM dbo.FactInternetSales;

SELECT * FROM sys.column_store_row_groups;

Результаты показывают одну OPEN группу строк, что означает, что SQL Server ожидает добавления дополнительных строк, прежде чем он закрывает группу строк и перемещает данные в columnstore. Следующая инструкция перестраивает кластеризованный индекс columnstore, что приводит к принудительной отправке всех строк в columnstore.

ALTER INDEX cci_FactInternetSales2 ON FactInternetSales2 REBUILD;
SELECT * FROM sys.column_store_row_groups;

Результаты SELECT инструкции показывают, что группа строк — COMPRESSEDэто означает, что сегменты столбцов группы строк теперь сжимаются и хранятся в columnstore.

F. Перестроение секции кластеризованного индекса columnstore в режиме "вне сети"

применимо к: SQL Server 2012 (11.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

Для перестроения секции большого кластеризованного индекса columnstore используйте инструкцию ALTER INDEX REBUILD с параметром секции. В этом примере перестраивается секция 12. Начиная с SQL Server 2016 (13.x), рекомендуется заменить REBUILDREORGANIZEна .

ALTER INDEX cci_fact3
ON fact3
REBUILD PARTITION = 12;

G. Изменение кластеризованного индекса columnstore для использования архивного сжатия

Не относится к SQL Server 2012 (11.x)

Вы можете уменьшить размер кластеризованного индекса columnstore еще дальше с помощью COLUMNSTORE_ARCHIVE параметра сжатия данных. Это целесообразно для более старых данных, которые будут храниться в дешевом хранилище. Мы рекомендуем использовать это только для данных, к которым не обращаются часто, так как декомпресса медленнее, чем при обычном COLUMNSTORE сжатие.

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

Сначала подготовьте пример, создав таблицу с кластеризованным индексом columnstore. Затем еще выполните сжатие таблицы, используя архивное сжатие.

--Prepare the example by creating a table with a clustered columnstore index.
CREATE TABLE SimpleTable (
    ProductKey [int] NOT NULL,
    OrderDateKey [int] NOT NULL,
    DueDateKey [int] NOT NULL,
    ShipDateKey [int] NOT NULL
);

CREATE CLUSTERED INDEX cci_SimpleTable ON SimpleTable (ProductKey);

CREATE CLUSTERED COLUMNSTORE INDEX cci_SimpleTable
ON SimpleTable
WITH (DROP_EXISTING = ON);

--Compress the table further by using archival compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE_ARCHIVE);
GO

В этом примере удаляется сжатие архива, а используется только сжатие columnstore.

ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH (DATA_COMPRESSION = COLUMNSTORE);
GO

Примеры: индексы rowstore

А. Перестроение индекса

В следующем примере показано, как перестроить единственный индекс на таблице Employee базы данных AdventureWorks2022.

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee REBUILD;

B. Перестроение всех индексов в таблице и указание параметров

В следующем примере указывается ключевое слово ALL. Тем самым выполняется перестроение всех индексов, связанных с таблицей Production.Product в базе данных AdventureWorks2022. Указываются три параметра.

ALTER INDEX ALL ON Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON);

В следующем примере добавляется параметр ONLINE, содержащий параметры блокировки с низким приоритетом, и добавляется параметр сжатия строк.

применимо к: SQL Server 2014 (12.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

ALTER INDEX ALL ON Production.Product
REBUILD WITH
(
    FILLFACTOR = 80,
    SORT_IN_TEMPDB = ON,
    STATISTICS_NORECOMPUTE = ON,
    ONLINE = ON ( WAIT_AT_LOW_PRIORITY ( MAX_DURATION = 4 MINUTES, ABORT_AFTER_WAIT = BLOCKERS ) ),
    DATA_COMPRESSION = ROW
);

В. Реорганизация индекса со сжатием данных LOB

В следующем примере показано, как реорганизовать единственный кластеризованный индекс в базе данных AdventureWorks2022. Поскольку индекс содержит тип данных LOB на конечном уровне, инструкция также подвергает сжатию все страницы, в которых содержатся данные больших объектов. Параметр WITH (LOB_COMPACTION = ON) не требуется указывать, так как значением по умолчанию является ON.

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto REORGANIZE WITH (LOB_COMPACTION = ON);

D. Установка параметров в индексе

В следующем примере задается несколько параметров индекса AK_SalesOrderHeader_SalesOrderNumber в базе данных AdventureWorks2022.

ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON
    Sales.SalesOrderHeader
SET (
    STATISTICS_NORECOMPUTE = ON,
    IGNORE_DUP_KEY = ON,
    ALLOW_PAGE_LOCKS = ON
    ) ;
GO

Е. Отключение индекса

В следующем примере показано отключение некластеризованного индекса на таблице Employee базы данных AdventureWorks2022.

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee DISABLE;

F. Отключение ограничений

В следующем примере ограничение отключается PRIMARY KEYPRIMARY KEY путем отключения индекса в AdventureWorks2022 базе данных. Ограничение FOREIGN KEY базовой таблицы автоматически отключено и отображается предупреждающее сообщение.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department DISABLE;

Результирующий набор возвращает это предупреждающее сообщение.

Warning: Foreign key 'FK_EmployeeDepartmentHistory_Department_DepartmentID'
on table 'EmployeeDepartmentHistory' referencing table 'Department'
was disabled as a result of disabling the index 'PK_Department_DepartmentID'.

G. Включение ограничений

Следующий пример включает PRIMARY KEY и FOREIGN KEY ограничения, которые были отключены в примере F.

Ограничение PRIMARY KEY включено путем перестроения PRIMARY KEY индекса.

ALTER INDEX PK_Department_DepartmentID ON HumanResources.Department REBUILD;

Затем FOREIGN KEY ограничение включено.

ALTER TABLE HumanResources.EmployeeDepartmentHistory
CHECK CONSTRAINT FK_EmployeeDepartmentHistory_Department_DepartmentID;
GO

H. Перестроение секционированного индекса

В следующем примере перестраивается единственная секция с номером 5 секционированного индекса IX_TransactionHistory_TransactionDate в базе данных AdventureWorks2022. Секция 5 перестраивается с ONLINE=ON, а 10 минут времени ожидания для низкоприоритетной блокировки применяется отдельно к каждой блокировке, полученной вследствие операции перестроения индекса. Если в течение этого времени не удается получить блокировку для завершения перестроения индекса, инструкция по перестроению прерывается по причине ABORT_AFTER_WAIT = SELF.

применимо к: SQL Server 2014 (12.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

-- Verify the partitioned indexes.
SELECT *
FROM sys.dm_db_index_physical_stats (DB_ID(),OBJECT_ID(N'Production.TransactionHistory'), NULL , NULL, NULL);
GO
--Rebuild only partition 5.
ALTER INDEX IX_TransactionHistory_TransactionDate
ON Production.TransactionHistory
REBUILD Partition = 5
   WITH (ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF)));
GO

I. Изменение настроек сжатия для индекса

В следующем примере перестраивается индекс на несекционированной таблице rowstore.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (DATA_COMPRESSION = PAGE);
GO

J. Изменение параметра индекса со сжатием XML

Область применения: SQL Server 2022 (16.x) и более поздних версий, База данных SQL Azure и Управляемый экземпляр SQL Azure.

В следующем примере перестраивается индекс на несекционированной таблице rowstore.

ALTER INDEX IX_INDEX1
ON T1
REBUILD
WITH (XML_COMPRESSION = ON);
GO

Дополнительные примеры сжатия данных см. в разделе "Сжатие данных".

K. Возобновляемое перестроение индексов в режиме "в сети"

применимо к: SQL Server 2017 (14.x) и более поздним версиям, Базе данных SQL Azure и Управляемому экземпляру SQL Azure

В следующих примерах показано использование возобновляемого перестроения индексов в режиме "в сети".

Выполните перестроение индекса в сети в качестве повторной операции с MAXDOP = 1. Выполнение той же команды снова после приостановки операции индекса автоматически возобновляет операцию перестроения индекса.

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, MAXDOP = 1, RESUMABLE = ON);

Выполните перестроение индекса в сети в качестве повторной операции с MAX_DURATION установленным значением 240 минут.

ALTER INDEX test_idx on test_table REBUILD WITH (ONLINE = ON, RESUMABLE = ON, MAX_DURATION = 240);

Приостановите выполняющееся возобновляемое перестроение индексов в режиме "в сети".

ALTER INDEX test_idx on test_table PAUSE;

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

ALTER INDEX test_idx on test_table RESUME WITH (MAXDOP = 4);

Возобновите операцию перестроения индекса в режиме "в сети" для перестроения индекса в режиме "в сети", которое было выполнено как возобновляемое. Установите MAXDOP значение 2, задайте время выполнения индекса, выполняющегося в качестве возобновления до 240 минут, и если индекс блокируется на блокировке, подождите 10 минут и после этого убили все блокировщики.

ALTER INDEX test_idx on test_table
    RESUME WITH (MAXDOP = 2, MAX_DURATION = 240 MINUTES,
    WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10, ABORT_AFTER_WAIT = BLOCKERS));

Прервать повторную операцию перестроения индекса, которая выполняется или приостановлена.

ALTER INDEX test_idx on test_table ABORT;

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

• Руководство по архитектуре и разработке индексов SQL Server и Azure SQ
• Выполнение операций с индексами в режиме "в сети"
• Инструкция CREATE INDEX (Transact-SQL)
• CREATE SPATIAL INDEX (Transact-SQL)
• CREATE XML INDEX (Transact-SQL)
• DROP INDEX (Transact-SQL)
• Отключение индексов и ограничений
• XML-индексы (SQL Server)
• Оптимизация обслуживания индексов позволяет повысить производительность запросов и снизить уровень потребления ресурсов
• sys.dm_db_index_physical_stats (Transact-SQL)
• EVENTDATA (Transact-SQL)