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

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

При переходе с более старой версии 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). Дополнительные сведения об уровнях совместимости и их влиянии на обновления см. в разделе Уровни совместимости и обновления ядра СУБД.

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

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

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

Внимание

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

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

В начальной точке QTA предполагается, что база данных из предыдущей версии SQL Server перемещается (через Attach a Database или RESTORE Statements) на новую версию системы управления базами данных SQL Server, а уровень совместимости базы данных сразу после обновления не изменяется. Направляет по следующим шагам QTA:

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

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

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

Внутреннее пространство поиска QTA Tuning

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

QTA предназначен для известных возможных шаблонов регрессий запросов из-за изменений в версиях оценки кардинальности (SQL Server). Например, при обновлении базы данных с 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.
Простое сдерживание vs. Базовое сдерживание: Если вариант соединения на основе сдерживания предоставляет более точные оценки для конкретного запроса, то подсказка USE HINT ('ASSUME_JOIN_PREDICATE_DEPENDS_ON_FILTERS') к запросу заставляет SQL Server создать план выполнения, используя предположение простого сдерживания вместо предположения базового сдерживания по умолчанию. Дополнительные сведения см. в разделах Указания запроса USE HINT и Версии CE.
Функция с табличным значением для нескольких операторов (MSTVF) с фиксированной оценкой кардинальности на 100 строк против 1 строки: Если фиксированная оценка для TVF в 100 строк не приводит к более эффективному плану, чем фиксированная оценка для TVF в 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 базы данных пользователя, где сеанс был создан в первый раз. Со временем в одной базе данных можно создать несколько сеансов настройки, но для заданной базы данных может существовать только один активный сеанс.

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

В SQL Server Management Studio откройте обозреватель объектов и подключитесь к ядру СУБД.
Для базы данных, предназначенной для обновления уровня совместимости базы данных, щелкните правой кнопкой мыши имя базы данных, выберите "Задачи", выберите "Обновить базу данных" и выберите "Создать сеанс обновления базы данных".
В окне мастера QTA необходимо выполнить два действия для настройки сеанса.
1. В окне Настройка настройте хранилище запросов для записи эквивалента одного полного бизнес-цикла данных рабочей нагрузки для анализа и настройки.
  - Введите ожидаемую продолжительность рабочей нагрузки в днях (минимальное значение — 1 день). Это используется для предложения рекомендуемых параметров хранилища запросов, чтобы временно разрешить сбор всей базовой линии. Сбор подходящих базовых данных имеет важное значение для возможности анализа регрессионных запросов, обнаруженных после изменения уровня совместимости базы данных.
  - Задайте намеченный целевой уровень совместимости базы данных, который должен быть у базы данных пользователя после завершения рабочего процесса QTA.
  После завершения нажмите кнопку Далее.
2. В окне "Параметры " два столбца отображают текущее состояние хранилища запросов в целевой базе данных и рекомендуемые параметры.
  - По умолчанию выбраны рекомендуемые параметры, но если установить переключатель над столбцом текущих параметров, они будут приняты, после чего можно будет точно настроить текущую конфигурацию хранилища запросов.
  - Предлагаемая настройка порога устаревших запросов устанавливается как удвоенное значение ожидаемой длительности рабочей нагрузки в днях. Это связано с тем, что хранилище запросов должно хранить сведения о базовой рабочей нагрузке и рабочей нагрузке после обновления базы данных.
  После завершения нажмите кнопку Далее.
  
  Внимание
  
  Предлагаемое максимальное значение — это произвольное значение, которое может быть подходит для короткой рабочей нагрузки. Однако может оказаться недостаточно для хранения сведений о базовых рабочих нагрузках и нагрузках после обновления базы данных для интенсивных рабочих процессов, особенно в случаях, когда создается множество различных планов. Если вы ожидаете, что это будет так, введите более высокое значение, соответствующее.
В окне Настройка выполняются заключительные шаги конфигурации сеанса и содержатся сведения о дальнейших действиях по открытию сеанса и работе с ним. После завершения нажмите кнопку Готово.

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

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

Примечание.

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

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

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

Примечание.

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

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

Запросы с установленным Tunable в False не могут быть выбраны для экспериментирования.

Внимание

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

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

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

Примечание.

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

Разрешения

Требуется членство в роли db_owner.

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

Была ли эта страница полезна?

Last updated on 2025-03-28