Поделиться через

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

  • Статья

Применимо к: База данных SQL Azure

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

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

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

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

  • блокировки;
  • ВВОД-ВЫВОД
  • Состязание, связанное с tempdb использованием
  • ожидание временно предоставляемого буфера памяти.

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

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

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

Разрешение запросов с помощью неоптимальных планов выполнения запросов

В следующих разделах описывается, как разрешать запросы с неоптимальными планами выполнения.

Запросы с проблемами плана с учетом параметров (PSP)

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

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

Для устранения проблем PSP существует несколько обходных решений. Каждое решение имеет свои компромиссы и недостатки.

  • Новая функция, представленная в SQL Server 2022 (16.x), — оптимизация конфиденциального плана параметров, которая пытается устранить большинство неоптимальных планов запросов, вызванных конфиденциальности параметров. Это включено с уровнем совместимости базы данных 160 в База данных SQL Azure.
  • Использование указания запроса RECOMPILE при каждом выполнении запроса. Этот метод позволяет заменить время компиляции и увеличение загрузки ЦП на улучшение качества плана. Использование параметра RECOMPILE часто невозможно для рабочих нагрузок, требующих высокую пропускную способность.
  • Используйте указание запроса OPTION (OPTIMIZE FOR...), чтобы переопределить фактическое значение параметра с типичным значением параметра, которое создает план, достаточно подходящий для большинства возможностей значения параметров. Для этого варианта требуется хорошее понимание оптимальных значений параметров и связанных с ними характеристик плана.
  • Использование указания запроса OPTION (OPTIMIZE FOR UNKNOWN) для переопределения фактического значения параметра и использования вместо него среднего значения вектора плотности. Это также можно сделать путем записи входящих значений параметров в локальные переменные, а затем использования этих локальных переменных в предикатах вместо самих параметров. Для этого исправления средняя плотность должна быть достаточно высокой.
  • Полное отключение мониторинга параметров путем использования указания запроса DISABLE_PARAMETER_SNIFFING.
  • Использование указания запроса KEEPFIXEDPLAN для предотвращения перекомпиляций в кэше. Этот обходной путь предполагает, что достаточно хороший общий план уже находится в кэше. Можно также отключить автоматические обновления статистики, чтобы снизить вероятность того, что хороший план будет исключен, а вместо него будет скомпилирован новый плохой план.
  • Принудительное применение плана путем явного использования указания запроса USE PLAN, для чего следует переписать запрос и добавить это указание в текст запроса. Можно также задать конкретный план с помощью хранилища запросов или путем включения автоматической настройки.
  • Замена одной процедуры вложенным набором процедур, каждая из которых может использоваться на основе условной логики и связанных значений параметров.
  • Создание альтернатив выполнения динамической строки для статического определения процедуры.

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

Дополнительные сведения об устранении проблем PSP см. в следующих записях блога:

Действие компиляции, вызванное неправильной параметризацией

Когда в запросе есть литералы, то либо ядро ​СУБД автоматически параметризует инструкцию, либо пользователь параметризует инструкцию явно, чтобы уменьшить количество компиляций. Большое количество компиляций запроса, использующих один и тот же шаблон, но разные значения литералов, может привести к высокой загрузке ЦП. Точно так же, если вы только частично параметризуете запрос, который по-прежнему имеет литералы, ядро СУБД не параметризует его дальше.

Ниже приведен пример частично параметризованного запроса.

SELECT *
FROM t1 JOIN t2 ON t1.c1 = t2.c1
WHERE t1.c1 = @p1 AND t2.c2 = '961C3970-0E54-4E8E-82B6-5545BE897F8F';

В этом примере t1.c1 принимает @p1, но t2.c2 продолжает принимать GUID как литерал. В этом случае, если изменить значение для c2, запрос будет считаться другим, и произойдет новая компиляция. Чтобы сократить число компиляций в этом примере, можно также параметризовать и GUID.

Следующий запрос показывает количество запросов по хэшу запроса для определения, правильно ли параметризован запрос:

SELECT TOP 10
  q.query_hash
  , count (distinct p.query_id ) AS number_of_distinct_query_ids
  , min(qt.query_sql_text) AS sampled_query_text
FROM sys.query_store_query_text AS qt
  JOIN sys.query_store_query AS q
     ON qt.query_text_id = q.query_text_id
  JOIN sys.query_store_plan AS p
     ON q.query_id = p.query_id
  JOIN sys.query_store_runtime_stats AS rs
     ON rs.plan_id = p.plan_id
  JOIN sys.query_store_runtime_stats_interval AS rsi
     ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id
WHERE
  rsi.start_time >= DATEADD(hour, -2, GETUTCDATE())
  AND query_parameterization_type_desc IN ('User', 'None')
GROUP BY q.query_hash
ORDER BY count (distinct p.query_id) DESC;

Факторы, влияющие на изменения плана запроса

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

  • изменения в схеме, на которые ссылается запрос;
  • изменения данных в таблицах, на которые ссылается запрос;
  • изменены параметры контекста запроса.

Скомпилированный план может быть удален из кэша по разным причинам, таким как:

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

Если вы используете указание RECOMPILE, план не будет кэшироваться.

Перекомпиляция (или новая компиляция после вытеснения кеша) по-прежнему может привести к созданию плана выполнения запроса, идентичного исходному. Когда план отличается от предыдущего или исходного, возможны следующие объяснения.

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

  • Различия в ресурсах сервера. Когда план в одной системе отличается от плана в другой системе, на создание планов могла повлиять доступность ресурсов, например, количество доступных процессоров. В частности, если в одной системе больше процессоров, может быть выбран параллельный план. Дополнительные сведения о параллелизме в Базе данных SQL Azure см. в разделе Настройка максимальной степени параллелизма (MAXDOP) в Базе данных SQL Azure.

  • Другая статистика. Статистика, связанная с объектами, на которые имеются ссылки, могла измениться или существенно отличаться от статистики исходной системы. Если статистика изменяется и происходит перекомпиляция, оптимизатор запросов использует статистику, начиная с момента ее изменения. Распределение и периодичность данных пересмотренной статистики могут отличаться от таковых в исходной компиляции. Эти изменения используются для создания оценок кратности. (Оценки кратности — это количество строк, которые, как ожидается, будут проходить через логическое дерево запроса.) Изменения в оценках кратности могут привести к выбору других физических операторов и соответственно порядка операций. Даже незначительные изменения в статистике могут привести к изменению плана выполнения запроса.

  • Изменен уровень совместимости базы данных или версия оценщика кратности. Изменения уровня совместимости базы данных могут включать новые стратегии и функции, которые могут привести к изменению плана выполнения запроса. Помимо уровня совместимости базы данных, отключенный или включенный флаг трассировки 4199 или измененное состояние конфигурации QUERY_OPTIMIZER_HOTFIXES на уровне базы данных также могут влиять на выбор плана выполнения запроса во время компиляции. Флаги трассировки 9481 (принудительное использование устаревших оценок кратности) и 2312 (принудительное использование оценок кратности по умолчанию) также влияют на план.

Проблемы ограничений ресурсов

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

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

Проблемы с производительностью, вызванные увеличением объема рабочей нагрузки

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

  • Являются ли запросы из приложения причиной проблемы высокой загрузки ЦП?

  • Для наиболее ресурсоемких запросов, которые вы можете выявить:

    • Связано ли с одним и тем же запросом несколько планов выполнения? Если да, почему?
    • Запросы с одним и тем же планом выполнения имеют одинаковое время выполнения? Увеличилось ли количество выполнений? Если да, то проблемы производительности скорее всего вызвало увеличение рабочей нагрузки.

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

Не всегда легко выявить изменение объема рабочей нагрузки, которое вызывает проблему с ЦП. Учитывайте следующие факторы:

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

  • Появление нового запроса. Приложение может запускать новый набор запросов в разное время.

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

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

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

Способы отображения основных категорий ожидания

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

  • Используйте наблюдатель за базами данных для выявления запросов с снижением производительности из-за увеличения ожиданий.
  • Использование хранилища запросов для получения статистики ожидания для каждого запроса с течением времени. Типы ожидания в хранилище запросов объединены в категории ожидания. Сопоставление категорий ожидания с типами ожидания см. в разделе sys.query_store_wait_stats.
  • Использование sys.dm_db_wait_stats для получения сведений обо всех периодах ожидания потоков, выполнявшихся в ходе запроса. Это агрегированное представление можно использовать для диагностики проблем производительности Базы данных SQL Azure, а также проблем определенных запросов и пакетов. Запросы могут ожидать ресурсы, ожидания очереди или внешние ожидания.
  • Использование sys.dm_os_waiting_tasks для получения сведений об очереди задач, которые ожидают некоторые ресурсы.

В сценариях с высоким уровнем загрузки ЦП хранилище запросов и статистика ожидания могут не отражать загрузку ЦП в следующих случаях.

  • Запросы с высоким потреблением ЦП все еще выполняются.
  • Во время выполнения запросов с высоким потреблением ЦП произошла отработка отказа.

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

Совет

Дополнительные средства

Связанный контент