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

Применимо к:SQL Server Управляемый экземпляр SQL Azure

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

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

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

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

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

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

• Определите отсутствующие индексы с помощью отсутствующих индексов в динамических административных представлениях и планах выполнения запросов. В этой статье показано, как обнаружить и настроить некластеризованные индексы с помощью запросов отсутствующих индексов.
• Попробуйте обновить статистику или перестроить индексы, чтобы получить лучший план. Включите автоматическое исправление плана базы данных для автоматического устранения этих проблем.
• В качестве дополнительного шага по устранению неполадок используйте указания хранилища запросов, чтобы применить указания запросов с помощью хранилища запросов без внесения изменений в код.
• Попробуйте изменить уровень совместимости базы данных и реализовать интеллектуальную обработку запросов. Оптимизатор SQL-запросов может создать другой план запроса в зависимости от уровня совместимости базы данных. Более высокие уровни совместимости обеспечивают больше возможностей для интеллектуальной обработки запросов.

Пример настройки и указания запросов в статье "Настройка приложений и баз данных для производительности" показывает влияние неоптимального плана запроса из-за параметризованного запроса, определения этого условия и использования указания запроса для разрешения.

• Дополнительные сведения об обработке запросов см. в разделе Руководство по архитектуре обработки запросов.
• Сведения об изменении уровней совместимости базы данных и о различиях между уровнями совместимости см. в разделе ALTER DATABASE.
• Дополнительные сведения об оценке кратности см. в разделе Оценка кратности.

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

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

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

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

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

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

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

• I smell a parameter (Кажется, здесь есть параметр!)
• Conor vs. dynamic SQL vs. procedures vs. plan quality for parameterized queries (Конор против динамического SQL, против процедур, против качества плана для параметризованных запросов)

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

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

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, план не будет кэшироваться.

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

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

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

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

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

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

• Динамическое административное представление sys.dm_db_resource_stats возвращает сведения о потреблении ресурсов ЦП, памяти и операциях ввода-вывода для базы данных. Каждая строка представляет очередной 15-секундный интервал, даже если в это время в базе данных не выполнялись никакие действия. Исторические данные хранятся в течение одного часа.
• Sys.server_resource_stats DMV возвращает данные об использовании ЦП и хранилище для Управляемый экземпляр SQL Azure. Данные собираются и агрегируются в 15 секундах.
• Множество отдельных запросов, которые в совокупности потребляют много ресурсов ЦП

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

• Проблемы tempdb

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

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

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

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

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

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

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

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

• Настройка параметра конфигурации сервера max degree of parallelism
• Общие сведения о проблемах, связанных с блокировкой SQL Server, и их устранении
• Мониторинг производительности Microsoft Управляемый экземпляр SQL Azure с помощью динамических административных представлений
• Настройка некластеризованных индексов с предложениями отсутствующих индексов
• sys.server_resource_stats (Управляемый экземпляр SQL Azure)
• Общие сведения об ограничениях ресурсов Управляемого экземпляра SQL Azure