Обновление баз данных с помощью помощника по настройке запросов

Область применения: SQL Server 2016 (13.x) и более поздних версий База данных SQL Azure Azure Synapse Analytics Analytics Platform System (PDW)

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

Начиная с SQL Server 2014 (12.x) и с каждой новой версией все изменения оптимизатора запросов включаются на последний уровень совместимости базы данных, поэтому планы выполнения не изменяются прямо на момент обновления, а вместо того, когда пользователь изменяет COMPATIBILITY_LEVEL параметр базы данных на последнюю доступную. Дополнительные сведения об изменениях оптимизатора запросов, представленных в SQL Server 2014 (12.x), см. в разделе "Оценка кратности". Дополнительные сведения об уровнях совместимости и их влиянии на обновления см. в разделе Уровни совместимости и обновления ядра СУБД.

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

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

Начиная с SQL Server Management Studio версии 18, новая функция помощника по настройке запросов (QTA) поможет пользователям использовать рекомендуемый рабочий процесс, чтобы обеспечить стабильность производительности при обновлении до более новых версий SQL Server, как описано в разделе "Сохранение стабильности производительности во время обновления до более новых сценариев использования SQL Server хранилище запросов". Тем не менее QTA не выполняет откат к ранее известному удачному плану, как показано на предыдущем шаге рекомендуемого рабочего процесса. Вместо этого QTA будет отслеживать регрессии, обнаруженные в представлении запросов со сниженной производительностью хранилища запросов, перебирая возможные перестановки вариантов применимой модели оптимизатора, чтобы создать новый улучшенный план.

Внимание

QTA не создает рабочую нагрузку пользователя. Если выполнение QTA в среде, которая не используется приложениями, убедитесь, что вы по-прежнему можете выполнять репрезентативную тестовую рабочую нагрузку на целевом ядро СУБД SQL Server другими средствами.

Рабочий процесс помощника по настройке запросов

Начальная точка QTA предполагает, что база данных из предыдущей версии SQL Server перемещается (через CREATE DATABASE ... FOR ATTACH или RESTORE) в более новую версию SQL Server ядро СУБД, а уровень совместимости базы данных перед обновлением не изменяется немедленно. QTA поможет выполнить следующие действия.

1. Настройка хранилища запросов в соответствии с рекомендуемыми параметрами для заданной пользователем продолжительности рабочей нагрузки (в днях). Продолжительность рабочей нагрузки следует определять так, чтобы она соответствовала стандартному бизнес-циклу.
2. Запрос на запуск необходимой рабочей нагрузки, чтобы хранилище запросов могло собрать базовые данные рабочей нагрузки (если их еще нет).
3. Обновление до целевого уровня совместимости базы данных, выбранного пользователем.
4. Запрос на второй сбор данных рабочей нагрузки для сравнения и обнаружения регрессии.
5. Итерация по регрессиям, найденным на основе представления Регрессионные запросы хранилища запросов, экспериментирование путем сбора статистики времени выполнения в возможных перестановках применимых вариантов модели оптимизатора и оценка результата.
6. Сообщение об измеренных улучшениях и сохранение этих изменений с помощью структур планов (при необходимости).

Дополнительные сведения о присоединении базы данных см. в статье Присоединение и отсоединение базы данных.

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

Внутренняя область поиска QTA

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

Из-за изменений в версиях модуля оценки кратности (CE) QTA работает с известными возможными шаблонами регрессий запросов. Например, при обновлении базы данных с SQL Server 2012 (11.x) и уровня совместимости базы данных 110 до SQL Server 2017 (14.x) и уровня совместимости базы данных 140 некоторые запросы могут регрессировать, так как они были разработаны специально для работы с версией CE, которая существовала в SQL Server 2012 (11.x) (CE 70). Это не значит, что единственным вариантом является возврат с CE 140 на CE 70. Если только определенное изменение в новой версии вводит регрессию, то можно указать, что запрос будет использовать только соответствующую часть предыдущей версии CE, которая работала лучше для конкретного запроса, а все еще использует все другие улучшения новых версий CE. Кроме того, можно разрешить другие запросы в рабочей нагрузке, которые не регрессировали, чтобы воспользоваться более новыми улучшениями CE.

Ниже перечислены шаблоны CE, поиск в которых выполнял QTA.

• Независимость и корреляция. Если предположение о независимости обеспечивает более высокую оценку для конкретного запроса, то указание USE HINT ('ASSUME_MIN_SELECTIVITY_FOR_FILTER_ESTIMATES') запроса приводит к созданию плана выполнения SQL Server с помощью минимальной селекторности при оценке AND предикатов для фильтров для учета корреляции. Дополнительные сведения см. в разделах Указания запроса USE HINT и Версии CE.
• Простое хранилище и базовый элемент. Если другое соединение содержит более точные оценки для конкретного запроса, то указание USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') запроса приводит к созданию плана выполнения SQL Server с помощью предположения простого сдерживания вместо допущения базового хранения по умолчанию. Дополнительные сведения см. в разделах Указания запроса USE HINT и Версии CE.
• Функция с табличным значением для нескольких операторов (MSTVF) с фиксированным числом кратности 100 строк и 1 строка: если фиксированная оценка для TVFs из 100 строк не приводит к более эффективному плану, чем с использованием фиксированной оценки для TVFs из 1 строки (соответствующая по умолчанию в модели CE SQL Server 2008 R2 (10.50.x) и более ранних версиях), затем подсказка QUERYTRACEON 9488 запроса используется для создания плана выполнения. Дополнительные сведения о функциях MSTVF см. в разделе Создание определяемых пользователем функций (ядро СУБД).

Примечание.

В крайнем случае, если ограниченные указания не возвращают приемлемые результаты для соответствующих шаблонов запросов, можно принять во внимание полную поддержку CE 70 с использованием указания запроса USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION') для формирования плана выполнения.

Внимание

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

Запуск помощника по настройке запросов для обновлений базы данных

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

Создание сеанса обновления базы данных

1. В SQL Server Management Studio откройте обозреватель объектов и подключитесь к ядро СУБД.

2. Для базы данных, предназначенной для обновления уровня совместимости базы данных, щелкните правой кнопкой мыши имя базы данных, выберите "Задачи", выберите "Обновить базу данных" и выберите "Создать сеанс обновления базы данных".

3. В окне мастера QTA необходимо выполнить два действия для настройки сеанса.

   1. В окне Настройка настройте хранилище запросов для записи эквивалента одного полного бизнес-цикла данных рабочей нагрузки для анализа и настройки.

      • Введите ожидаемую продолжительность рабочей нагрузки в днях (минимальное значение — 1 день). Это значение будет использоваться, чтобы предлагать рекомендуемые параметры хранилища запросов для предварительного разрешения на сбор всех базовых данных. Сбор подходящих базовых данных имеет важное значение для возможности анализа регрессионных запросов, обнаруженных после изменения уровня совместимости базы данных.
      • Задайте намеченный целевой уровень совместимости базы данных, который должен быть у базы данных пользователя после завершения рабочего процесса QTA. После завершения нажмите кнопку Далее.

      

   2. В окне Параметры в двух столбцах отображаются Текущие настройки хранилища запросов в целевой базе данных, а также Рекомендуемые параметры.

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

      

      Внимание

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

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

   

Выполнение рабочего процесса обновления базы данных

1. Для базы данных, предназначенной для обновления уровня совместимости базы данных, щелкните правой кнопкой мыши имя базы данных, выберите "Задачи", выберите "Обновить базу данных" и выберите "Мониторинг сеансов".

2. На станице Управление сеансами указаны текущие и прошлые сеансы для базы данных. Выберите нужный сеанс и выберите "Сведения".

   Примечание.

   Если текущий сеанс отсутствует, нажмите кнопку Обновить.

   Список содержит следующие данные.

   • Идентификатор сеанса
   • Имя сеанса: имя, созданное системой, состоящее из имени базы данных, даты и времени создания сеанса.
   • Состояние — состояние сеанса (активный или закрытый).
   • Описание. Созданная системой система состоит из уровня совместимости целевой базы данных, выбранного пользователем, и количества дней для рабочей нагрузки бизнес-цикла.
   • Время начала — дата и время создания сеанса.

   

   Примечание.

   Кнопка Удалить сеанс служит для удаления всех данных, сохраненных для выбранного сеанса. Однако при удалении закрытого сеанса не удаляются все ранее развернутые структуры планов. При удалении сеанса, имеющего развернутые структуры планов, будет невозможно использовать QTA для отката. Вместо этого найдите структуры планов с помощью системной таблицы sys.plan_guides и удалите их вручную, используя sp_control_plan_guide.

3. Отправной точкой для нового сеанса является шаг Сбор данных.

   Примечание.

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

   Этот шаг состоит из трех этапов.

   1. Сбор базовых данных — пользователю отправляется запрос на запуск репрезентативного цикла рабочей нагрузки, чтобы хранилище данных могло собрать базовые данные. После завершения этой рабочей нагрузки установите флажок Done with workload run (Выполнено с помощью запуска рабочей нагрузки) и нажмите кнопку Далее.

      Примечание.

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

      

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

      

      На следующей странице подтверждается успешное обновление уровня совместимости базы данных.

      

   3. Наблюдаемый сбор данных — запрос на повторный запуск репрезентативного цикла рабочей нагрузки, чтобы хранилище запросов могло собрать сравнительные базовые данные, которые будут использоваться для поиска возможностей оптимизации. При выполнении рабочей нагрузки нажимайте кнопку Обновить, чтобы обновлять список регрессионных запросов (если они будут найдены). Измените значение Queries to show (Число отображаемых запросов), чтобы ограничить число отображаемых запросов. Порядок элементов в списке зависит от значений параметров Метрика ("Продолжительность" или "Время ЦП") и Статистическая обработка ("Среднее" — значение по умолчанию). Кроме того, задайте значение параметра Queries to show (Число отображаемых запросов). После завершения этой рабочей нагрузки установите флажок Done with workload run (Выполнено с помощью запуска рабочей нагрузки) и нажмите кнопку Далее.

      

      Список содержит следующие данные.

      • Идентификатор запроса
      • Текст запроса: инструкция Transact-SQL, которую можно развернуть, нажав кнопку ... .
      • Выполняется: отображает количество выполнения этого запроса для всей коллекции рабочих нагрузок.
      • Базовая метрика — выбранная метрика ("Продолжительность" или "Время ЦП") в мс для сбора базовых данных до обновления уровня совместимости базы данных.
      • Наблюдаемая метрика — выбранная метрика ("Продолжительность" или "Время ЦП") в мс для сбора базовых данных после обновления уровня совместимости базы данных.
      • % Изменения: процент изменений для выбранной метрики между состоянием обновления совместимости базы данных до и после нее. Отрицательное число означает объем измеренной регрессии для запроса.
      • Настраиваемые — значение True или False в зависимости от того, подходит ли запрос для экспериментирования.

4. Просмотр анализа — выбор запросов для экспериментирования и поиска возможностей оптимизации. Значение Queries to show (Число отображаемых запросов) указывает на число подходящих для экспериментов запросов. После выбора нужных запросов нажмите кнопку Далее, чтобы начать экспериментирование.

   Примечание.

   Запросы, для которых параметр "Настраиваемые" имеет значение "False", нельзя выбирать для экспериментирования.

   Внимание

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

   

5. Просмотр результатов — выбор запросов для развертывания предлагаемого решения в качестве структуры плана.

   Список содержит следующие данные.

   • Идентификатор запроса
   • Текст запроса: инструкция Transact-SQL, которую можно развернуть, нажав кнопку ... .
   • Состояние — отображение текущего состояния экспериментирования для запроса.
   • Базовая метрика — выбранная метрика ("Продолжительность" или "Время ЦП") в мс для запроса, как указано на шаге 2, вложенном шаге 3, представляющая регрессионный запрос после обновления уровня совместимости базы данных.
   • Наблюдаемая метрика — выбранная метрика ("Продолжительность" или "Время ЦП") в мс для запроса после экспериментирования для достаточной предложенной оптимизации.
   • % Изменение: процент изменений для выбранной метрики между состоянием до и после экспериментирования, представляющий объем измеряемого улучшения запроса с предлагаемой оптимизацией.
   • Параметр запроса — ссылка на предлагаемое указание, которое улучшает метрику выполнения запроса.
   • Можно развернуть — значение True или False в зависимости от того, можно ли развернуть предлагаемую оптимизацию запроса в виде структуры плана.

   

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

   

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

   

   Примечание.

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

Разрешения

Необходимо быть членом роли db_owner.

См. также

• Уровни совместимости и обновления ядра СУБД
• Средства контроля и настройки производительности
• Мониторинг производительности с использованием хранилища запросов
• Изменение режима совместимости базы данных и использование хранилища запросов
• Флаги трассировки
• Указания запросов USE HINT
• Модуль оценки кратности
• Автоматическая настройка
• Использование помощника по настройке запросов SQL Server