ALTER INDEX (Transact-SQL)

Статья
05/21/2024

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

Изменяет существующий индекс таблицы или представления (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 1}
`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`

¹ Относится к SQL Server 2012 (11.x) и более поздним версиям и База данных SQL Azure.

² Относится к SQL Server 2017 (14.x) и более поздним версиям и База данных SQL Azure

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

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

database_name

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

schema_name

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

table_or_view_name

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

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

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

Область применения: SQL Server 2012 (11.x) и более поздних версий и База данных SQL Azure

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

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

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

Примечание.

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

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

Не использует порядок сортировки.
Приобретает монопольную блокировку на таблице или секции на то время, как происходит операция REBUILD. Данные находятся в автономном режиме и недоступны во время REBUILDиспользования, даже при использовании NOLOCKизоляции зафиксированных моментальных снимков считывания (RCSI) или изоляции моментальных снимков (SI).
Повторно сжимает все данные в columnstore. Во время операции REBUILD существуют две копии индекса columnstore. REBUILD По завершении SQL Server удаляет исходный индекс columnstore.

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

СЕКЦИЯ

Указывает, что только одна секция индекса перестроена или реорганизована. 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 — это параметры, которые можно указывать при выполнении операции REBUILD для одиночной секции (PARTITION = partition_number). XML-индексы нельзя указывать в операции REBUILD одиночной секции.

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.

LOB_COMPACTION = ON

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

LOB_COMPACTION = OFF

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

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

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

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

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

Физически удаляет строки из группы строк, если были логически удалено 10 % или более строк. Удаленные байты освобождают место на физическом носителе. Например, если сжатая группа строк из 1 миллиона строк содержит 100 000 строк, SQL Server удаляет удаленные строки и повторно сжимает группу строк с 900 тысячами строк. Группа будет сохранена в хранилище за счет удаления удаленных строк.
Объединяет одну или несколько сжатых групп строк для увеличения числа строк для каждой группы до максимального значения, составляющего 1 048 576 строк. Например, при массовом импорте 5 пакетов из 102 400 строк вы получите 5 сжатых групп строк. При выполнении REORGANIZEэти группы строк объединяются в 1 сжатую группу строк размером 512 000 строк. Предполагается отсутствие ограничений на размер словаря или объем памяти.
Для групп строк, в которых 10% или более строк были логически удалены, SQL Server пытается объединить эту группу строк с одной или несколькими группами строк. Например, группа строк 1 сжимается с 500 000 строками, а группа строк 21 сжимается с максимум 1 048 576 строками. В группе строк 21 удалено 60 % строк и осталось 409 830 строк. SQL Server объединяет этих две группы строк для сжатия новой группы строк, содержащей 909 830 строк.

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

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

Область применения: SQL Server (начиная с SQL Server 2016 (13.x)) и База данных SQL Azure

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

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

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

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

PAD_INDEX = { ON | OFF }

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

DNS

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

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

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

FILLFACTOR = fillfactor

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

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

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

Внимание

Создание или изменение кластеризованного индекса со FILLFACTOR значением влияет на объем дискового пространства, которое занимает данные, так как ядро СУБД перераспространяет данные при создании кластеризованного индекса.

SORT_IN_TEMPDB = { ON | OFF }

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

DNS

Промежуточные результаты сортировки, используемые для построения индекса, хранятся в tempdb. Если 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 используйте sys.indexes.

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

STATISTICS_NORECOMPUTE = { ON | OFF }

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

DNS

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

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

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

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

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

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

STATISTICS_INCREMENTAL = { ON | OFF }

Область применения: SQL Server 2014 (12.x) и более поздних версий и База данных SQL Azure

При ONсоздании статистики для каждой секции создается статистика. При OFFудалении дерева статистики sql Server перекомпьютирует статистику. Значение по умолчанию — OFF.

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

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

ONLINE = { ON | OFF } <применимо к rebuild_index_option>

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

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

Внимание

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

DNS

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

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

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

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

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

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

Следующие XEvents связаны с ALTER TABLE ... SWITCH PARTITION перестроениями индексов в сети.

lock_request_priority_state
process_killed_by_abort_blockers
ddl_with_wait_at_low_priority

Существующие XEvent progress_report_online_index_operation для операций с индексами в сети включают partition_number и partition_id.

RESUMABLE = { ON | OFF}

Область применения: SQL Server 2017 (14.x) и более поздних версий и База данных SQL Azure

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

DNS

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

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

MAX_DURATION = время [ МИНУТЫ] используется с `RESUMABLE = ON` (требуется `ONLINE = ON` )

Область применения: SQL Server 2017 (14.x) и более поздних версий и База данных SQL Azure

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

Внимание

Примечание.

Повторное перестроение индексов в Интернете не поддерживается в индексах columnstore.

ALLOW_ROW_LOCKS = { ON | OFF }

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

DNS

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

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

ALLOW_PAGE_LOCKS = { ON | OFF }

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

DNS

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

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

Примечание.

Индекс не может быть переупорядочен, если ALLOW_PAGE_LOCKS задано значение OFF.

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

Область применения: SQL Server 2019 (15.x) и более поздних версий и База данных SQL Azure

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

MAXDOP = max_degree_of_parallelism

Переопределяет параметр конфигурации максимальной степени параллелизма на время выполнения операции с индексами. Дополнительные сведения см. в разделе "Настройка максимальной степени параллелизма" (параметр конфигурации сервера). Используется MAXDOP для ограничения количества процессоров, используемых в параллельном выполнении плана. Максимальное число процессоров — 64.

Внимание

Хотя параметр MAXDOP синтаксически поддерживается для всех XML-индексов, для пространственного индекса или первичного 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))

Для таблицы на основе диска задержка указывает минимальное количество минут разностной группы строк в CLOSED состоянии должно оставаться в разностной строковой группе, прежде чем SQL Server сможет сжать ее в сжатые строки. Так как таблицы на основе дисков не отслеживают время вставки и обновления отдельных строк, SQL Server применяет задержку к разностным группам строк в CLOSED состоянии.

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

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

DATA_COMPRESSION

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

NONE

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

Для индекса или заданных секций производится сжатие строк. Это не относится к индексам columnstore.
СТРАНИЦА

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

Область применения: SQL Server 2014 (12.x) и более поздних версий и База данных SQL Azure

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

Область применения: SQL Server 2014 (12.x) и более поздних версий и База данных 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)
);

ONLINE = { ON | OFF } <применимо к single_partition_rebuild_index_option>

Указывает, может ли быть перестроен индекс или секция индекса базовой таблицы в режиме "в сети" или "вне сети". Если выполняется REBUILD ... ONLINE = ON, то данные таблицы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF.

DNS

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

Примечание.

При перестроении индекса в режиме "в сети" могут задаваться параметры low_priority_lock_wait. См. WAIT_AT_LOW_PRIORITY с операциями с индексами в режиме "в сети".
ВЫКЛ.

Блокировки таблиц применяются при выполнении операций с индексами. Это предотвращает доступ к базовой таблице всех пользователей во время выполнения операции.

RESUME

Область применения: SQL Server 2017 (14.x) и более поздних версий и База данных SQL Azure

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

MAX_DURATION используется с RESUMABLE = ON

Время (целочисленное значение минутах), в течение которого выполняется возобновляемая операция с индексами с подключением к сети после приостановки. После истечения этого времени, если возобновляемая операция все еще выполняется, она приостанавливается.
WAIT_AT_LOW_PRIORITY используется с RESUMABLE = ON и ONLINE = ON.

Возобновление перестроения индекса в режиме "в сети" после приостановки должно ожидать операции блокировки в этой таблице. 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

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

ABORT

Область применения: SQL Server 2017 (14.x) и более поздних версий и База данных SQL Azure

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

Замечания

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

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

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

На компьютерах с несколькими процессорами инструкция ALTER INDEX REBUILD, как и другие запросы, использует больше процессоров для операций просмотра и сортировки, связанных с изменением индекса. При выполнении инструкции ALTER INDEX REORGANIZE без предложения LOB_COMPACTION или с ним значение аргумента max degree of parallelism представляет собой однопотоковую операцию. Дополнительные сведения см. в статье Настройка параллельных операций с индексами.

Внимание

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

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

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

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

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

Если указывается ключевое слово ALL, то реляционные индексы, как кластеризованные, так и некластеризованные, и XML-индексы для таблицы реорганизуются. Существуют некоторые ограничения при указании ключевого слова ALL. См. определение ALL в разделе "Аргументы" этой статьи.

Внимание

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

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

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

Примечание.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Область применения: SQL Server 2017 (14.x) и более поздних версий и База данных SQL Azure

Операция ONLINE INDEX REBUILD указывается как возобновляемая с помощью параметра RESUMABLE = ON.

Параметр RESUMABLE не сохраняется среди метаданных для указанного индекса и применяется только на время выполнения текущей инструкции DDL. Таким образом, для включения возобновляемости предложение RESUMABLE = ON должно быть указано явным образом.
Параметр MAX_DURATION поддерживается для параметра RESUMABLE = ON или low_priority_lock_wait.
- MAX_DURATION параметр RESUMABLE указывает интервал времени для перестраиваемого индекса. После использования этого времени перестроение индекса либо приостановлено, либо завершается его выполнение. Пользователь решает, когда можно возобновить перестроение приостановленного индекса. Значение time в минутах для MAX_DURATION должно быть больше 0 минут и меньше или равно 1 неделе (7 × 24 × 60 = 10080 минут). При длительной приостановке операции индекса может повлиять на производительность DML в определенной таблице, а также емкость диска базы данных, так как оба индекса (исходное и только что созданное) требуют места на диске и должны быть обновлены во время операций DML. Если MAX_DURATION параметр опущен, операция индекса продолжается до его завершения или до тех пор, пока не произойдет сбой.
- Параметр аргумента low_priority_lock_wait позволяет решить, каким образом будет продолжена операция с индексами при Sch-M-блокировке.
Повторное выполнение исходной инструкции ALTER INDEX REBUILD с теми же параметрами возобновляет приостановленную операцию перестроение индексов. Возобновить приостановленную операцию перестроения индексов можно также путем выполнения инструкции ALTER INDEX RESUME.
Параметр SORT_IN_TEMPDB = ON не поддерживается для возобновляемых индексов
Команду DDL с RESUMABLE = ON невозможно выполнить внутри явной транзакции (она не может быть частью блока BEGIN TRAN ... COMMIT).
Возобновляемыми являются только приостановленные операции с индексами.
При возобновлении операции индекса, приостановленной, можно изменить MAXDOP значение на новое значение. Если MAXDOP при возобновлении операции индекса, которая приостановлена, используется последнее MAXDOP значение. MAXDOP Если параметр не указан вообще для операции перестроения индекса, то используется значение по умолчанию.
Чтобы немедленно приостановить операцию с индексами, можно остановить текущую команду (CTRL+C) либо выполнить команду ALTER INDEX PAUSE или KILL <session_id>. После приостановки команды его можно возобновить с помощью RESUME параметра.
Команда ABORT завершает сеанс, размещающий исходное перестроение индекса, и прерывает выполнение операции с индексами.
Для возобновляемого перестроения индекса не требуются дополнительные ресурсы, за исключением перечисленных ниже.
- Дополнительное пространство, необходимое для сохранения сборки индекса, включая время приостановки индекса.
- Состояние DDL, запрещающее изменения DDL.
Очистка призрака выполняется во время этапа приостановки индекса, но приостанавливается во время выполнения индекса. Для возобновляемых операций перестроения индексов отключены следующие функциональные возможности.
- Перестроение отключенного индекса не поддерживается с RESUMABLE = ON
- Команда ALTER INDEX REBUILD ALL
- ALTER TABLE с использованием перестроения индекса
- Команду DDL с RESUMABLE = ON невозможно выполнить внутри явной транзакции (она не может быть частью блока BEGIN TRAN ... COMMIT)
- Перестройте индекс, вычисляемый или TIMESTAMP столбцы в качестве ключевых столбцов.
В случае, если базовая таблица содержит столбцы бизнес-аналитики, повторное перестроение кластеризованных индексов требует блокировки Sch-M в начале этой операции.

Примечание.

Команда DDL выполняется вплоть до завершения, приостанавливается или завершается ошибкой. Если команда приостанавливается, будет выдана ошибка, указывающая, что операция была приостановлена, и что создание индекса не завершено. Дополнительные сведения о текущем состоянии индекса можно получить из sys.index_resumable_operations. Как и в случае выше, при сбое также будет выведено сообщение об ошибке.

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

Область применения: SQL Server 2014 (12.x) и более поздних версий и База данных SQL Azure

Синтаксис low_priority_lock_wait позволяет указать поведение WAIT_AT_LOW_PRIORITY. WAIT_AT_LOW_PRIORITY может использоваться только с ONLINE = ON.

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

Параметр WAIT_AT_LOW_PRIORITY позволяет администратору базы данных управлять блокировками стабильности схемы (Sch-S) и изменения схемы (Sch-M), необходимыми для перестроения индекса в режиме "в сети". Для этого доступно два варианта. Во всех случаях, если во время ожидания MAX_DURATION = n [minutes] нет блокирующих действий, перестроение индекса в режиме "в сети" выполняется немедленно и без ожидания завершения инструкции DDL.

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

MAX_DURATION = время [ МИНУТы ]

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

ABORT_AFTER_WAIT = [ NONE | SELF | BLOCKERS ]

NONE

Продолжить ожидание блокировки с обычным приоритетом.
SELF

Прекратить операцию DDL по перестроению индекса в режиме «в сети», выполняемую в данный момент без выполнения какого-либо действия. SELF Параметр нельзя использовать с значением MAX_DURATION 0.
BLOCKERS

Остановить все пользовательские транзакции, в данный момент блокирующие операцию DDL по перестроению индекса в режиме «в сети», чтобы можно было продолжить данную операцию. Для BLOCKERS этого параметра требуется ALTER ANY CONNECTION разрешение для входа.

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

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

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

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

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

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

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

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

На секционированные индексы налагаются следующие ограничения.

Если при использовании ALTER INDEX ALL ... у таблицы есть невыровненные индексы, настройку сжатия отдельной секции изменить нельзя.
Синтаксис ALTER INDEX <index> ... REBUILD PARTITION ... производит перестроение указанной секции индекса.
Синтаксис ALTER INDEX <index> ... REBUILD WITH ... производит перестроение всех секций индекса.

Статистика

При применении инструкции ALTER INDEX ALL ... к таблице происходит обновление только тех статистических данных, которые связаны с индексами. Автоматические или созданные вручную статические данные таблицы (вместо индекса) не обновляются.

Разрешения

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

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

В базе данных SQL не используются параметры файловой группы и файлового потока.
Индексы columnstore недоступны до SQL Server 2012 (11.x).
Операции возобновления индексов доступны начиная с SQL Server 2017 (14.x) и База данных 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;

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

Область применения: SQL Server (начиная с SQL Server 2016 (13.x)) и База данных 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 Server 2012 (11.x))

Совет

Начиная с 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) и более поздних версий

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

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

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
);

C. Реорганизация индекса со сжатием данных 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 KEY PRIMARY 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

-- 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

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

Выполните перестроение индекса в сети в качестве повторной операции с 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;

Поделиться через

ALTER INDEX (Transact-SQL)

Синтаксис

Аргументы

index_name

ВСЕ

database_name

schema_name

table_or_view_name

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

СЕКЦИЯ

DISABLE

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

REORGANIZE WITH ( LOB_COMPACTION = { ON | OFF } )

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

REORGANIZE WITH ( COMPRESS_ALL_ROW_GROUPS = { ON | OFF } )

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

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 | OFF } <применимо к rebuild_index_option>

RESUMABLE = { ON | OFF}

MAX_DURATION = время [ МИНУТЫ] используется с RESUMABLE = ON (требуется ONLINE = ON )

ALLOW_ROW_LOCKS = { ON | OFF }

ALLOW_PAGE_LOCKS = { ON | OFF }

OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }

MAXDOP = max_degree_of_parallelism

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

DATA_COMPRESSION

XML_COMPRESSION

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

ONLINE = { ON | OFF } <применимо к single_partition_rebuild_index_option>

RESUME

PAUSE

ABORT

Замечания

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

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

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

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

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

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

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

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

MAX_DURATION = время [ МИНУТы ]

ABORT_AFTER_WAIT = [ NONE | SELF | BLOCKERS ]

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

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

Статистика

Разрешения

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Обратная связь

Дополнительные ресурсы

MAX_DURATION = время [ МИНУТЫ] используется с `RESUMABLE = ON` (требуется `ONLINE = ON` )