Как посмотреть ход процесса индексации бд sql

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

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

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

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

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

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

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

Каждая страница в базе данных может содержать переменное число строк. Если эти строки занимают весь объем страницы, плотность такой страницы определяется как 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:

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

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

Управление файловым пространством для баз данных в базе данных Azure SQL Database
Управление пространством файлов для баз данных в Управляемый экземпляр 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

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

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

В обозреватель объектов разверните базу данных, содержащую таблицу, в которой требуется реорганизовать индекс.
Разверните папку Таблицы.
Разверните таблицу, в которой нужно реорганизовать индекс.
Разверните папку Индексы.
Щелкните правой кнопкой мыши индекс, который требуется реорганизовать, и выберите пункт Реорганизовать.
В диалоговом окне «Реорганизовать индексы» убедитесь, что правильный индекс находится в индексах для реорганизации сетки и нажмите кнопку «ОК«.
Установите флажок Сжать данные в столбцах больших объектов , чтобы указать, что также сжимаются все страницы, содержащие данные больших объектов.
Нажмите ОК.

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

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

Обслуживание индексов с помощью 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).

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

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

Как отследить ход процесса индексации в Manticore / Sphinx?

И всё, больше мы ничего не знаем, как там дела идут.
Если какой-то сбой, то оно так и виснет в этом состоянии.
И не узнать, то ли он там что-то индексирует всё ещё, то ли какой-то сбой в работе.
Мне приходится только по ctrl+c сбрасывать дня через 3 только когда я уже точно есть уверенность, что все разумные сроки прошли. Но блин — время то прос#ано, а задача до сих пор не выполнена.

—
И после ctrl+c выясняется, что где-то там в самом начале ещё потеряно было соединение с mysql при выполнении запроса, а всё остальное время мы просто ничего не делали:

ERROR: index 'myindex': sql_fetch_row: Lost connection to MySQL server during query. total docs, 1263321 bytes total . sec. total - 0 reads. 0 sec. total 4 writes, 0 sec.

Вопрос задан 09 февр. 2023
138 просмотров

9 комментариев

Средний 9 комментариев

sys.dm_db_index_physical_stats (Transact-SQL)

Возвращает сведения о размере и фрагментации для данных и индексов указанной таблицы или представления в SQL Server. Для индекса возвращается одна строка для каждого уровня сбалансированного дерева в каждой секции. Для кучи возвращается одна строка для IN_ROW_DATA единицы выделения каждой секции. Для данных больших объектов (LOB) возвращается одна строка для LOB_DATA единицы выделения каждой секции. Если данные переполнения строк существуют в таблице, одна строка возвращается для ROW_OVERFLOW_DATA единицы выделения в каждой секции.

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

sys.dm_db_index_physical_stats не возвращает сведения об индексах columnstore, оптимизированных для памяти. Сведения об использовании индексов, оптимизированных для памяти, см. в sys.dm_db_xtp_index_stats (Transact-SQL).

При запросе sys.dm_db_index_physical_stats на экземпляр сервера, на котором размещена доступная для чтения вторичная реплика группы доступности, может возникнуть проблема с блокировкой REDO . Это связано с тем, что это динамическое административное представление получает блокировку IS указанной пользовательской таблицы или представления, которая может блокировать запросы REDO потоком для блокировки этой пользовательской X таблицы или представления.

Синтаксис

sys.dm_db_index_physical_stats ( < database_id | NULL | 0 | DEFAULT >, < object_id | NULL | 0 | DEFAULT >, < index_id | NULL | 0 | -1 | DEFAULT >, < partition_number | NULL | 0 | DEFAULT >, < mode | NULL | DEFAULT >)

Аргументы

database_id | NULL | 0 | ПО УМОЛЧАНИЮ

Идентификатор базы данных. database_id имеет небольшой размер. Допустимые входные данные — это идентификатор базы данных, NULL, 0 или DEFAULT. По умолчанию установлено значение 0. ЗНАЧЕНИЯ NULL, 0 и DEFAULT эквивалентны в этом контексте.

Укажите ЗНАЧЕНИЕ NULL для возврата сведений для всех баз данных в экземпляре SQL Server. Если для database_id задано значение NULL, необходимо также указать значение NULL для object_id, index_id и partition_number.

Можно указать встроенную функцию DB_ID . При использовании DB_ID без указания имени базы данных уровень совместимости текущей базы данных должен иметь значение 90 или больше.

object_id | NULL | 0 | ПО УМОЛЧАНИЮ

Идентификатор объекта таблицы или представления индекса включен. object_id имеет значение int.

Допустимые входные данные — это идентификатор таблицы и представления, NULL, 0 или DEFAULT. По умолчанию установлено значение 0. ЗНАЧЕНИЯ NULL, 0 и DEFAULT эквивалентны в этом контексте. По состоянию на SQL Server 2016 (13.x) допустимые входные данные также включают имя очереди посредника служб или внутреннее имя таблицы очереди. Если применяются параметры по умолчанию (то есть все объекты, все индексы и т. д.), сведения о фрагментации для всех очередей включаются в результирующий набор.

Укажите значение NULL, чтобы вернуть данные для всех таблиц и представлений в указанной базе данных. Если для object_id задано значение NULL, необходимо также указать ЗНАЧЕНИЕ NULL для index_id и partition_number.

index_id | 0 | NULL | -1 | ПО УМОЛЧАНИЮ

Идентификатор индекса. index_id имеет значение int. Допустимые входные данные — это идентификатор индекса, 0, если object_id куча, NULL, -1 или DEFAULT. Значение по умолчанию — -1. ЗНАЧЕНИЯ NULL, -1 и DEFAULT эквивалентны этому контексту.

Укажите значение NULL, чтобы вернуть данные для всех индексов базовой таблицы или представления. При указании NULL для index_id необходимо также указать ЗНАЧЕНИЕ NULL для partition_number.

partition_number | NULL | 0 | ПО УМОЛЧАНИЮ

Номер секции в объекте. partition_number является int . Допустимые входные данные — это partion_number индекса или кучи, NULL, 0 или DEFAULT. По умолчанию установлено значение 0. ЗНАЧЕНИЯ NULL, 0 и DEFAULT эквивалентны в этом контексте.

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

partition_number основан на 1. Непартиментный индекс или куча имеет значение 1 partition_number.

режим | NULL | ПО УМОЛЧАНИЮ

Имя режима. режим указывает уровень сканирования, используемый для получения статистики. mode — sysname. Допустимыми входными данными являются значения DEFAULT, NULL, LIMITED, SAMPLED и DETAILED. Значение по умолчанию (NULL) соответствует значению LIMITED.

Таблица возвращенной информации

Имя столбца	Тип данных	Описание:
database_id	smallint	Идентификатор базы данных таблицы или представления.

— IN_ROW_DATA
— LOB_DATA
— ROW_OVERFLOW_DATA

Единица LOB_DATA выделения содержит данные, хранящиеся в столбцах текста типа, ntext, image, varchar(max),nvarchar(max), varbinary(max)и xml. Дополнительные сведения см. в разделе Типы данных (Transact-SQL).

0 для конечных уровней индекса, куч и LOB_DATA ROW_OVERFLOW_DATA единиц распределения.

Значения больше 0 соответствуют неконечным уровням индекса. index_level является самым высоким на корневом уровне индекса.

Значение измеряется в процентах и учитывает несколько файлов. Определения логической фрагментации и фрагментации экстентов см. в разделе «Замечания».

0 для LOB_DATA единиц распределения и ROW_OVERFLOW_DATA распределения.

NULL для небезопасных уровней индекса и LOB_DATA ROW_OVERFLOW_DATA единиц распределения.

Для индекса общее количество страниц индекса в текущем уровне дерева B в единице IN_ROW_DATA выделения.

Для кучи общее количество страниц данных в единице IN_ROW_DATA выделения.

Для индекса среднее значение применяется к текущему уровню дерева B в единице IN_ROW_DATA выделения.

Для кучи среднее значение всех страниц данных в единице IN_ROW_DATA выделения.

Для LOB_DATA единиц ROW_OVERFLOW_DATA распределения среднее значение всех страниц в единице выделения.

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

Для кучи общее количество записей в единице IN_ROW_DATA выделения.

Примечание. Для кучы количество записей, возвращаемых из этой функции, может не соответствовать количеству строк, возвращаемых при выполнении SELECT COUNT(*) кучи. Это происходит потому, что строка может содержать несколько записей. Например, при обновлении одна строка кучи может иметь указывающую запись и перенаправленную запись как результат операции обновления. Кроме того, большинство больших бизнес-строк разделены на несколько записей в LOB_DATA хранилище.

Для LOB_DATA единиц ROW_OVERFLOW_DATA распределения общее количество записей в полной единице выделения.

Значение 0 для нелиафетных уровней индекса в единице IN_ROW_DATA выделения.

Для индекса минимальный размер записи применяется к текущему уровню дерева B в единице IN_ROW_DATA выделения.

Для кучи минимальный размер записи в единице IN_ROW_DATA выделения.

Для LOB_DATA единиц ROW_OVERFLOW_DATA распределения минимальный размер записи в полном блоке выделения.

Для индекса максимальный размер записи применяется к текущему уровню дерева B в единице IN_ROW_DATA выделения.

Для кучи максимальный размер записи в единице IN_ROW_DATA выделения.

Для LOB_DATA единиц ROW_OVERFLOW_DATA распределения максимальный размер записи в полном блоке выделения.

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

Для кучи средний размер записи в единице IN_ROW_DATA выделения.

Для LOB_DATA единиц ROW_OVERFLOW_DATA распределения средний размер записи в полной единице выделения.

ЗНАЧЕНИЕ NULL для любой единицы выделения, отличной IN_ROW_DATA от единиц распределения для кучи.

Для кучи только что выделенные страницы не сжимаются. Куча — это СТРАНИЦА, сжимаемая при наступлении двух особых условий: при массовом импорте данных или при перестройке кучи. Типичные операции DML, которые приводят к выделению страниц, не сжимаются. Перестройте кучу, когда compressed_page_count значение увеличивается больше порогового значения.

OPEN — средства удаления и сканеры используют это.

ОЧИСТКА — удаленные удаляются, но сканеры по-прежнему используют его.

FLUSHING — буфер закрывается, а строки в буфере записываются в растровое изображение удаления.

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

READY — этот буфер удаления готов к использованию.

Замечания

Функция sys.dm_db_index_physical_stats динамического управления заменяет инструкцию DBCC SHOWCONTIG .

Режимы сканирования

Режим, в котором выполняется функция, определяет уровень просмотра для получения статистических данных, используемых функцией. Режим указан как LIMITED, SAMPLED или DETAILED. Эта функция проходит цепочки страниц в поисках единиц распределения, составляющих заданные секции таблицы или индекса. sys.dm_db_index_physical_stats требуется только блокировка таблицы «Намерение — общий доступ» (IS), независимо от режима, в котором она выполняется.

Режим LIMITED является самым быстрым, в нем производится наименьшее число просмотров страниц. Для индекса просматриваются только страницы родительского уровня в сбалансированном дереве (то есть страницы, расположенные выше конечного уровня). Для кучи просматриваются только связанные PFS- и IAM-страницы. Страницы данных в куче просматриваются в режиме LIMITED.

В режиме LIMITED используется значение NULL, compressed_page_count так как ядро СУБД сканирует только нелебезопасные страницы дерева B и IAM и PFS-страниц кучи. Используйте режим SAMPLED, чтобы получить предполагаемое значение и compressed_page_count использовать режим DETAILED, чтобы получить фактическое значение. compressed_page_count В режиме SAMPLED возвращается статистика на основе 1-процентной выборки всех страниц в индексе или куче. Результаты в режиме SAMPLED следует рассматривать как приблизительные. Если в индексе или куче менее 10 000 страниц, вместо режима SAMPLED используется режим DETAILED.

В режиме DETAILED проводится просмотр всех страниц и возвращается вся статистика.

Режимы характеризуются снижением скорости, начиная с LIMITED и заканчивая DETAILED, т. к. в каждом последующем режиме этой последовательности выполняется все больший объем работы. Для быстрого измерения уровня фрагментации таблицы или индекса используйте режим LIMITED. Это самый быстрый и не возвращает строку для каждого нелиафетного уровня в IN_ROW_DATA единице выделения индекса.

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

Функции Transact-SQL можно использовать DB_ID и OBJECT_ID , чтобы указать значение для параметров database_id и object_id . Однако передача значений, которые не являются допустимыми для этих функций, может привести к непредвиденным результатам. Например, если имя базы данных или объекта не удается найти, так как они не существуют или неправильно написаны, обе функции возвращают значение NULL. Функция sys.dm_db_index_physical_stats интерпретирует NULL как подстановочное значение, указывающее все базы данных или все объекты.

Кроме того, OBJECT_ID функция обрабатывается перед sys.dm_db_index_physical_stats вызовом функции и поэтому оценивается в контексте текущей базы данных, а не в базе данных, указанной в database_id. Это может привести OBJECT_ID к возврату значения NULL функции или, если имя объекта существует как в текущем контексте базы данных, так и в указанной базе данных, может быть возвращено сообщение об ошибке. В следующих примерах демонстрируются эти неожиданные результаты.

USE master; GO -- In this example, OBJECT_ID is evaluated in the context of the master database. -- Because Person.Address does not exist in master, the function returns NULL. -- When NULL is specified as an object_id, all objects in the database are returned. -- The same results are returned when an object that is not valid is specified. SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'AdventureWorks2022'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED'); GO -- This example demonstrates the results of specifying a valid object name -- that exists in both the current database context and -- in the database specified in the database_id parameter of the -- sys.dm_db_index_physical_stats function. -- An error is returned because the ID value returned by OBJECT_ID does not -- match the ID value of the object in the specified database. CREATE DATABASE Test; GO USE Test; GO CREATE SCHEMA Person; GO CREATE Table Person.Address(c1 int); GO USE AdventureWorks2022; GO SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED'); GO -- Clean up temporary database. DROP DATABASE Test; GO

Обнаружение фрагментации

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

Уровень фрагментации индекса или кучи отображается в столбце avg_fragmentation_in_percent . Для куч это значение соответствует фрагментации экстентов. Для индексов это значение соответствует логической фрагментации. В отличие от DBCC SHOWCONTIG алгоритмов вычисления фрагментации в обоих случаях следует учитывать хранилище, охватывающее несколько файлов, и, следовательно, точно.

Логическая фрагментация

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

Фрагментация экстентов

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

Значение должно avg_fragmentation_in_percent быть как можно ближе к нулю для максимальной производительности. Но могут быть приемлемыми значения от 0 до 10 процентов. Для снижения этих значений могут использоваться любые методы снижения фрагментации, такие как перестройка, реорганизация или повторное создание. Дополнительные сведения о том, как анализировать степень фрагментации в индексе, см. в разделе «Реорганизация и перестроение индексов».

Уменьшение фрагментации в индексе

Если индекс становится фрагментирован настолько, что это влияет на производительность запросов, для снижения фрагментации есть три возможности.

Удаление и повторное создание кластеризованного индекса. Повторное создание кластеризованного индекса перераспределяет данные и приводит к полному заполнению страниц данных. Уровень заполнения можно настроить с помощью параметра FILLFACTOR инструкции CREATE INDEX. Недостатком этого метода является то, что в цикле удаления и повторного создания индекс находится в автономном режиме, а также то, что эта операция является атомарной. Если создание индекса прерывается, индекс не создается повторно. Дополнительные сведения см. в статье CREATE INDEX (Transact-SQL).
Используйте ALTER INDEX REORGANIZE, замену для DBCC INDEXDEFRAG , чтобы изменить порядок страниц конечного уровня индекса в логическом порядке. Так как эта операция выполняется в режиме «в сети», во время выполнения инструкции индекс доступен. Кроме того, операция может быть прервана без потери уже выполненной работы. Недостаток в этом методе заключается в том, что это не делает так хорошо для реорганизации данных как операции перестроения индекса, и она не обновляет статистику.
Используйте ALTER INDEX REBUILD, замену для DBCC DBREINDEX , чтобы перестроить индекс в сети или в автономном режиме. Дополнительные сведения см. в разделе ALTER INDEX (Transact-SQL).

Фрагментация не является достаточной причиной для реорганизации или перестроения индекса. Основной эффект фрагментации заключается в том, что она замедляет упреждающее чтение во время просмотра индекса. В результате этого увеличивается время ответа. Если рабочая нагрузка запроса на фрагментированную таблицу или индекс не включает сканирование, так как рабочая нагрузка в основном является одноэлементной подстановкой, удаление фрагментации может не влиять.

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

Уменьшение фрагментации в куче

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

Создание и удаление кластеризованного индекса в таблице перестраивает все некластеризованные индексы в этой таблице дважды.

Компактные данные больших объектов

По умолчанию инструкция ALTER INDEX REORGANIZE делает более компактными страницы, содержащие данные больших объектов (LOB). Так как бизнес-страницы не освобождены при пустом, сжатие этих данных может улучшить использование места на диске, если удалено большое количество бизнес-данных, или столбец бизнес-аналитики удаляется.

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

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

Столбец avg_page_space_used_in_percent указывает на полноту страницы. Чтобы обеспечить оптимальное использование места на диске, это значение должно быть близко к 100 процентам для индекса, который не имеет большого количества случайных вставок. Однако индекс, имеющий множество случайных вставок и имеющий очень полное количество страниц, увеличивается количество разбиений страниц. Это приводит к увеличению фрагментации. Поэтому для снижения числа разбиений страниц это значение должно быть меньше 100 процентов. Перестроение индекса с параметром FILLFACTOR позволяет изменять степень заполнения страницы для обеспечения соответствия индекса шаблону запроса. Дополнительные сведения о коэффициенте заполнения см. в разделе «Указание коэффициента заполнения» для индекса. Кроме того, инструкция ALTER INDEX REORGANIZE сжимает индекс, пытаясь заполнять страницы до последнего заданного значения аргумента FILLFACTOR. Благодаря этому увеличивается значение avg_space_used_in_percent. ALTER INDEX REORGANIZE не может уменьшить полноту страницы. Для этого необходимо выполнить перестроение индекса.

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

Фрагмент состоит из физически последовательных конечных страниц в одном файле единицы распределения. Индекс состоит, по крайней мере, из одного фрагмента. Максимальное число фрагментов, которое может иметь индекс, равно числу страниц на конечном уровне индекса. Увеличение размера фрагментов означает, что для считывания того же количества страниц понадобится меньшее количество обращений к диску. Таким образом, чем больше avg_fragment_size_in_pages значение, тем лучше производительность сканирования диапазона. Значения avg_fragment_size_in_pages и avg_fragmentation_in_percent значения обратно пропорциональны друг другу. То есть перестройка или реорганизация индекса уменьшают степень фрагментации и увеличивают размер фрагментов.

ограничения

Не возвращает данные для кластеризованных индексов columnstore.

Разрешения

Необходимы следующие разрешения:

разрешение CONTROL на указанный объект в базе данных;
Разрешение VIEW DATABASE STATE или VIEW DATABASE PERFORMANCE STATE (SQL Server 2022) для возврата сведений обо всех объектах в указанной базе данных с помощью подстановочного знака @object_id=NULL.
РАЗРЕШЕНИЕ VIEW SERVER STATE или VIEW SERVER PERFORMANCE STATE (SQL Server 2022) для возврата сведений обо всех базах данных с помощью подстановочного знака @database_id = NULL.

Предоставление разрешения VIEW DATABASE STATE позволяет всем объектам в базе данных быть возвращаемыми, независимо от любых разрешений CONTROL, запрещенных для определенных объектов.

Запрет разрешения VIEW DATABASE STATE запрещает всем объектам в базе данных быть возвращаемыми, независимо от любых разрешений CONTROL, предоставленных на определенные объекты. Кроме того, если указан подстановочный знак базы данных @database_id=NULL, база данных опущена.

Примеры

О. Возврат сведений об указанной таблице

В следующем примере возвращаются размер и статистика фрагментации для всех индексов и секций таблицы Person.Address . Для повышения производительности и ограничения возвращаемой статистики используется режим просмотра ‘LIMITED’ . Для выполнения этого запроса необходимо по крайней мере разрешение CONTROL на таблицу Person.Address .

DECLARE @db_id SMALLINT; DECLARE @object_id INT; SET @db_id = DB_ID(N'AdventureWorks2022'); SET @object_id = OBJECT_ID(N'AdventureWorks2022.Person.Address'); IF @db_id IS NULL BEGIN; PRINT N'Invalid database'; END; ELSE IF @object_id IS NULL BEGIN; PRINT N'Invalid object'; END; ELSE BEGIN; SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, NULL, NULL , 'LIMITED'); END; GO

B. Возврат сведений о куче

В следующем примере возвращаются все статистические данные для кучи dbo.DatabaseLog в базе данных AdventureWorks2022. Поскольку таблица содержит бизнес-данные, строка возвращается для LOB_DATA единицы выделения в дополнение к строке, возвращаемой для IN_ROW_ALLOCATION_UNIT страниц данных кучи. Для выполнения этого запроса необходимо по крайней мере разрешение CONTROL на таблицу dbo.DatabaseLog .

DECLARE @db_id SMALLINT; DECLARE @object_id INT; SET @db_id = DB_ID(N'AdventureWorks2022'); SET @object_id = OBJECT_ID(N'AdventureWorks2022.dbo.DatabaseLog'); IF @object_id IS NULL BEGIN; PRINT N'Invalid object'; END; ELSE BEGIN; SELECT * FROM sys.dm_db_index_physical_stats(@db_id, @object_id, 0, NULL , 'DETAILED'); END; GO

C. Возврат сведений для всех баз данных

В следующем примере возвращаются все статистические данные для всех таблиц и индексов в экземпляре SQL Server, указав подстановочный знак NULL для всех параметров. Для выполнения этого запроса требуется разрешение VIEW SERVER STATE.

SELECT * FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL); GO

D. Использование sys.dm_db_index_physical_stats в скрипте для перестроения или реорганизации индексов

В следующем примере автоматически реорганизуются или перестраиваются все секции в базе данных со средней степенью фрагментации более 10 процентов. Для выполнения этого запроса необходимо разрешение VIEW DATABASE STATE. В данном примере в качестве первого параметра указывается DB_ID без определения имени базы данных. Ошибка возникает, если текущая база данных имеет уровень совместимости 80 или ниже. Чтобы исправить эту ошибку, замените вызов функции DB_ID() действительным именем базы данных. Дополнительные сведения о уровнях совместимости базы данных см. в разделе ALTER DATABASE Compatibility Level (Transact-SQL).

-- Ensure a USE statement has been executed first. SET NOCOUNT ON; DECLARE @objectid INT; DECLARE @indexid INT; DECLARE @partitioncount BIGINT; DECLARE @schemaname NVARCHAR(130); DECLARE @objectname NVARCHAR(130); DECLARE @indexname NVARCHAR(130); DECLARE @partitionnum BIGINT; DECLARE @partitions BIGINT; DECLARE @frag FLOAT; DECLARE @command NVARCHAR(4000); -- Conditionally select tables and indexes from the sys.dm_db_index_physical_stats function -- and convert object and index IDs to names. SELECT object_id AS objectid, index_id AS indexid, partition_number AS partitionnum, avg_fragmentation_in_percent AS frag INTO #work_to_do FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0; -- Declare the cursor for the list of partitions to be processed. DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do; -- Open the cursor. OPEN partitions; -- Loop through the partitions. WHILE (1 = 1) BEGIN; FETCH NEXT FROM partitions INTO @objectid, @indexid, @partitionnum, @frag; IF @@FETCH_STATUS < 0 BREAK; SELECT @objectname = QUOTENAME(o.name), @schemaname = QUOTENAME(s.name) FROM sys.objects AS o INNER JOIN sys.schemas AS s ON s.schema_id = o.schema_id WHERE o.object_id = @objectid; SELECT @indexname = QUOTENAME(name) FROM sys.indexes WHERE object_id = @objectid AND index_id = @indexid; SELECT @partitioncount = count(*) FROM sys.partitions WHERE object_id = @objectid AND index_id = @indexid; -- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding. IF @frag < 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REORGANIZE'; IF @frag >= 30.0 SET @command = N'ALTER INDEX ' + @indexname + N' ON ' + @schemaname + N'.' + @objectname + N' REBUILD'; IF @partitioncount > 1 SET @command = @command + N' PARTITION=' + CAST(@partitionnum AS NVARCHAR(10)); EXEC (@command); PRINT N'Executed: ' + @command; END; -- Close and deallocate the cursor. CLOSE partitions; DEALLOCATE partitions; -- Drop the temporary table. DROP TABLE #work_to_do; GO

Д. Использование sys.dm_db_index_physical_stats для отображения количества страниц, сжатых на странице

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

SELECT o.name, ips.partition_number, ips.index_type_desc, ips.record_count, ips.avg_record_size_in_bytes, ips.min_record_size_in_bytes, ips.max_record_size_in_bytes, ips.page_count, ips.compressed_page_count FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') ips INNER JOIN sys.objects o ON o.object_id = ips.object_id ORDER BY record_count DESC;

Е. Использование в режиме sys.dm_db_index_physical_stats SAMPLED

В следующем примере показано, как в режиме SAMPLED возвращается примерное значение, отличающееся от результатов в режиме DETAILED.

CREATE TABLE t3 ( col1 INT PRIMARY KEY, col2 VARCHAR(500) ) WITH (DATA_COMPRESSION = PAGE); GO BEGIN TRANSACTION DECLARE @idx INT = 0; WHILE @idx < 1000000 BEGIN INSERT INTO t3 (col1, col2) VALUES ( @idx, REPLICATE('a', 100) + CAST(@idx AS VARCHAR(10)) + REPLICATE('a', 380) ) SET @idx = @idx + 1 END COMMIT; GO SELECT page_count, compressed_page_count, forwarded_record_count, * FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'SAMPLED'); SELECT page_count, compressed_page_count, forwarded_record_count, * FROM sys.dm_db_index_physical_stats(db_id(), object_id('t3'), NULL, NULL, 'DETAILED');

G. Запрос очередей посредника служб для фрагментации индекса

Область применения: SQL Server 2016 (13.x) и более поздних версий.

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

--Using queue internal table name SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('sys.queue_messages_549576996'), DEFAULT, DEFAULT, DEFAULT); --Using queue name directly SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('ExpenseQueue'), DEFAULT, DEFAULT, DEFAULT);

См. также

Динамические административные представления и функции (Transact-SQL)
Индексы, связанные с динамическими административными представлениями и функциями (Transact-SQL)
sys.dm_db_index_operational_stats (Transact-SQL)
sys.dm_db_index_usage_stats (Transact-SQL)
sys.dm_db_partition_stats (Transact-SQL)
sys.allocation_units (Transact-SQL)
Системные представления (Transact-SQL)

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

Были ли сведения на этой странице полезными?

Индексы в базе данных Oracle

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

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

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

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

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

Уникальные и неуникальные индексы. Уникальные индексы основаны на уникальном столбце – обычно вроде номера карточки социального страхования сотрудника. Хотя уникальные индексы можно создавать явно, Oracle не рекомендует это делать. Вместо этого следует использовать уникальные ограничения. Когда накладывается ограничение уникальности на столбец таблицы, Oracle автоматически создает уникальные индексы по этим столбцам.
Первичные и вторичные индексы. Первичные индексы – это уникальные индексы в таблице, которые всегда должны иметь какое-то значение и не могут быть равны null. Вторичные индексы – это прочие индексы таблицы, которые могут и не быть уникальными.
Составные индексы – индексы, содержащие два или более столбца из одной и той же таблицы. Они также известны как сцепленные индексы (concatenated index). Составные индексы особенно полезны для обеспечения уникальности сочетания столбцов таблицы в тех случаях, когда нет уникального столбца, однозначно идентифицирующего строку.

Руководство по созданию индексов

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

Индекс имеет смысл, если нужно обеспечить доступ одновременно не более чем к 4-5% данных таблицы. Альтернативной использования индекса для доступа к данным строки является полное последовательное чтение таблицы от начала до конца, что называется полным сканированием таблицы. Полное сканирование таблицы больше подходит для запросов, которые требуют извлечения большего процента данных таблицы. Помните, что применение индексов для извлечения строк требует двух операций чтения: индекса и затем таблицы.
Избегайте создания индексов для сравнительно небольших таблиц. Для таких таблиц больше подходит полное сканирование. В случае маленьких таблиц нет необходимости в хранении данных и таблиц, и индексов.
Создавайте первичные ключи для всех таблиц. При назначении столбца в качестве первичного колюча Oracle автоматически создаст индекс по этому столбцу.
Индексируйте столбцы, участвующие в многотабличных операциях соединения.
Индексируйте столбцы, которые часто используются в конструкциях WHERE.
Индексируйте столбцы, участвующие в операциях ORDER BY и GROUP BY или других операциях, таких как UNION и DISTINCT, включающих сортировку. Поскольку индексы уже отсортированы, объем работы по выполнению необходимой сортировки данных для упомянутых операций будет существенно сокращен.
Столбцы, стоящие из длинно-символьных строк, обычно плохие кандидаты на индексацию.
Столбцы, которые часто обновляются, в идеале не должны быть индексированы из-за связанных с этим накладных расходов.
Индексируйте таблицы в которых мало строк имеют одинаковые значения.
Сохраняйте количество индексов небольшим.
Составные индексы могут понадобиться там, где одностолбцовые значения сами по себе не уникальны. В составных индексах первым столбцом ключа должен быть столбец в котором количество строк с одинаковым значением минимально.

Всегда помните золотое правило индексации таблиц: индекс таблицы должен быть основан на типах запросов, которые будут выполняться над столбцами этой таблицы. На таблице можно создавать более одного индекса: например, можно создать индекс на столбце X, или столбце Y, или обоих сразу, а также один составной индекс на обоих столбцах. Принимая правильное решение относительно того, какие индексы следует создавать, подумайте о наиболее часто используемых типах запросов данных таблицы.

Схемы индексации Oracle

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

(B*tree)

В реализации индексов на основе B-деревьев используется концепция сбалансированного (на что указывает буква ‘B’ (balanced)) дерева поиска в качестве основы структуры индекса. В Oracle имеется собственный вариант B-дерева. Это обычные индексы, создаваемые по умолчанию, когда вы применяете оператора CREATE INDEX.

Индексы на основе B-деревьев структурированы в форме обратного дерева, где блоки верхнего уровня называются блоками ветвей (branch blocks), а блоки нижнего уровня – листовыми блоками (leaf blocks). В иерархии узлов все узлы кроме вершины, или корневого узла, имеют родительский узел и могут иметь ноль или более дочерних узлов. Если глубина древовидной структуры , т.е. количество уровней, одинакова от каждого листового блока до корневого узла, то такое дерево называется сбалансированным, или B-деревом.

B-деревья автоматически поддерживают необходимый уровень индекса по размеру таблицы. B-деревья также гарантируют, что индексные блоки всегда будут заполнены не меньше, чем наполовину, и менее, чем на 100%. B-деревья допускают операции выборки, вставки и удаления с очень небольшим количеством операций ввода-вывода на один оператор. Большинство B-деревьев имеет всего три и менее уровней. При использовании B-дерева нужно читать только блоки B-дерева, так что количество операций ввода-вывода будет ограничено числом уровней B-дерева (скажем, тремя) плюс две операции ввода-вывода на выполнение обновления или удаления (одна для чтения и одна для записи). Для выполнения поиска по B-дереву понадоисят всего три или менее обращений к диску.

Реализация B-дерева от Oracle – всегда сохраняет дерево сбалансированным. Листовые блоки содержат по два элемента: индексированные значения столбца и соответствующий идентификатор ROWID для строки, которая содержит это значение столбца. ROWID – уникальный указатель Oracle, идентифицирующий физическое местоположение строки и обеспечивающий самый быстрый способ доступа к строке в базе данных Oracle. Сканирование индекса быстро дает ROWID строки, и отсюда можно быстро получить к ней доступ непосредственно. Если запрос нуждается лишь в значении индексированного столбца, то конечно, последний шаг исключается, поскольку извлекать дополнительные данные, кроме прочитанных из индекса, не потребуется.

Оценка размера индекса

Для оценки размера нового индекса можно использовать пакет DBMS_SPACE. Процедуре CREATE_INDEX_COST этого пакета потребуется передать оператор DDL, создающий индекс, в качестве атрибута.

SET SERVEROUTPUT ON DECLARE l_index_ddl varchar2(1000); l_used_bytes NUMBER; l_allocated_bytes NUMBER; BEGIN DBMS_SPACE.create_index_cost ( ddl => 'create index repsons_idx on EMP(ENAME)', used_bytes => l_used_bytes, alloc_bytes => l_allocated_bytes); DBMS_OUTPUT.PUT_LINE ('RESULT:'); DBMS_OUTPUT.PUT_LINE ('used_bytes = ' || l_used_bytes || ' byte'); DBMS_OUTPUT.PUT_LINE ('alloc_bytes = ' || l_allocated_bytes || ' byte'); END; /

Обратите внимание на отличие между атрибутами, касающимися размера, в процедуре CREATE_INDEX_COST:

Used_bytes показывает количество байт, которыми представлены данные индекса;
Alloc_bytes показывает количество байт, которое займет индекс в табличном пространстве после его создания.

Создание индекса

Индекс создается с помощью оператора CREATE INDEX

CREATE INDEX employee_id ON employee(employee_id) TABLESPACE MY_INDEXES;

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

Для создания уникального индекса служит оператор CREATE UNIQUE INDEX.

Специальные типы индексов

Нормальный или типовой индекс, который создается в базе данных, называется индексом кучи (heap index), или неупорядоченным индексом. Oracle также предоставляет несколько специальных типов индексов для специфических нужд.

Битовые индексы (bitmap indexes)

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

Битовые индексы состоят из битового потока (единиц и нулей) для каждого столбца индекса. Битовые индексы очень компактны по сравнению с нормальными индексами на основе B-деревьев.

Индексы B-деревьев	Битовые индексы
Хороши для данных с высокой кардинальностью	Хороши для данных с низкой кардинальностью
Хороши для баз данных OLTP	Хороши для приложений хранилищ данных OLAP
Занимают много места	Используют, относительно мало места
Легко обновляются	Трудно обновляются

Для создания битового индекса используется оператор

CREATE BITMAP INDEX gender_dx ON employee(gender) TABLESPACE MY_INDEXES;

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

Индексы с реверсированным ключом

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

SQL> CREATE INDEX reverse_idx ON employee(emp_id) REVERSE;

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

Индексы со сжатым ключом

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

SQL> CREATE INDEX emp_indx1 ON employees(ename) TABLESPACE MY_INDEXES COMPRESS 1;

Приведенный выше оператор сжимает все дублированные вхождения индексированного ключа в листовом блоке индекса (на уровне 1).

Индексы на основе функций

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

Ниже показано, как создать индекс на основе функции LOWER

SQL> CREATE INDEX lastname _idx ON employees(LOWER(l_name));

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

Секционированные индексы

Секционированные индексы используются для индексации секционированных таблиц. Oracle предлагает два типа индексов для таких таблиц: локальные и глобальные.

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

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

Глобальные индексы

Глобальные индексы на секционированных таблицах могут быть как секционированными, так и несекционированными. Глобальные несекционированные индексы подобны обычным индексам Oracle для несекционированных таблиц. Для создания таких индексов применяется обычный синтаксис CREATE INDEX.

Ниже приведен пример глобального индекса на таблице ticket_sales:

SQL> CREATE INDEX tickersales_idx ON ticket_sales(month) GLOBAL PARTITION BY range(month) (PARTITION ticketsales1_idx VALUES LESS THAN (3) PARTITION ticketsales1_idx VALUES LESS THAN (6) PARTITION ticketsales2_idx VALUES LESS THAN (9) PARTITION ticketsales3_idx VALUES LESS THAN (MAXVALUE);

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

Давайте в качестве примера воспользуемся таблицей ticket_sales, чтобы разобраться, почему это так. Предположим, что вы ежеквартально уничтожаете самый старый раздел, чтобы освободить место для нового раздела, в который поступят данные за новый квартал. Когда уничтожается раздел, относящийся к таблице ticket_sales, глобальные индексы могут стать недействительными, потому что часть данных, на которые они указывают, перестают существовать. Чтобы предотвратить такое объявление недействительным индекса из-за уничтожения раздела, необходимо использовать опцию UPDATE GLOBAL INDEXES вместе с оператором DROP PARTITION:

SQL> ALTER TABLE ticket_sales DROP PARTITION sales_quarter01 UPDATE GLOBAL INDEXES;

Если не включить оператор UPDATE GLOBAL INDEXES, то все глобальные индексы станут недействительными. Опцию UPDATE GLOBAL INDEXES можно также использовать при добавлении, объединении, обмене, слиянии, перемещении, разделении или усечении секционированных таблиц. Разумеется, с помощью ALTER INDEX..REBUILD можно перестраивать любой индекс, который становится недействительным, но эта опция также требует дополнительных затрат времени и обслуживания.

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

SQL> CREATE INDEX hgidx ON tab (c1,c2,c3) GLOBAL PARITION BY HASH (c1,c2) ( PARTITION p1 TABLESPACE tsb_1, PARTITION p2 TABLESPACE tsb_2, PARTITION p3 TABLESPACE tsb_3, PARTITION p4 TABLESPACE tsb_4, );

Локальные индексы

Локально секционированные индексы, в отличие от глобально секционированных индексов, имею отношение «один к одному» с разделами таблицы. Локально секционированные индексы можно создавать в соответствии с разделами и даже подразделами. База данных конструирует индекс таким образом, чтобы он был секционирован так же, как и его таблица. При каждой модификации раздела таблицы база автоматически сопровождает это соответствующей модификацией раздела индекса. Это, наверное, самое большое преимущество использования локально секционированных индексов – Oracle автоматически перестраивает их всегда, когда уничтожается раздел или над ним выполняется какая-то другая операция DDL.

Ниже приведен простой пример создания локально секционированного индекса на секционированной таблице:

SQL> CREATE INDEX ticket_no_idx ON Ticket_sales(ticket_no) LOCAL TABLESPACE localidx_01;

Невидимые индексы

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

База данных поддерживает невидимый индекс точно так же, как и нормальный (видимый) индекс. После объявления индекса невидимым, его и все прочие невидимые индексы можно сделать вновь видимым для оптимизатора, установив значение параметра optimizer_use_invisible_index равным TRUE на уровне сеанса или всей системы. Значением этого параметра по умолчанию является FALSE, а это означает, что оптимизатор по умолчанию не может использовать невидимые индексы.

Создание невидимого индекса.

Чтобы сделать индекс невидимым, к оператору CRETE INDEX нужно добавить конструкцию INVISIBLE.

С помощью команды ALTER INDEX можно превратить существующий индекс в невидимый.

ALTER INDEX test_idx INVISIBLE;

И обратная команда

ALTER INDEX test_idx VISIBLE;

Приведенный ниже запрос к представлению DBA_INDEXES показывает состояние видимости индекса:

SQL> SELECT index_name, visibility FROM user_indexes WHERE index_name =’indx1’;

Мониторинг использования индекса

Если вы сомневаетесь в использовании определенного индекса, можете попросить Oracle выполнить мониторинг его применения. Таким образом, если индекс окажется избыточным, его можно уничтожить и сэкономить место в хранилище, а также снизить накладные расходы на операции DML.

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

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

SQL> ALTER INDEX p_key_sales MONITORING USAGE;

Теперь запустите какие-нибудь запросы к таблице sales. Завершите мониторинг, применив следующую команду:

SQL> ALTER INDEX p_key_sales NOMONITORING USAGE;

После этого можно запросить представление словаря данных V$OBJECT_USAGE для определения того, используется ли индекс p_key_sales.

SQL> SELECT index_nm, used FROM v$object_usage WHERE index_name=’P_KEY_SALES’;

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

Обслуживание индексов

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

ALTER INDEX sales_idx REBUILD;

Перестройка индексов лучше уничтожения и воссоздания неудачного индекса, потому что при этой операции пользователи продолжают иметь доступ к индексу в процессе его перестройки. Однако индексы в процессе перестройки накладывают много ограничений на действия пользователя. Еще более эффективный способ перестройки индексов состоит в том, чтобы сделать это в оперативном (online) режиме, как показано в следующем примере. Во время оперативной перестройки индекса разрешено применение всех операций DML, но не операций DDL.

ALTER INDEX sales_idx REBUILD ONLINE;

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

Пример запроса который показывает на какие внешние ключи отсутствуют индексы

elect table_name, constraint_name, cname1 || nvl2(cname2,','||cname2,null) || nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) || nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) || nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null) columns from ( select b.table_name, b.constraint_name, max(decode( position, 1, column_name, null )) cname1, max(decode( position, 2, column_name, null )) cname2, max(decode( position, 3, column_name, null )) cname3, max(decode( position, 4, column_name, null )) cname4, max(decode( position, 5, column_name, null )) cname5, max(decode( position, 6, column_name, null )) cname6, max(decode( position, 7, column_name, null )) cname7, max(decode( position, 8, column_name, null )) cname8, count(*) col_cnt from (select substr(table_name,1,30) table_name, substr(constraint_name,1,30) constraint_name, substr(column_name,1,30) column_name, position from user_cons_columns ) a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'R' group by b.table_name, b.constraint_name ) cons where col_cnt > ALL ( select count(*) from user_ind_columns i where i.table_name = cons.table_name and i.column_name in (cname1, cname2, cname3, cname4, cname5, cname6, cname7, cname8 ) and i.column_position  cons.col_cnt group by i.index_name )

Tags: Oracle Database, Indexes

Как посмотреть ход процесса индексации бд sql

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ограничения

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

Примеры

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

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

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

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

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

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

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

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

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

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

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

Как отследить ход процесса индексации в Manticore / Sphinx?

sys.dm_db_index_physical_stats (Transact-SQL)

Синтаксис

Аргументы

database_id | NULL | 0 | ПО УМОЛЧАНИЮ

object_id | NULL | 0 | ПО УМОЛЧАНИЮ

index_id | 0 | NULL | -1 | ПО УМОЛЧАНИЮ

partition_number | NULL | 0 | ПО УМОЛЧАНИЮ

режим | NULL | ПО УМОЛЧАНИЮ

Таблица возвращенной информации

Замечания

Режимы сканирования

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

Рекомендация

Обнаружение фрагментации

Логическая фрагментация

Фрагментация экстентов

Уменьшение фрагментации в индексе

Уменьшение фрагментации в куче

Компактные данные больших объектов

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

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

ограничения

Разрешения

Примеры

О. Возврат сведений об указанной таблице

B. Возврат сведений о куче

C. Возврат сведений для всех баз данных

D. Использование sys.dm_db_index_physical_stats в скрипте для перестроения или реорганизации индексов

Д. Использование sys.dm_db_index_physical_stats для отображения количества страниц, сжатых на странице

Е. Использование в режиме sys.dm_db_index_physical_stats SAMPLED

G. Запрос очередей посредника служб для фрагментации индекса

См. также

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

Индексы в базе данных Oracle

Руководство по созданию индексов

Схемы индексации Oracle

(B*tree)

Оценка размера индекса

Создание индекса

Специальные типы индексов

Битовые индексы (bitmap indexes)

Индексы с реверсированным ключом

Индексы со сжатым ключом

Индексы на основе функций

Секционированные индексы

Глобальные индексы

Локальные индексы

Невидимые индексы