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

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

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

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

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

  Примечание

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  Совет

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Существуют конкретные, но редкие сценарии, когда может потребоваться однократное или периодическое обслуживание индекса в Azure SQL Database и Azure SQL Managed Instance.

• Чтобы увеличить плотность страницы и уменьшить используемое пространство в базе данных, и таким образом оставаться в пределах предельного размера ценовой категории. Это позволит избежать перехода на более высокую ценовую категорию с более высоким предельным размером.
• Если необходимо уменьшить файлы, перестройте или переорганизуйте индексы, прежде чем сжатие файлов увеличит плотность страниц. Это ускорит операцию сжатия, так как нужно будет перемещать меньше страниц. Дополнительные сведения см. в следующем разделе:
  • Управление файловым пространством для баз данных в базе данных Azure SQL Database
  • Управление пространством файлов для баз данных в Azure SQL Managed Instance

Перестроение индексов 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 параметра.

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

В приведенном ниже примере определяется средняя фрагментация и плотность страниц для всех индексов 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.

В приведенном ниже примере определяется средняя фрагментация для всех индексов 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

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

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

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

ALTER INDEX IX_Employee_OrganizationalLevel_OrganizationalNode
    ON HumanResources.Employee
    REORGANIZE;

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

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

• Руководство по архитектуре и разработке индексов SQL Server и Azure SQ
• Выполнение операций с индексами в режиме "в сети"
• ALTER INDEX (Transact-SQL)
• Адаптивная дефрагментация индексов
• CREATE STATISTICS (Transact-SQL)
• UPDATE STATISTICS (Transact-SQL)
• Индексы columnstore - Производительность запроса
• Начните работу с Columnstore для оперативной аналитики в реальном времени
• Индексы Columnstore — склад данных
• Индексы columnstore и политика слияния для групп строк