Настройка запросов после миграции
Предположим, вы являетесь администратором баз данных в юридической компании. В выходные дни вы перенесли в SQL Server 2019 несколько баз данных, включая базу данных управления отношениями с клиентами (CRM). Вы приходите на работу рано утром в понедельник за пару часов до того, как сотрудники отдела продаж приступят к работе и начнут звонить клиентам и принимать от них звонки. Отдел продаж зависит от базы данных CRM, и для эффективного взаимодействия с клиентами приложение должно мгновенно реагировать.
После переноса баз данных вы оставили уровень совместимости без изменений. Приложения CRM подключены к новому серверу. Вы хотите измерить производительность запросов в новой системе с обычной рабочей нагрузкой понедельника перед изменением уровня совместимости. Обеспечение производительности запросов имеет важное значение при обновлении баз данных.
Вы не хотите ждать, пока группа продаж обнаружит, что приложение CRM стало работать медленно. Вместо этого вы хотите реализовать процесс для автоматического измерения рабочей нагрузки и производительности. Это придаст уверенности в том, что вы сможете устранить проблемы с медленными запросами после изменения уровня совместимости.
Хранилище запросов
Функция хранилища запросов появилась в SQL Server 2016 и предназначена для непрерывного сбора сведений о запросах. хранилище запросов собирает сведения, которые можно использовать для устранения неполадок с недоработающими запросами, работая с локальными и облачными установками. Кэшированные планы запросов хранятся в кэше процедур, однако в SQL Server хранится только последний план выполнения.
По всей вероятности, планы выполнения для запроса будут изменяться со временем из-за изменений схемы либо добавления или удаления индексов. Кроме того, интенсивное использование памяти может привести к исключению планов из кэша планов.
По умолчанию хранилище запросов не включается при создании или переносе базы данных в SQL Server 2017 или SQL Server 2019. Однако перед обновлением уровня совместимости базы данных в перенесенной базе данных можно включить функцию хранилище запросов. Включите хранилище запросов, щелкнув правой кнопкой мыши базу данных в обозреватель объектов, выбрав "Свойства", а затем выберите страницу хранилище запросов. В списке Режим работы (запрошенный) выберите значение Отключено, Только для чтения или Чтение и запись. Вы также можете включить хранилище запросов с помощью Transact-SQL. Чтобы включить хранилище запросов для базы данных с именем CustomerServices, выполните следующую команду:
ALTER DATABASE CustomerServices SET QUERY_STORE = ON
При первоначальной компиляции запроса текст запроса и первый план передаются в хранилище запросов. Если запрос перекомпилируется, план в хранилище запросов обновляется. После создания нового плана хранилище запросов добавляет и сохраняет его вместе с предыдущими планами и статистикой выполнения.
При выполнении запроса в хранилище запросов сохраняется статистика времени выполнения. Во время компиляции или перекомпиляции SQL Server проверяет, имеется ли в хранилище запросов план, который следует применить к запросу. Если применяемый план отличается от плана в кэше процедур, запрос перекомпилируется.
До тех пор пока уровень совместимости не изменится, к базе данных не применяются последние изменения оптимизатора запросов. Вы можете собирать типичную рабочую нагрузку и создавать базовые показатели для измерения на существующем уровне совместимости. После сбора достаточного количества информации о запросах для создания базовых показателей производительности, измените уровень совместимости базы данных на версию сервера.
Чтобы создать надлежащие базовые показатели, необходимо собрать достаточно данных за типичный период деятельности. При переходе на текущий уровень совместимости сервера рабочая нагрузка предоставляется последнему оптимизатору запросов. Производительность запросов, скорее всего, улучшится, так как новый оптимизатор запросов должен создавать лучшие планы.
Иногда SQL Server будет использовать менее оптимальный план запросов, поэтому он внезапно начинает выполняться медленно и без четкой причины. Эту проблему можно устранить, заставив предыдущий план в хранилище запросов. Использование хранилища запросов аналогично использованию указания запроса USE PLAN, но не требует изменения пользовательских приложений.
После изменения уровня совместимости хранилище запросов продолжает поддерживать запросы для обеспечения лучшей производительности. Оно измеряет такие показатели, как Регрессированные запросы, Общее потребление ресурсов, Основные запросы, потребляющие ресурсы, Запросы с принудительными планами, Запросы с высокой вариативностью и Отслеживаемые запросы.
Автоматическая настройка запросов
Роль оценщика количества элементов (CE) в SQL Server — оценка количества строк, возвращаемых запросом. Оптимизатор запросов использует результат выполнения CE для создания плана выполнения. До версии SQL Server 2014 оценщик количества элементов оставался практически неизменным. В SQL Server 2014 алгоритмы CE были переработаны для улучшения оценки и максимально быстрого предоставления результатов. Однако используемый оценщик количества элементов определяется уровнем совместимости базы данных.
Хотя производительность запросов в SQL Server 2014 и более поздних версиях, как правило, значительно выше, производительность перенесенной базы данных с измененным уровнем совместимости может снизиться. Автоматическая настройка запросов появилась в SQL Server 2017. Ее можно использовать в сочетании с хранилищем запросов для поиска и автоматического исправления регрессированных запросов. После переноса базы данных и регистрации рабочей нагрузки базы данных на прежнем уровне совместимости необходимо изменить уровень совместимости и продолжить выполнение хранилища запросов.
Автоматическая настройка использует данные из хранилища запросов для поиска запросов, производительность которых начинает снижаться из-за изменений в оценщике количества элементов после смены уровня совместимости. Автоматические эксперименты по настройке запросов регрессии для повышения производительности, что позволяет создавать руководства по плану, в которых выполняется улучшение.
Настройка производительности запросов в SQL Server 2019
Так как в нашем примере юридической компании вы обновили серверы баз данных до SQL Server 2019, то можете использовать как хранилище запросов, так и автоматическую настройку, чтобы оптимизировать производительность и убедиться в том, что изменение уровня совместимости не приводит к регрессии. Рекомендуется выполнить следующий процесс:
- Обновите сервер базы данных до SQL Server 2019, но не изменяйте уровень совместимости.
- Включите хранилище запросов.
- Соберите данные по производительности в рабочие часы с помощью хранилища запросов.
- Измените уровень совместимости базы данных.
- Проверьте, обнаружило ли хранилище запросов регрессии. Если да, можно принудительно применить последний удачный план запроса, чтобы восстановить более высокий уровень производительности.
Хранилище запросов автоматизирует процесс мониторинга производительности запросов. Это полезное средство, которое выполняется в фоновом режиме и непрерывно обеспечивает максимально быструю работу базы данных. Помощник по настройке запросов (QTA) применяется в сочетании с хранилищем запросов и использует данные из него для поиска регрессированных запросов в обновленных базах данных.
После переноса базы данных и изменения уровня совместимости до SQL Server 2014 или более поздней версии новый оценщик количества элементов определяет количество строк, возвращаемых запросом. Помощник по настройке запросов можно использовать, чтобы выявлять и исправлять запросы, производительность которых снижается с переходом на более новую версию CE.