Знакомство с проверками при обслуживании базы данных
Оптимизатор запросов использует статистические сведения из индексов, чтобы попытаться сформировать наиболее оптимальный план выполнения.
В рамках обслуживания SQL Azure выполняются такие задачи, как резервное копирование и проверка целостности, и, хотя вы можете получить автоматические обновления при сохранении актуальности статистики, иногда этого недостаточно.
Наличие пригодных индексов и статистики гарантирует, что любой данный план будет выполняться с оптимальной эффективностью. Обслуживание индексов должно выполняться регулярно по мере изменения данных в базах данных с течением времени. Стратегию обслуживания индексов можно скорректировать на основе частоты изменений в данных.
Перестроение и реорганизация
Фрагментация индексов выполняется, когда логический порядок на страницах индекса не соответствует физическому упорядочению. Страницы могут быть неупорядоченными во время выполнения стандартных инструкций модификации данных, таких как UPDATE
, DELETE
и INSERT
. Фрагментация может вызвать проблемы с производительностью из-за дополнительных операций ввода-вывода, необходимых для поиска данных, на которые ссылаются указатели на страницах индекса.
По мере вставки, обновления и удаления данных из индексов логическое упорядочение в индексе больше не будет соответствовать физическому упорядочению внутри страниц и между страницами, составляющими индексы. Кроме того, со временем изменения данных могут привести к тому, что сами данные станут разрозненными или фрагментированными в базе данных. Такая фрагментация может снизить производительность запросов в случае необходимости чтения ядром СУБД дополнительных страниц для поиска необходимых данных.
Реорганизация индекса — это выполняемая онлайн операция дефрагментации конечного уровня индекса (как кластеризованного, так и некластеризованного). В ходе этого процесса дефрагментации страницы конечного уровня переупорядочиваются физически, чтобы они соответствовали логическому порядку узлов слева направо. Во время этого процесса страницы индекса также сжимаются на основе настроенного значения коэффициента заполнения.
Перестроение может выполняться либо в оперативном, либо автономном режиме в зависимости от выполняемой команды или используемого выпуска SQL Server. В процессе перестроения в автономном режиме индекс удаляется и создается повторно. Если это можно сделать в оперативном режиме, новый индекс будет создан параллельно с существующим индексом. После построения нового индекса существующий индекс будет удален, а новому будет присвоено имя старого индекса. Следует иметь в виду, что для выполнения онлайн операции потребуется дополнительное пространство, так как новый индекс создается параллельно с существующим индексом.
Общие рекомендации по обслуживанию индекса:
>5%, но < 30 % — Реорганизация индекса
>
30% — Перестроение индекса
Используйте эти значения в качестве общих рекомендаций. В зависимости от рабочей нагрузки и данных может потребоваться более интенсивная работа или, в некоторых случаях, потребуется отложить обслуживание индексов для баз данных, которые в основном выполняют запросы по поиску определенных страниц.
В платформах SQL Server и SQL Azure имеются динамические административные представления, которые позволяют обнаруживать фрагментацию в объектах. Чаще всего для этой цели используются динамическое административное представление sys.dm_db_index_physical_stats
для индексов сбалансированного дерева и sys.dm_db_column_store_row_group_physical_stats
для индексов columnstore.
Обратите внимание, что перестроение индекса приводит к обновлению статистики по индексу, что позволит в дальнейшем повысить производительность. При реорганизации индекса статистика не обновляется.
Корпорация Microsoft представила возобновляемые операции перестроения индексов начиная с версии SQL Server 2017. Возобновляемые операции перестроения индексов обеспечивают большую гибкость в определении периода времени, в течение которого операции перестроения применяются к данному экземпляру. С выпуском SQL Server 2019 была представлена возможность контроля над соответствующей максимальной степенью параллелизма, что предоставляет администраторам баз данных возможность более детального управления.
Статистика
При настройке производительности в SQL Azure важно понимать, насколько важна статистика.
Статистика хранится в пользовательской базе данных в виде больших двоичных объектов (BLOB). В этих больших двоичных объектах содержатся статистические сведения о распределении значений данных в одном или нескольких столбцах таблицы или индексированного представления.
Статистика содержит сведения о распределении значений данных в столбце. Оптимизатор запросов использует статистику столбцов и индексов, чтобы определить кратность, которая представляет собой число строк, возвращаемых запросом.
Затем оценки кратности используются оптимизатором запросов для создания плана выполнения. Оценка кратности также помогает оптимизатору определить тип операции (например, поиск по индексу или сканирование), который будет использоваться для получения запрошенных данных.
Чтобы просмотреть список определяемых пользователем статистических данных с последней обновленной датой, выполните следующий запрос:
SELECT sp.stats_id,
name,
last_updated,
rows,
rows_sampled
FROM sys.stats
CROSS APPLY sys.dm_db_stats_properties(object_id, stats_id) AS sp
WHERE user_created = 1
Создание статистики
Если параметр AUTO_CREATE_STATISTICS
применяется к ON
, оптимизатор запросов по умолчанию создает статистику по индексированного столбцу. Оптимизатор запросов также создает статистику для отдельных столбцов в предикатах запросов.
Эти методы обеспечивают высококачественные планы запросов для большинства запросов. Иногда может потребоваться создать дополнительные статистические данные с помощью инструкции CREATE STATISTICS
для оптимизации конкретных планов запросов.
Рекомендуется активировать параметр AUTO_CREATE_STATISTICS
, так как это позволит оптимизатору запросов автоматически создавать статистику для столбцов предиката запросов.
В следующих ситуациях рассмотрите возможность создания статистики:
- Помощник по настройке ядра СУБД рекомендует создание статистики
- Предикат запроса содержит несколько столбцов, которые еще не включены в один индекс
- Запрос выполняет выборку из подмножества данных
- Для запроса отсутствует статистика
Автоматизация задач обслуживания
SQL Azure имеет собственные средства для выполнения задач обслуживания базы данных в целях автоматизации. Доступность различных средств зависит от платформы, на которой выполняется база данных.
SQL Server на виртуальной машине Azure
Имеется доступ к службам планирования, таким как Агент SQL или планировщик задач Windows. Использование этих средств автоматизации способствует минимизации объема фрагментации в индексах. В больших базах данных для обеспечения оптимальной производительности необходимо найти баланс между перестроением и реорганизацией индексов. Гибкость, обеспечиваемая Агентом SQL или планировщиком задач, позволяет выполнять собственные задания.
База данных SQL Azure
В базе данных SQL Azure нет доступа к Агенту SQL Server и планировщику задач Windows ввиду характера этой базы данных. Без этих служб обслуживание индекса создается другими способами. В базе данных SQL существует три способа управления операциями обслуживания:
Runbook в службе автоматизации Azure
Задание Агента SQL из SQL Server на виртуальной машине Azure (удаленный вызов)
Эластичные пулы Azure SQL
Управляемый экземпляр SQL Azure
Как и в случае с SQL Server на виртуальной машине Azure, можно планировать задания для выполнения на управляемом экземпляре SQL с помощью Агента SQL Server. Использование Агента SQL Server обеспечивает гибкость при выполнении кода, специально разработанного для минимизации фрагментации в индексах базы данных.