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

  • Статья

Применяется к:SQL Server База данных SQL Azure Управляемый экземпляр SQL Azure Аналитические платформенные системы (PDW)

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

Примечание.

Эта статья не применяется к выделенному пулу SQL в Azure Synapse Analytics. Сведения о обслуживании индексов для выделенного пула SQL в Azure Synapse Analytics см. в статье Индексирование выделенных таблиц пула SQL в Azure Synapse Analytics.

Основные понятия: фрагментация индекса и плотность страниц

Что такое фрагментация индекса и как она влияет на производительность?

  • В индексах сбалансированного дерева (rowstore) фрагментацией называют такое состояние, когда для некоторых страниц индекса логический порядок, основанный на значении ключа, не совпадает с физическим порядком страниц индексов.

    Примечание.

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

  • Ядро СУБД автоматически изменяет индексы при каждом выполнении операций вставки, обновления или удаления в базовые данные. Например, добавление строк в таблицу может привести к необходимости "раздвинуть" существующие страницы в индексах rowstore, чтобы освободить место для новых значений ключей. Со временем такие изменения накапливаются и могут привести к тому, что данные в индексе будут неупорядоченно "разбросаны" по базе данных (то есть фрагментированы).

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

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

Что такое плотность страниц (или заполненность страниц) и как она влияет на производительность?

  • Каждая страница в базе данных может содержать переменное число строк. Если эти строки занимают весь объем страницы, плотность такой страницы определяется как 100 %. Если страница пуста, ее плотность определяется как 0 %. Разбивая страницу с плотностью 100 % на две страницы, например для размещения новой строки, мы получим для новых страниц значения плотности около 50 %.
  • Если плотность страниц мала, то для хранения того же объема данных требуется больше страниц. Это означает, что для чтения и записи тех же данных потребуется больше операций ввода-вывода, а для кэширования — больше памяти. Если объем памяти ограничен, меньше страниц, необходимых запросу, кэшируются, что приводит к еще большему объему операций ввода-вывода диска. Как мы понимаем, низкая плотность страниц негативно влияет на производительность.
  • Если ядро СУБД добавляет строки на страницу, она не заполняет страницу полностью, если коэффициент заполнения индекса имеет значение, отличное от 100 (или 0), эквивалентное этому контексту. Это приводит к уменьшению плотности страниц и увеличивает затраты на ввод-вывод, а значит, негативно влияет на производительность.
  • При низкой плотности страниц может увеличиться количество промежуточных уровней в сбалансированном дереве. Это немного повышает нагрузку на ЦП и количество операций ввода-вывода при поиске страниц конечного уровня для операций сканирования и поиска по индексу.
  • Когда оптимизатор запросов компилирует план запроса, он учитывает стоимость операций ввода-вывода для чтения необходимых этому запросу данных. При низкой плотности страниц потребуется считывать больше страниц, а значит, и стоимость ввода-вывода будет выше. Это может повлиять на выбор плана запроса. Например, с течением времени плотность страниц уменьшается из-за разбиений, и оптимизатор может скомпилировать для того же запроса другой план с другой профилем потребления ресурсов и другой производительностью.

Совет

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

Чтобы не допустить излишнего снижения плотности страниц, корпорация Майкрософт не рекомендует задавать коэффициент заполнения, отличный от значения 100 (или 0), за исключением тех сценариев, в которых индексы часто подвергаются разбиению страниц, как, например, часто изменяемые индексы с ведущими столбцами, которые содержат непоследовательные значения GUID.

Измерение фрагментации индекса и плотности страниц

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

Фрагментация для индексов rowstore и columnstore определяется по-разному. Для индексов rowstore функция sys.dm_db_index_physical_stats позволяет узнать фрагментацию и плотность страниц для конкретного индекса, для всех индексов в таблице или индексированном представлении, для всех индексов в базе данных или для всех индексов во всех базах данных. Для секционированных индексов sys.dm_db_index_physical_stats() возвращает информацию отдельно для каждой секции.

Результирующий набор, возвращаемый sys.dm_db_index_physical_stats следующими столбцами:

Столбец Description
avg_fragmentation_in_percent Логическая фрагментация (неупорядоченные страницы в индексе).
avg_page_space_used_in_percent Средняя плотность страниц.

Для сжатых групп строк в индексах columnstore фрагментация определяется как отношение числа удаленных строк к общему числу строк, выраженное в процентах. Функция sys.dm_db_column_store_row_group_physical_stats позволяет определить общее число строк и число удаленных строк отдельно для каждой группы строк в определенном индексе, во всех индексах таблицы или во всех индексах базы данных.

Результирующий набор, возвращаемый sys.dm_db_column_store_row_group_physical_stats следующими столбцами:

Столбец Description
total_rows Количество строк, которые физически хранятся в группе строк. Для сжатых групп строк учитываются строки, помеченные как удаленные.
deleted_rows Количество строк, физически хранящихся в сжатой группе строк и помеченных для удаления. Для групп строк в разностном хранилище это значение равно 0.

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

100.0*(ISNULL(deleted_rows,0))/NULLIF(total_rows,0)

Совет

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

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

Методы обслуживания индекса: реорганизация и перестроение

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

  • Реорганизация индекса
  • Перестроение индекса

Примечание.

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

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

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

  • Для индексов rowstore ядро СУБД дефрагментирует только конечный уровень кластеризованных и некластеризованных индексов в таблицах и представлениях путем физического переупорядочения страниц конечного уровня в соответствии с логическим порядком конечных узлов (слева направо). Кроме того, при реорганизации страницы индекса сжимаются таким образом, чтобы плотность страниц соответствовала указанному коэффициенту заполнения индекса. Увидеть коэффициент заполнения можно в таблице sys.indexes. Примеры синтаксиса см. в разделе Примеры: реорганизация индексов rowstore.
  • При использовании индексов columnstore в результате большого числа операций вставки, обновления и удаления данных в разностном хранилище с течением времени может накопиться много небольших групп строк. Реорганизация индекса columnstore приводит к принудительному сохранению групп строк разностного хранения в сжатые группы строк в columnstore и объединению малых сжатых групп строк в большие группы строк. Кроме того, операция реорганизации позволяет физически удалить те строки, которые помечены в columnstore как удаленные. При реорганизации индекса columnstore могут потребоваться дополнительные ресурсы ЦП для сжатия данных, что иногда приводит к снижению общей производительности системы на время выполнения операции. Но по завершении сжатия данных производительность запросов возрастает. Примеры синтаксиса см. в разделе Примеры: реорганизация индексов columnstore.

Примечание.

Начиная с SQL Server 2019 (15.x), База данных SQL Azure и Управляемый экземпляр SQL Azure, перемещение кортежей помогает задачей фонового слияния, которая автоматически сжимает небольшие открытые разностные группы строк, которые существовали в течение некоторого времени, как определено внутренним пороговым значением, или объединяет сжатые группы строк, из которых было удалено большое количество строк. Это со временем повышает качество индекса columnstore. В большинстве случаев это избавляет от необходимости выдавать команды ALTER INDEX ... REORGANIZE.

Совет

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

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

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

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

Синтаксис Transact-SQL см. в разделе ALTER INDEX REBUILD. Дополнительные сведения об операциях с индексами с сохранением подключения см. в статье Выполнение операции с индексами в сети.

Примечание.

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

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

Совет

В зависимости от наличия ресурсов и шаблонов рабочей нагрузки при использовании значения выше стандартного MAXDOP в инструкции ALTER INDEX REBUILD может сократиться длительность перестроения за счет более интенсивной загрузки ЦП.

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

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

    Совет

    Начиная с SQL Server 2016 (13.x), перестроение индекса columnstore обычно не требуется, так как REORGANIZE выполняет основные компоненты перестроения в качестве оперативной операции.

Использование перестроения индекса для восстановления после повреждения данных

В более ранних версиях SQL Server иногда можно перестроить некластеризованный индекс rowstore, чтобы исправить несоответствия из-за повреждения данных в индексе.

Начиная с SQL Server 2008 (10.0.x), вы все равно сможете восстановить такие несоответствия в некластеризованном индексе, перестроив некластеризованный индекс в автономном режиме. Но вы не сможете устранить несоответствия в некластеризованном индексе, перестроив индекс с сохранением подключения, потому что этот механизм перестроения использует существующий некластеризованный индекс в качестве основы для перестроения, то есть все эти несоответствия сохранятся. Перестроение индекса в автономном режиме иногда может вызвать принудительную проверку кластеризованного индекса (или кучи), при которой данные с несоответствиями в некластеризованном индексе будут заменены правильными данными из кластеризованного индекса или кучи.

Чтобы в качестве источника данных применялся именно кластеризованный индекс или куча, вместо перестроения некластеризованного индекса удалите его и создайте заново. Как и в предыдущих версиях, для устранения несоответствий мы рекомендуем восстанавливать затронутые данные из резервной копии, но иногда несоответствия в некластеризованном индексе удается исправить, перестроив некластеризованный индекс в автономном режиме или создав его заново. Дополнительные сведения см. в разделе DBCC CHECKDB (Transact-SQL).

Автоматическое управление индексами и статистикой

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

Вопросы, связанные с перестроением и реорганизацией индексов columnstore

Автоматическое перестроение всех некластеризованных индексов rowstore в таблице происходит в следующих случаях:

  • при создании кластеризованного индекса в таблице, в том числе при повторном создании кластеризованного индекса с другим ключом в операции CREATE CLUSTERED INDEX ... WITH (DROP_EXISTING = ON);
  • удаление кластеризованного индекса, в результате которого таблица сохраняется как куча.

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

  • перестроение кластеризованного индекса;
  • изменение хранилища для кластеризованного индекса, например применение схемы секционирования или перемещение кластеризованного индекса в другую файловую группу.

Важно!

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

При перестроении индекса на физическом носителе должно быть достаточно места для хранения двух копий индекса. После завершения перестроения ядро СУБД удаляет исходный индекс.

При указании ключевого слова ALL в инструкции ALTER INDEX ... REORGANIZE для таблицы выполняется реорганизация кластеризованных и некластеризованных индексов, а также XML-индексов.

Перестроение или реорганизация малых индексов rowstore не всегда позволяет снизить уровень фрагментации. Вплоть до SQL Server 2014 (12.x), sql Server ядро СУБД выделяет пространство с помощью смешанных экстентов. Поэтому страницы небольших индексов иногда хранятся в нескольких экстентах, что неявным образом делает такие индексы фрагментированными. Смешанные экстенты могут находиться в общем пользовании у восьми объектов, поэтому фрагментацию в малом индексе нельзя уменьшить путем его реорганизации или перестроения.

Вопросы, связанные с перестроением индекса columnstore

При перестроении индекса columnstore ядро СУБД считывает все данные из исходного индекса columnstore, включая разностное хранилище. Данные объединяются в новые группы строк, а группы строк сжимаются в columnstore. Ядро СУБД дефрагментирует columnstore путем физического удаления строк, помеченных как удаленные.

Примечание.

Начиная с SQL Server 2019 (15.x), перемещение кортежей помогает задачей фонового слияния, которая автоматически сжимает более мелкие группы строк разностного хранилища, которые существовали в течение некоторого времени, как определено внутренним пороговым значением, или объединяет сжатые группы строк, в которых было удалено большое количество строк. Со временем это повышает качество индекса columnstore. Дополнительные сведения о терминах и понятиях columnstore см. в статье "Общие сведения об индексах Columnstore".

Перестраивайте секцию, а не всю таблицу

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

Для секционированных таблиц не требуется перестраивать весь индекс columnstore, если фрагментация есть только в некоторых секциях, например в тех секциях, где операции UPDATE, DELETE или MERGE затронули большое количество строк.

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

Вопросы, связанные с реорганизацией индекса columnstore

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

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

После нескольких загрузок данных в разностном хранилище может находиться несколько небольших групп строк. Вы можете применить ALTER INDEX REORGANIZE, чтобы принудительно передать эти группы строк в columnstore, а затем объединить малые сжатые группы строк в большие сжатые группы строк. Операция реорганизации также приведет к удалению строк, которые были помечены как удаленные в columnstore.

Примечание.

Реорганизация индекса columnstore с помощью Management Studio объединяет сжатые группы строк вместе, но не принудительно сжимает все группы строк в columnstore. В columnstore будут сжаты только закрытые группы строк, но не открытые. Чтобы принудительно сжать все группы строк, используйте пример Transact-SQL, включающийCOMPRESS_ALL_ROW_GROUPS = ON.

Что нужно оценить перед началом обслуживания индекса

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

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

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

Важно!

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

Положительный побочный эффект от перестроения индекса

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

Перестроение индекса дает еще одно важное преимущество: позволяет обновить статистику по ключевым столбцам индекса, сканируя все строки в индексе. Это эквивалентно операции UPDATE STATISTICS ... WITH FULLSCAN, которая позволяет актуализировать статистику и иногда дает более точные данные, чем обычное обновление статистики по ограниченной выборке. При обновлении статистики заново компилируются все планы запросов, которые ее используют. Если прежний план запроса не был оптимальным из-за устаревшей статистики, недостаточного объема выборки для статистики или по любой другой причине, то после повторной компиляции многие планы дают лучшие результаты.

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

Совет

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

Стратегия обслуживания индекса

Корпорация Майкрософт рекомендует всем клиентам изучить и применить следующую стратегию обслуживания индексов:

  • Не следует полагаться на то, что обслуживание индекса обязательно заметно повысит производительность рабочей нагрузки.
  • Измерьте реальное влияние от реорганизации или перестроения индексов на производительность запросов в конкретной рабочей нагрузке. Хранилище запросов  — хороший способ сравнить производительность "до обслуживания" и "после обслуживания" по методике тестирования А/Б.
  • Если вы заметите, что при перестроении индексов повышается производительность, попробуйте вместо него обновить статистику. Иногда эти методы дают аналогичные улучшения. Если это справедливо для вашей системы, перестроение индексов можно выполнять реже или не выполнять совсем, заменив его периодическим обновлением статистики. Для некоторых видов статистики нужно увеличить долю выборки, используя предложения WITH SAMPLE ... PERCENT и WITH FULLSCAN (это редкая ситуация).
  • Отслеживайте фрагментацию индекса и плотность страниц с течением времени, чтобы оценить корреляцию между изменением этих значений и производительностью запросов. Если повышение уровня фрагментации или уменьшение плотности страниц снижает производительность до неприемлемого уровня, используйте реорганизацию или перестроение индексов. Часто бывает достаточно применить реорганизацию или перестроение для отдельных индексов, используемых в конкретных запросах, производительность которых ухудшается. Так вы сможете избежать высоких затрат ресурсов на обслуживание каждого индекса в базе данных.
  • Определение корреляции между фрагментацией, плотностью страниц и производительностью также поможет выбрать правильную частоту обслуживания индексов. Не следует планировать обслуживание по фиксированному расписанию. Лучше всего постоянно контролировать уровни фрагментации и плотности страниц, чтобы выполнять обслуживание индексов по мере необходимости до неприемлемого снижения производительности.
  • Если вы определили, что требуется обслуживание индекса и затраты ресурсов на такое обслуживание допустимы, выполняйте его в периоды низкой нагрузки (если это применимо), учитывая вероятность изменения тенденций по использованию ресурсов с течением времени.

Обслуживание индексов в База данных SQL Azure и Управляемый экземпляр SQL Azure

Помимо описанных выше рекомендаций и стратегий, в База данных SQL Azure и Управляемый экземпляр SQL Azure особенно важно учитывать затраты и преимущества обслуживания индекса. Клиентам следует выполнять его только в том случае, если такая потребность подтверждается фактами, и обязательно с учетом указанных ниже факторов.

  • База данных SQL Azure и Управляемый экземпляр SQL Azure реализовать управление ресурсами для установки ограничений на потребление ЦП, памяти и ввода-вывода в соответствии с подготовленной ценовой категорией. Эти ограничения применяются ко всем рабочим нагрузкам пользователей, включая обслуживание индексов. Если совокупное потребление ресурсов всеми рабочими нагрузками приближается к ограничению ресурсов, операция перестроения или реорганизации может снижать производительность других рабочих нагрузок из-за конкуренции за ресурсы. Например, загрузка больших объемов данных может замедлиться, когда на запись в журнал транзакций будет израсходовано 100 % квоты на операции ввода-вывода при перестроении индекса. В Управляемый экземпляр SQL Azure это влияние можно уменьшить, выполнив обслуживание индекса в отдельной группе рабочей нагрузки регулятора ресурсов с ограниченным выделением ресурсов за счет расширения длительности обслуживания индекса.
  • Для сокращения затрат клиенты часто подготавливают базы данных, эластичные пулы и управляемые экземпляры с минимальным запасом ресурсов. Ценовая категория выбирается в зависимости от рабочих нагрузок приложений. Чтобы обеспечить достаточные ресурсы для значительно более высокой нагрузки при обслуживании индексов без ухудшения производительности приложения, возможно, потребуются дополнительные подготовленные ресурсы. Это заметно повысит затраты, но не обязательно производительность приложений.
  • В эластичных пулах ресурсы совместно используются всеми базами данных в пуле. Даже если конкретная база данных бездействует, обслуживание индексов в ней может повлиять на рабочие нагрузки приложений, выполняющиеся одновременно с обслуживанием в других базах данных того же пула. Дополнительные сведения см. в разделе "Управление ресурсами" в плотных эластичных пулах.
  • Для большинства типов хранилища, используемых в База данных SQL Azure и Управляемый экземпляр SQL Azure, нет разницы в производительности между последовательным вводом-выводом и случайным вводом-выводом. Это снижает влияние фрагментации индексов на производительность запросов.
  • При использовании реплик масштабирования для чтения или георепликации задержка поступления данных в реплики часто увеличивается в период обслуживания индексов в первичной реплике. Если геореплика подготовлена с таким количеством ресурсов, которого недостаточно для обработки возросшего числа операций с журналом транзакций при обслуживании индекса, это приведет к отставанию такой реплики от изменений в первичной реплике. В этом случае потребуется восстановить исходное состояние. При этом реплика станет недоступной до завершения восстановления. Кроме того, на уровнях служб "Премиум" и "Критически важный для бизнеса" используемые для обеспечения высокого уровня доступности реплики также могут отставать от первичной реплики в период обслуживания индекса. Если в этот период или вскоре после него потребуется отработка отказа, она может занять больше времени, чем ожидалось.
  • Если в первичной реплике выполняется перестроение индекса и в это же время в доступной для чтения реплике выполняется длительный запрос, этот запрос может быть автоматически прекращен, чтобы предотвратить блокировку потока повтора в этой реплике.

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

Совет

Если вы определили, что обслуживание индекса необходимо для ваших База данных SQL Azure и Управляемый экземпляр SQL Azure рабочих нагрузок, следует либо реорганизовать индексы, либо использовать перестроение индексов в сети. Это позволит запросам рабочей нагрузки использовать таблицы во время перестроения индексов.

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

Совет

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

ограничения

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

Инструкция ALTER INDEX REORGANIZE требует, чтобы в файле данных, где содержится индекс, было свободное пространство, потому что операция может выделять временные рабочие страницы только в том же файле (а не в другом файле файловой группы, к примеру). Во время операции реорганизации пользователь может столкнуться с ошибкой 1105: Could not allocate space for object '###' in database '###' because the '###' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup, даже если в файловой группе достаточно места (например, если закончилось место для файла данных).

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

До SQL Server 2017 (14.x), перестроение кластеризованного индекса columnstore — это автономная операция. При перестроении ядро СУБД необходимо получить монопольную блокировку таблицы или секции. Данные находятся в автономном режиме и недоступны во время перестроения, даже при использовании NOLOCKизоляции моментальных снимков с фиксацией для чтения (RCSI) или изоляции моментальных снимков. Начиная с SQL Server 2019 (15.x), кластеризованный индекс columnstore можно перестроить с помощью ONLINE = ON параметра.

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

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

Ограничения статистики

  • Когда создается или перестраивается индекс, для него создается и обновляется статистика по данным из всех строк в таблице. Это эквивалентно использованию предложения FULLSCAN в CREATE STATISTICS или UPDATE STATISTICS. Однако начиная с SQL Server 2012 (11.x) при создании или перестроении секционированного индекса статистика не создается или обновляется путем сканирования всех строк в таблице. Вместо этого используется коэффициент выборки по умолчанию. Чтобы создать или обновить статистику секционированных индексов путем сканирования всех строк таблицы, используйте инструкции CREATE STATISTICS или UPDATE STATISTICS с предложением FULLSCAN.
  • Аналогичным образом, когда возобновляется операция создания или перестроения индекса, статистика создается или обновляется с коэффициентом выборки по умолчанию. Если статистика создана или последний раз обновлена со значением ON для предложения PERSIST_SAMPLE_PERCENT, возобновляемые операции с индексами будут использовать для создания или обновления статистики сохраненный коэффициент выборки.
  • Когда индекс реорганизуется, статистика не обновляется.

Примеры

Проверка фрагментации и плотности страниц индекса rowstore с помощью Transact-SQL

В приведенном ниже примере определяется средняя фрагментация и плотность страниц для всех индексов rowstore в текущей базе данных. Здесь используется режим SAMPLED для быстрого получения применимых на практике результатов. Для получения более точных результатов используйте режим DETAILED. Он потребует сканирования всех страниц индекса, что может занять много времени.

SELECT OBJECT_SCHEMA_NAME(ips.object_id) AS schema_name,
       OBJECT_NAME(ips.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       ips.avg_fragmentation_in_percent,
       ips.avg_page_space_used_in_percent,
       ips.page_count,
       ips.alloc_unit_type_desc
FROM sys.dm_db_index_physical_stats(DB_ID(), default, default, default, 'SAMPLED') AS ips
INNER JOIN sys.indexes AS i 
ON ips.object_id = i.object_id
   AND
   ips.index_id = i.index_id
ORDER BY page_count DESC;

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

schema_name  object_name           index_name                               index_type    avg_fragmentation_in_percent avg_page_space_used_in_percent page_count  alloc_unit_type_desc
------------ --------------------- ---------------------------------------- ------------- ---------------------------- ------------------------------ ----------- --------------------
dbo          FactProductInventory  PK_FactProductInventory                  CLUSTERED     0.390015600624025            99.7244625648629               3846        IN_ROW_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            89.6839757845318               497         LOB_DATA
dbo          DimProduct            PK_DimProduct_ProductKey                 CLUSTERED     0                            80.7132814430442               251         IN_ROW_DATA
dbo          FactFinance           NULL                                     HEAP          0                            99.7982456140351               239         IN_ROW_DATA
dbo          ProspectiveBuyer      PK_ProspectiveBuyer_ProspectiveBuyerKey  CLUSTERED     0                            98.1086236718557               79          IN_ROW_DATA
dbo          DimCustomer           IX_DimCustomer_CustomerAlternateKey      NONCLUSTERED  0                            99.5197553743514               78          IN_ROW_DATA

Подробные сведения см. в статье sys.dm_db_index_physical_stats (Transact-SQL).

Проверка фрагментации индекса columnstore с помощью Transact-SQL

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

SELECT OBJECT_SCHEMA_NAME(i.object_id) AS schema_name,
       OBJECT_NAME(i.object_id) AS object_name,
       i.name AS index_name,
       i.type_desc AS index_type,
       100.0 * (ISNULL(SUM(rgs.deleted_rows), 0)) / NULLIF(SUM(rgs.total_rows), 0) AS avg_fragmentation_in_percent
FROM sys.indexes AS i
INNER JOIN sys.dm_db_column_store_row_group_physical_stats AS rgs
ON i.object_id = rgs.object_id
   AND
   i.index_id = rgs.index_id
WHERE rgs.state_desc = 'COMPRESSED'
GROUP BY i.object_id, i.index_id, i.name, i.type_desc
ORDER BY schema_name, object_name, index_name, index_type;

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

schema_name  object_name            index_name                           index_type                avg_fragmentation_in_percent
------------ ---------------------- ------------------------------------ ------------------------- ----------------------------
Sales        InvoiceLines           NCCX_Sales_InvoiceLines              NONCLUSTERED COLUMNSTORE  0.000000000000000
Sales        OrderLines             NCCX_Sales_OrderLines                NONCLUSTERED COLUMNSTORE  0.000000000000000
Warehouse    StockItemTransactions  CCX_Warehouse_StockItemTransactions  CLUSTERED COLUMNSTORE     4.225346161484279

Обслуживание индексов с помощью SQL Server Management Studio

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

  1. В обозреватель объектов разверните базу данных, содержащую таблицу, в которой требуется реорганизовать индекс.
  2. Разверните папку Таблицы.
  3. Разверните таблицу, в которой нужно реорганизовать индекс.
  4. Разверните папку Индексы.
  5. Щелкните правой кнопкой мыши индекс, который требуется реорганизовать, и выберите пункт Реорганизовать.
  6. В диалоговом окне "Реорганизовать индексы" убедитесь, что правильный индекс находится в индексах для реорганизации сетки и нажмите кнопку "ОК".
  7. Установите флажок Сжать данные в столбцах больших объектов , чтобы указать, что также сжимаются все страницы, содержащие данные больших объектов.
  8. Нажмите ОК.

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

  1. В обозреватель объектов разверните базу данных, содержащую таблицу, в которой требуется реорганизовать индексы.
  2. Разверните папку Таблицы.
  3. Разверните таблицу, в которой нужно реорганизовать индексы.
  4. Щелкните правой кнопкой мыши папку Индексы и выберите команду Реорганизовать все.
  5. В диалоговом окне Реорганизация индексов убедитесь, что нужные индексы приведены в сетке Индексы для реорганизации. Для удаления индекса из сетки Индексы для реорганизации выделите индекс и нажмите клавишу DELETE.
  6. Установите флажок Сжать данные в столбцах больших объектов , чтобы указать, что также сжимаются все страницы, содержащие данные больших объектов.
  7. Нажмите ОК.

Обслуживание индексов с помощью Transact-SQL

Примечание.

Дополнительные примеры использования Transact-SQL для перестроения или реорганизации индексов см. в статье ALTER INDEX Examples — Rowstore Indexes и ALTER INDEX Examples — Columnstore Indexes.

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

В приведенном ниже примере показано, как реорганизовать индекс IX_Employee_OrganizationalLevel_OrganizationalNode в таблице HumanResources.Employee базы данных AdventureWorks2022.

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

В приведенном ниже примере показано, как реорганизовать индекс columnstore IndFactResellerSalesXL_CCI в таблице dbo.FactResellerSalesXL_CCI базы данных AdventureWorksDW2022. Эта команда заставляет все закрытые и открытые группы строк в columnstore.

-- This command forces all closed and open row groups into columnstore.
ALTER INDEX IndFactResellerSalesXL_CCI
    ON FactResellerSalesXL_CCI
    REORGANIZE WITH (COMPRESS_ALL_ROW_GROUPS = ON);

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

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

ALTER INDEX ALL ON HumanResources.Employee
   REORGANIZE;

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

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

ALTER INDEX PK_Employee_BusinessEntityID ON HumanResources.Employee
REBUILD
;

Перестроение всех индексов в таблице

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

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

Подробные сведения см. в статье ALTER INDEX (Transact-SQL).

Следующие шаги