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

  • Статья

Область применения:База данных SQL Azure

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

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

Workload states

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

  • блокировки;
  • ВВОД-ВЫВОД
  • Состязание, связанное с 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 процентов на длительный период времени. Сама по себе загрузка ЦП не означает, что изменился объем рабочей нагрузки. Регрессии в плане выполнения запроса и изменения в распределении данных также могут способствовать большему использованию ресурсов, даже если приложение выполняет ту же рабочую нагрузку.

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

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

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

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

  • Блокировка.

    Один запрос может удерживать блокировку объектов в базе данных, когда другие запросы пытаются получить доступ к тем же объектам. Вы можете определить блокирующие запросы с помощью динамических административных представлений или Intelligent Insights. Дополнительные сведения см. в статье "Общие сведения о блокировке и устранении проблем с базой данных SQL Azure".

  • Проблемы ввода-вывода.

    Запросы могут ожидать записи страниц в файлы данных или журналов. В этом случае проверьте статистику ожидания INSTANCE_LOG_RATE_GOVERNOR, WRITE_LOG или PAGEIOLATCH_* в динамическом административном представлении. См. описание использования динамических административных представлений для выявления проблем производительности ввода-вывода.

  • Проблемы tempdb

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

  • Проблемы, связанные с памятью.

    Если рабочей нагрузке не хватает памяти, ожидаемое время существования страницы может упасть, или запросы могут получить меньше памяти, чем им нужно. В некоторых случаях встроенные средства искусственного интеллекта в оптимизаторе запросов могут устранить проблемы, связанные с памятью. См. сведения об использовании динамических административных представлений для выявления проблем с временно предоставляемым буфером памяти. Дополнительные сведения и примеры запросов см. в Устранение ошибок нехватки памяти в базе данных Azure SQL. При возникновении ошибок, связанных с нехваткой памяти, см. сведения в статье о sys.dm_os_out_of_memory_events.

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

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

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

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

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

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

Совет

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

Дальнейшие действия