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

План обслуживания «на каждый день» – Часть 1: Автоматическая дефрагментация индексов

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

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

Среди подобных задач можно выделить следующие:

Рассмотрим по порядку автоматизацию каждой из этих задач.

UPDATE 2019-06-03:
Но сперва хочется чуток порекламировать опенсорс программу, которую я сделал спустя 5 лет после написания этого поста. Так уж исторически сложилось, что долгое время участвовал в разработке системных тулов для обслуживания SQL Server. За это время накопилось много идей и на определенном этапе захотелось сделать что-то свое.

В результате получилось приложение, которое позволяет обслуживать индексы через удобный UI. За основных конкурентов брались платные аналоги от компаний RedGate и Devart.

Но вернемся теперь к изначальному посту. Итак, пункт первый…

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

1. Фрагментация внутри отдельных страниц индекса

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

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

Бороться с данным видом фрагментации стоит на этапе проектировании схемы, т. е. выбирать такие типы данных, которые бы компактно умещались на страницах.

2. Фрагментация внутри структур индекса

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

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

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

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

Для борьбы с фрагментацией индексов в арсенале SQL Server предусмотрены команды: ALTER INDEX REBUILD / REORGANIZE.

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

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

Степень фрагментации того или иного индекса можно узнать из динамического системного представления sys.dm_db_index_physical_stats:

В данном запросе, последний параметр задает режим, от значения которого возможно быстрое, но не совсем точное определения уровня фрагментации индекса (режимы LIMITED/NULL). Поэтому рекомендуется задавать режимы SAMPLED/DETAILED.

Мы знаем откуда получить список фрагментированных индексов. Теперь необходимо для каждого из них сгенерировать соответствующую ALTER INDEX команду. Традиционно для этого используют курсор:

Чтобы ускорить процесс пересоздания индекса рекомендуется дополнительно указывать опцию SORT_IN_TEMPDB. Еще нужно отдельно упомянуть про опцию ONLINE — она замедляет пересоздание индекса. Но иногда бывает полезной. Например, чтение из кластерного индекса очень дорогое. Мы создали покрывающий индекс и решили проблему с производительностью. Далее мы делаем REBUILD некластерного индекса. В этот момент нам придется снова обращаться к кластерному индексу — что снижает перфоманс.

SORT_IN_TEMPDB позволяет перестраивать индексы в базе tempdb, что бывает особенно полезно для больших индексов в случае нехватки памяти и ином случае — опция игнорируется. Кроме того, если база tempdb расположена на другом диске — это существенно сократит время создания индекса. ONLINE позволяет пересоздать индекс не блокируя при этом запросы к объекту для которого этот индекс создается.

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

В дополнении, приведенный выше запрос можно переписать без применения курсора:

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

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

UPDATE 2016-04-22: добавил возможность дефрагментации отдельных секций и исправил некоторые баги

Источник

Реорганизация индекса и Перестроение индекса в плане обслуживания Sql Server

в правилах SSW для улучшения базы данных SQL Server приведен пример полного плана обслуживания базы данных:SSW. В этом примере они запускают как индекс реорганизации, так и индекс перестройки, а затем обновляют статистику. Какой в этом смысл? Я думал, что реорганизация индекса была быстрой, но менее эффективной версией индекса перестройки? и что перестроение индекса также автоматически обновит статистику (по крайней мере, по кластеризованному индексу).

9 ответов

хуже того, что в схеме плана обслуживания от SSW он выполняет SHRINK во-первых, который фрагментирует индексы как побочный эффект того, как он освобождает пространство. Тогда REBUILD снова выделяет больше места для файлов базы данных в качестве рабочего пространства во время REBUILD операция.

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

не используйте SHRINK за исключением TRUNCATEONLY опция, и даже тогда, если файл будет расти снова, то вы должны думать о том, нужно ли это:

реорганизация и перестройка-это разные вещи.

Reorganize: это дефрагментация для индексов. Принимает существующий индекс(индексы) и дефрагментирует существующие страницы. Однако, если страницы не находятся в непрерывном порядке, они остаются такими же, как и раньше. Меняется только содержание страниц.

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

кроме того, с помощью rebuild вы можете изменить секционирование или группы файлов, но с помощью reorganize вы можете дефрагментировать не только весь индекс, но и только один раздел индекса.

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

прежде чем рассматривать содержание индексов, важно ответить на два основных вопроса:

как описано в этой статье http://solutioncenter.apexsql.com/why-when-and-how-to-rebuild-and-reorganize-sql-server-indexes/, и чтобы помочь вам определить, следует ли выполнять перестройку индекса или реорганизацию индекса, пожалуйста поймите следующее:

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

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

в вышеупомянутой статье также объясняется, как реорганизовать и перестроить индексы с помощью SSMS, T-SQL (для реорганизации/перестроения индексов в таблице) и стороннего инструмента ApexSQL Backup.

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

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

несколько раз реорг будет быстрее, а несколько раз реиндекс будет быстрее в зависимости от того, насколько фрагментирован индекс. Чем менее фрагментирован индекс, тем реорг будет чем быстрее, тем более фрагментированным будет реорг, но тем быстрее будет реиндекс.

Источник

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

Содержание

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

SQL Server 2012

Компонент Компонент SQL Server Database Engine автоматически поддерживает состояние индексов при выполнении операций вставки, обновления или удаления в отношении базовых данных. Со временем эти изменения могут привести к тому, что данные в индексе окажутся разбросанными по базе данных (фрагментированными). Фрагментация имеет место в тех случаях, когда в индексах содержатся страницы, для которых логический порядок, основанный на значении ключа, не совпадает с физическим порядком в файле данных. Значительно фрагментированные индексы могут серьезно снижать производительность запросов и служить причиной замедления откликов приложения.

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

Среда SQL Server Management Studio

Среда SQL Server Management Studio

Перед началом работы [ ]

Выявление фрагментации [ ]

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

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

avg_fragmentation_in_percentПроцентная доля логической фрагментации (неупорядоченные страницы в индексе).fragment_countЧисло фрагментов (физически последовательные конечные страницы) в индексе.avg_fragment_size_in_pagesСреднее число страниц в одном фрагменте индекса.

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

> 5 % и 30%ALTER INDEX REBUILD WITH (ONLINE = ON)*

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

Однако фактические значения могут различаться в каждом конкретном случае. Важно определить наилучшее пороговое значение для используемой среды экспериментальным путем.При очень низких уровнях фрагментации (менее 5 %) эти команды использоваться не должны, так как выгода от дефрагментации столь низкого уровня почти всегда в достаточной степени компенсируется за счет реорганизации или перестроения индекса.

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

Ограничения [ ]

Безопасность [ ]

Разрешения [ ]

Пользователь должен быть членом предопределенной роли сервера sysadmin или предопределенных ролей базы данных db_ddladmin и db_owner.

Использование среды SQL Server Management Studio [ ]

Проверка фрагментации индекса [ ]

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

Использование Transact-SQL [ ]

Проверка фрагментации индекса [ ]

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

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

Использование среды SQL Server Management Studio [ ]

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

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

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

Использование Transact-SQL [ ]

Реорганизация дефрагментированного индекса [ ]

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

Перестроение дефрагментированного индекса [ ]

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

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

Источник

Задача «Перестроение индекса» (план обслуживания)

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

Задача «Перестроение индекса» использует инструкцию ALTER INDEX. Дополнительные сведения о параметрах, описанных на этой странице, см. в разделе ALTER INDEX (Transact-SQL).

Параметры

Соединение
Выберите соединение с сервером, которое будет использоваться для выполнения этой задачи.

Создать
Создать новое соединение с сервером для его использования при выполнении этой задачи. Диалоговое окно Создание соединения описано ниже.

Базы данных
Укажите базы данных, для которых должна выполняться эта задача.

Все базы данных

Все системные базы данных

Все пользовательские базы данных

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

Определенные базы данных

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

Планы обслуживания выполняются только для баз данных, уровень совместимости которых 80 или выше. Базы данных с уровнем совместимости 70 или ниже не отображаются.

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

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

Изменить долю свободного места на странице
Удалите индексы таблиц в базе данных и создайте их повторно с новым, автоматически вычисляемым коэффициентом заполнения, резервирующим указанный объем свободного пространства на страницах индекса. Чем выше процентное значение, тем больше свободного места резервируется на страницах индекса и тем больше будет размер индекса. Допустимые значения: от 0 до 100.

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

разредить индекс
Укажите заполнение индекса.

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

Операции с индексами в сети доступны не во всех выпусках MicrosoftSQL Server. Сведения о функциях, поддерживаемых различными выпусками SQL Server, см. в статье Возможности, поддерживаемые выпусками SQL Server 2016.

Не перестраивать индексы | Перестроить индексы в режиме «вне сети»
Укажите, что нужно делать для типов индексов, которые невозможно перестроить, пока они находятся в сети.

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

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

Максимальная длительность
Укажите длительность ожидания блокировок с низким приоритетом.

Если количество затронутых объектов велико, построение этого отображения может занять значительное время.

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

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

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

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

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

Диалоговое окно «Создание соединения»

Имя соединения
Введите имя нового соединения.

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

Обновить
Обновите список доступных серверов.

Введите данные для входа на сервер
Укажите способ проверки подлинности на сервере.

Использовать встроенную безопасность Windows
Подключиться к экземпляру Компонент SQL Server Database Engine с использованием проверки подлинности Windows.

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

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Ограничения

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

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

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

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

Примеры

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

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

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

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

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

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

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

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

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

Дополнительные примеры использования Transact-SQL для перестроения или реорганизации индексов см. в разделах Примеры: индексы rowstore и Примеры: индексы columnstore.

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

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

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

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

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

Источник