ALTER INDEX (Transact-SQL)

  • Статья

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

Применимо для следующих объектов: SQL Server (начиная с SQL Server 2008 до текущей версии), База данных SQL Azure.

Значок ссылки на раздел Cинтаксические обозначения в Transact-SQL

Синтаксис

-- SQL Server Syntax

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 ( LOB_COMPACTION = { ON | OFF } ) ]
    | SET ( <set_index_option> [ ,...n ] ) 
    }
[ ; ]

<object> ::=
{
    [ database_name. [ schema_name ] . | schema_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 } 
  | 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_expression> | <range> } 
     [ , ...n ] ) ]
}
<range> ::= 
<partition_number_expression> TO <partition_number_expression>

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

<set_index_option>::=
{
    ALLOW_ROW_LOCKS = { ON | OFF }
  | ALLOW_PAGE_LOCKS = { ON | OFF }
  | IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

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

-- Windows Azure SQL Database Syntax

ALTER INDEX { index_name | ALL }
    ON <object>
    { REBUILD 
        [
          [ WITH ( <rebuild_index_option> [ ,...n ] ) ] 

        ] 
    | DISABLE
    | SET ( <set_index_option> [ ,...n ] ) 
    }
 [ ; ] 

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

<rebuild_index_option > ::= 
{
   IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
  | ONLINE = { ON | OFF } 
}

<set_index_option>::=
{
   IGNORE_DUP_KEY = { ON | OFF }
  | STATISTICS_NORECOMPUTE = { ON | OFF }
}

Аргументы

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

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

    Указание ALL с этой операцией

    Отказывает, если в таблице имеется один или несколько

    REBUILD WITH ONLINE = ON

    XML-индекс

    Пространственный индекс

    Индекс columnstore

    Область применения: С SQL Server 2012 по SQL Server 2014 включительно.

    REBUILD PARTITION = partition_number

    Несекционированный, пространственный, отключенный индекс или XML-индекс

    REORGANIZE

    Индексы с параметром ALLOW_PAGE_LOCKS, равным OFF

    REORGANIZE PARTITION = partition_number

    Несекционированный, пространственный, отключенный индекс или XML-индекс

    IGNORE_DUP_KEY = ON

    XML-индекс

    Пространственный индекс

    Индекс columnstore

    Область применения: С SQL Server 2012 по SQL Server 2014 включительно.

    ONLINE = ON

    XML-индекс

    Пространственный индекс

    Индекс columnstore

    Область применения: С SQL Server 2012 по SQL Server 2014 включительно.

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

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

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

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

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

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

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

  • REBUILD [ WITH (<rebuild_index_option> [ ,... n]) ]
    Указывает, что индекс будет перестроен с использованием тех же столбцов, типов индекса, атрибута уникальности и порядка сортировки. Для индексов columnstore задание порядка сортировки неприменимо. Это предложение эквивалентно DBCC DBREINDEX. REBUILD включает отключенный индекс. При перестройке кластеризованного индекса не перестраиваются ассоциированные некластеризованные индексы, если только не указано ключевое слово ALL. Если параметры индекса не заданы, то применяется существующий параметр индекса, который хранится в таблице sys.indexes. Для любого параметра индекса, значение которого не хранится в таблице sys.indexes, применяется значение по умолчанию, указанное в определении аргумента.

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

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

    Примечание

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

  • PARTITION

    Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

    PARTITION = ALL, перестроение всех секций.

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

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

  • partition_number

    Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

  • WITH (<single_partition_rebuild_index_option>)

    Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

    SORT_IN_TEMPDB, MAXDOP и DATA_COMPRESSION — параметры, которые могут быть указаны при перестроении одиночной секции (PARTITION = n). XML-индексы не могут быть указаны в операции перестроения одиночной секции.

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

  • REORGANIZE
    Указывает, что конечный уровень индекса будет реорганизован. Применительно к кластеризованным индексам columnstore служит указанием на то, что все группы строк CLOSED будут перемещены в columnstore. Инструкция ALTER INDEX REORGANIZE всегда выполняется в режиме в сети. Это означает, что долгосрочные блокировки таблицы не удерживаются и запросы или обновления базовой таблицы могут продолжаться во время выполнения транзакции ALTER INDEX REORGANIZE. REORGANIZE не может быть указана для отключенного индекса или индекса с ALLOW_PAGE_LOCKS со значением OFF. Операция REORGANIZE, выполняемая в транзакции, не откатывается при откате транзакции.

  • WITH ( LOB_COMPACTION = { ON | OFF } )

    Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

    • ON
      Все страницы, содержащие данные большого объекта, сжимаются.

      Изменение указанного кластеризованного индекса сжимает все столбцы LOB, которые содержатся в кластеризованном индексе.

      Реорганизация некластеризованного индекса приводит к уплотнению всех столбцов больших объектов, которые являются неключевыми (включенными) столбцами в индексе. Если задано ключевое слово ALL, то все индексы, связанные с указанной таблицей или представлением, реорганизуются, а все столбцы больших объектов, которые связаны с кластеризованным индексом, базовой таблицей или с некластеризованным индексом со включенными столбцами, уплотняются.

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

      Параметр OFF не влияет на кучу.

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

  • SET ( <set_index option> [ ,... n] )
    Указывает параметры индекса без перестройки или реорганизации индекса. SET нельзя указать для отключенного индекса.

  • PAD_INDEX = { ON | OFF }

    Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

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

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

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

  • FILLFACTOR = fillfactor

    Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

    Определяет величину в процентах, показывающую насколько должен компонент Компонент Database Engine заполнять конечный уровень каждой страницы индекса во время его создания и изменения. Значение fillfactor должно быть целым числом от 1 до 100. Значение по умолчанию равно 0. Значения коэффициентов заполнения 0 и 100 идентичны.

    Явный параметр FILLFACTOR применяется, только если индекс создается впервые или перестраивается. Компонент Компонент Database Engine не сохраняет динамически указанный процентный объем свободного места на страницах. Дополнительные сведения см. в разделе CREATE INDEX (Transact-SQL).

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

    Важное примечаниеВажно!

    Создание или замена кластеризованного индекса со значением FILLFACTOR влияет на пространство памяти, занимаемое данными, так как компонент Компонент Database Engine перераспределяет данные при создании кластеризованного индекса.

  • SORT_IN_TEMPDB = { ON | OFF }

    Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

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

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

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

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

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

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

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

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

    Для просмотра значения IGNORE_DUP_KEY используйте sys.indexes.

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

  • STATISTICS_NORECOMPUTE = { ON | OFF }
    Указывает, выполнялся ли перерасчет статистики распределения. Значение по умолчанию — OFF.

    • ON
      Устаревшие статистики не пересчитываются автоматически.

    • OFF
      Автоматическое обновление статистических данных включено.

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

    Важное примечаниеВажно!

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

  • STATISTICS_INCREMENTAL = { ON | OFF }
    При значении ON статистики создаются как статистики отдельно по секциям. При значении OFF дерево статистик удаляется и SQL Server повторно вычисляет статистики. По умолчанию используется значение OFF.

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

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

    • Статистики, созданные в доступных для чтения базах данных-получателях AlwaysOn.

    • Статистики, созданные в базах данных, доступных только для чтения.

    • Статистики, созданные по фильтрованным индексам.

    • Статистика, созданная по представлениям.

    • Статистики, созданные по внутренним таблицам.

    • Статистики, созданные с пространственными индексами или XML-индексами.

    Применимо для следующих объектов: С SQL Server 2014 по SQL Server 2014 включительно.

  • ONLINE = {ON | OFF } <применяемое к rebuild_index_option>
    Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию — OFF.

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

    Примечание

    Операции с индексами в сети доступны не во всех выпусках Microsoft SQL Server.Перечень функций, поддерживаемых в разных выпусках SQL Server, см. в разделе Возможности, поддерживаемые различными выпусками SQL Server 2014.

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

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

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

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

    • XML-индексы

    • индексы локальных временных таблиц;

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

  • ALLOW_ROW_LOCKS = { ON | OFF }

    Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

    • ON
      Блокировки строк допустимы при доступе к индексу. Компонент Компонент Database Engine определяет, когда используются блокировки строки.

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

  • ALLOW_PAGE_LOCKS = { ON | OFF }

    Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

    • ON
      Блокировки страниц допустимы при доступе к индексу. Компонент Компонент Database Engine определяет, когда используются блокировки страниц.

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

    Примечание

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

  • MAXDOP **=**max_degree_of_parallelism

    Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

    Переопределяет параметр конфигурации max degree of parallelism на время выполнения операции с индексами. Дополнительные сведения см. в разделе Настройка параметра конфигурации сервера max degree of parallelism. MAXDOP можно использовать для ограничения числа процессоров, используемых при параллельном выполнении планов. Максимальное число процессоров — 64.

    Важное примечаниеВажно!

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

    Аргумент max_degree_of_parallelism может иметь следующие значения.

    • 1
      Подавляет формирование параллельных планов.

    • >1
      Ограничивает указанным значением максимальное число процессоров, используемых для параллельных операций с индексами.

    • 0 (по умолчанию)
      В зависимости от текущей рабочей нагрузки системы использует реальное или меньшее число процессоров.

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

    Примечание

    Параллельные операции с индексами доступны не во всех выпусках Microsoft SQL Server.Перечень функций, поддерживаемых в разных выпусках SQL Server, см. в разделе Возможности, поддерживаемые различными выпусками SQL Server 2014.

  • DATA_COMPRESSION

    Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

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

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

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

    • COLUMNSTORE

      Применимо для следующих объектов: С SQL Server 2014 по SQL Server 2014 включительно.

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

    • COLUMNSTORE_ARCHIVE

      Применимо для следующих объектов: С SQL Server 2014 по SQL Server 2014 включительно.

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

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

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

    Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

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

    • Указав номер секции, например ON PARTITIONS (2).

    • Указав номера нескольких секций, разделив их запятыми, например ON PARTITIONS (1, 5).

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

    <Диапазон> можно указать номерами секций, разделенными ключевым словом 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)
);

  • ONLINE = {ON | OFF } <применяемое к single_partition_rebuild_index_option>
    Указывает, может ли быть перестроен индекс или секция индекса базовой таблицы в режиме «в сети» или «вне сети». Если REBUILD выполняется в режиме «в сети» (ON), то данные таблицы доступны для запросов и изменения данных во время операций с индексами. По умолчанию используется значение OFF.

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

      Примечание

      Перестроение индекса в режиме «в сети» может задать параметры low_priority_lock_wait, описанные ниже в этом разделе.

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

  • WAIT_AT_LOW_PRIORITY

    Применимо для следующих объектов: С SQL Server 2014 по SQL Server 2014 включительно.

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

  • MAX_DURATION = time [MINUTES ]

    Применимо для следующих объектов: С SQL Server 2014 по SQL Server 2014 включительно.

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

  • ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

    Применимо для следующих объектов: С SQL Server 2014 по SQL Server 2014 включительно.

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

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

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

Замечания

Инструкция 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, то все индексы для таблицы удаляются и перестраиваются в одной транзакции. Ограничения FOREIGN KEY не обязательно отменять заранее. Если перестраиваются индексы с 128 или большим числом экстентов, то компонент Компонент Database Engine откладывает процедуры освобождения страниц и связанные с ними блокировки до фиксации транзакции.

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

Статистические данные в SQL Server 2014 не создаются путем сканирования всех строк таблицы при создании или перестроении секционированного индекса. Вместо этого оптимизатор запросов использует для создания статистики алгоритм выборки по умолчанию. Для получения статистики по секционированным индексам путем сканирования всех строк таблицы используйте инструкции CREATE STATISTICS или UPDATE STATISTICS с предложением FULLSCAN.

В предыдущих версиях SQL Server иногда можно было перестроить некластеризованный индекс, чтобы исправить несоответствия, вызванные отказами оборудования. В SQL Server 2008 и более поздних версиях по-прежнему можно скорректировать такие несоответствия между индексом и кластеризованным индексом, перестроив некластеризованный индекс в режиме «вне сети». Однако нельзя устранить несоответствия некластеризованного индекса, перестроив индекс в режиме в сети, потому что механизм перестроения в этом режиме будет использовать существующий некластеризованный индекс в качестве основы для перестроения и тем самым закрепит несоответствие. При перестроении индекса вне сети, в отличие от вышесказанного, принудительно запускается просмотр кластеризованного индекса (или кучи) и в результате устраняются несоответствия. В предыдущих версиях рекомендованным методом устранения несоответствий было восстановление неправильных данных из резервных копий, однако исправить несоответствия индекса можно, перестроив некластеризованный индекс в режиме «вне сети». Дополнительные сведения см. в разделе DBCC CHECKDB (Transact-SQL).

Перестроение кластеризованного индекса columnstore, SQL Server:

  1. Приобретает монопольную блокировку на таблице или секции на то время, как происходит перестроение. Во время перестроения данные находятся в режиме «вне сети» и недоступны.

  2. Дефрагментирует таблицу columnstore, физически удаляя строки, которые были логически удалены из таблиц; удаленные байты освобождают место на физическом носителе.

  3. Считывает все данные из исходного индекса columnstore, включая deltastore. Объединяет данные в новые группы строк и сжимает columnstore в группы строк.

  4. Требует места на физическом носителе для хранения двух копий индекса columnstore, пока происходит его перестроение. После завершения перестроения SQL Server удаляет исходный кластеризованный индекс columnstore.

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

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

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

Для реорганизации кластеризованного индекса columnstore SQL Server перемещает все группы строк, отмеченные как CLOSED, в columnstore. Реорганизация не требуется для перемещения групп строк CLOSED в columnstore. Процесс перемещения кортежей в конечном счете находит все группы строк CLOSED и перемещает их. Но процесс перемещения кортежей является однопотоковым и может не перемещать группы строк достаточно быстро применительно к конкретной рабочей нагрузке. Для обеспечения того, чтобы группы строк перемещались после их закрытия, можно выполнять инструкцию ALTER INDEX REORGANIZE вслед за каждой загрузкой.

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

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

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

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

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

Установка параметров

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

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

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

Если присвоены значения 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) для страниц. Компонент Компонент Database Engine может запросить намеренную блокировку страницы (IS, IU или IX) для внутренних целей.

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

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

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

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

  • создание нескольких некластеризованных индексов;

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

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

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

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

WAIT_AT_LOW_PRIORITY

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

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

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

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

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

Чтобы изменить параметры, характерные для пространственного индекса (такие как BOUNDING_BOX или GRID), необходимо либо применить инструкцию CREATE SPATIAL INDEX с параметром DROP_EXISTING = ON, либо удалить пространственный индекс и создать новый. См. пример в разделе CREATE SPATIAL INDEX (Transact-SQL).

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

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

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

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

  • Если у таблицы есть невыровненные индексы, то изменить настройку сжатия отдельной секции с помощью инструкции ALTER INDEX ALL невозможно.

  • Инструкция ALTER INDEX <index> ... Инструкция REBUILD PARTITION ... производит перестроение указанной секции индекса.

  • Инструкция ALTER INDEX <index> ... Инструкция REBUILD WITH ... производит перестроение всех секций индекса.

Statistics

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

Разрешения

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

Примеры

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

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

ALTER INDEX PK_Employee_EmployeeID ON HumanResources.Employee
REBUILD;

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

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

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

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

Применимо для следующих объектов: С SQL Server 2014 по SQL Server 2014 включительно.

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

В.Перестроение кластеризованного индекса columnstore

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

USE AdventureWorksDW2012;
GO
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.

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

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

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

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

ALTER INDEX PK_ProductPhoto_ProductPhotoID ON Production.ProductPhoto
REORGANIZE ;

Д.Установка параметров для индекса

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

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

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

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

ALTER INDEX IX_Employee_ManagerID ON HumanResources.Employee
DISABLE ;

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

В следующем примере показано отключение ограничения PRIMARY KEY путем отключения индекса PRIMARY KEY в базе данных AdventureWorks2012. Ограничение 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'.

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

В следующем примере активируются ограничения PRIMARY KEY и FOREIGN KEY, снятые в примере Е.

Ограничение 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

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

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

Применимо для следующих объектов: С SQL Server 2014 по SQL Server 2014 включительно.

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

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

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

Применимо для следующих объектов: С SQL Server 2008 по SQL Server 2014 включительно.

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

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

Применимо для следующих объектов: С SQL Server 2014 по SQL Server 2014 включительно.

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

--Remove the archive compression and only use columnstore compression.
ALTER INDEX cci_SimpleTable ON SimpleTable
REBUILD
WITH ( DATA_COMPRESSION = COLUMNSTORE );
GO

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

См. также

Справочник

CREATE INDEX (Transact-SQL)

CREATE SPATIAL INDEX (Transact-SQL)

CREATE XML INDEX (Transact-SQL)

DROP INDEX (Transact-SQL)

sys.dm_db_index_physical_stats (Transact-SQL)

EVENTDATA (Transact-SQL)

Основные понятия

Отключение индексов и ограничений

XML-индексы (SQL Server)

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

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