Оценивание производительности запросов с использованием планов выполнения и динамических административных представлений

Когда запрос выполняется медленнее, чем ожидалось, первый шаг заключается в том, как ядро СУБД выполняет его. Планы выполнения показывают точные операторы, методы доступа к данным и затраты на ресурсы, выбранные оптимизатором для запроса. Динамические административные представления (DMV) дополняют это, предоставляя данные о производительности во время выполнения для всех запросов в базе данных, чтобы вы могли найти самые ресурсоемкие из них, прежде чем переходить к конкретному плану.

Чтение планов выполнения

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

Существует два типа планов выполнения:

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

Чтобы отобразить предполагаемый план, выполните SET SHOWPLAN_XML ON перед запросом или выберите Показать предполагаемый план выполнения в SQL Server Management Studio (SSMS). Чтобы записать фактический план, выполните SET STATISTICS XML ON или выберите "Включить фактический план выполнения " в SSMS перед выполнением запроса.

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

Определение распространенных проблем в планах выполнения

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

Типы операторов сообщают, как модуль обращается к данным. Существует множество типов операторов, каждый из которых представляет другой метод извлечения или обработки данных. Например, оператор Index Seek представляет эффективный метод, предназначенный для определенных строк с помощью ключей индекса. С другой стороны, оператор сканирования таблиц или индексирования представляет менее эффективный метод, который считывает каждую строку. Если вы видите сканирование на большой таблице, скорее всего, вам потребуется индекс. Например, если приложение электронной коммерции запрашивает заказы по дате и план показывает кластерное сканирование индекса Orders в таблице, добавление некластеризованного индекса на столбец OrderDate может изменить это сканирование на поиск. Обратите внимание, что не все сканы плохи. Если таблица небольшая или условие поиска возвращает большинство строк в таблице, проверка может быть наиболее эффективным методом доступа. Всегда учитывайте контекст запроса и размер данных. Изучите свои данные и используйте планы выполнения, чтобы убедиться, является ли метод доступа обоснованным.

Оценочные и фактические счетчики строк показывают, соответствуют ли предположения оптимизатора реальности. Оптимизатор основывает свой план на статистике, метаданные, описывающие распределение и плотность данных в таблицах. Если эти статистические данные являются устаревшими, предполагаемые и фактические числа строк расходятся. Когда оптимизатор недооценивает количество строк, он может выбрать вложенное соединение (которое обрабатывает одну строку за раз из внутренней таблицы соединения) вместо хеш-соединения (которое создает хеш-таблицу в памяти для быстрого поиска), хотя оно было бы быстрее, или выделить слишком мало памяти для операции сортировки. Статистика может стать устаревшей после значительных изменений данных, поэтому обновление статистики с помощью UPDATE STATISTICS или включение автоматического обновления статистики может помочь оптимизатору принимать лучшие решения.

Операторы поиска ключей отображаются, когда подсистема находит строки через некластеризованный индекс, но требует дополнительных столбцов из кластеризованного индекса. Для каждой соответствующей строки подсистема выполняет дополнительную круговую поездку к кластеризованному индексу, чтобы получить эти столбцы. Если фильтр возвращает много строк, эти дополнительные поиски накапливаются очень быстро. Например, если приложение электронной коммерции фильтрует заказы по CustomerID, но также выбирает OrderDate, TotalAmount и ShippingAddress, а некластеризованный индекс по CustomerID не включает эти столбцы, план показывает поиск ключей для каждого соответствующего заказа. Вы можете исключить поиск ключей, добавив отсутствующие столбцы в качестве включенных столбцов в индекс. Имейте в виду, что включенные столбцы увеличивают размер индекса, что может замедлить запись, поэтому взвесите преимущество производительности чтения по отношению к затратам на запись.

Толстые стрелки между операторами представляют количество строк, поступающих между ними. Неожиданно толстая стрелка в начале плана (читаем слева направо и сверху вниз) часто означает, что отсутствие фильтра или индекса позволяет пропускать слишком много строк.

Отсутствующие предложения индекса отображаются как зеленый выделенный текст в верхней части графического плана выполнения в SSMS. Когда оптимизатор обнаруживает, что индекс может значительно снизить затраты на запрос, он предоставляет рекомендацию непосредственно в плане. Щелкните правой кнопкой мыши по предложению и выберите «Отсутствующие сведения об индексе», чтобы создать CREATE INDEX инструкцию, которую можно просмотреть и запустить. Эти рекомендации — один из самых простых способов достижения успеха, изучая план выполнения.

Предупреждения отображаются как желтый треугольник с восклицательным знаком (⚠) на затронутом операторе. Каждое предупреждение указывает на возможность оптимизации. Распространенные предупреждения:

• Недостающая статистика: оптимизатор не смог найти статистику для столбца, поэтому он сделал предположение на основании оценок количеств строк вместо использования фактического распределения данных. Чтобы устранить эту проблему, создайте статистику по столбцам, используемым в запросах, или обновите существующую статистику, если они устарели.
• Чрезмерное предоставление памяти: запрос запрашивал больше памяти, чем он необходим, тратя ресурсы, которые могут использовать другие запросы. Эта проблема часто возникает, когда оптимизатор переоценит количество строк. Обновление статистики или перезаписи запроса для фильтрации строк ранее может помочь уменьшить объем памяти.
• Нет предиката соединения: две таблицы объединяются без правильного условия, создавая декартовский продукт, который возвращает каждое возможное сочетание строк. Проверьте ваш запрос на отсутствие или неправильную ON клаузу.
• Неявное преобразование: несоответствие типа данных заставляет подсистему преобразовывать значения во время выполнения, что может превратить поиск индекса в сканирование. Например, если WHERE предложение сравнивает nvarchar параметр с varchar столбцом, подсистема преобразует каждую строку в столбце в nvarchar перед сравнением. Чтобы исправить неявные преобразования, сопоставляйте типы данных в параметрах запроса с определениями столбцов.
• Сортировка или хэш-выгрузка: операция сортировки или хэширования превысила выделенную память и сбросила промежуточные результаты в tempdb. Эти операции являются второй по распространенности причиной высокой загрузки процессора после сканирования. Если вы видите предупреждение о разливе, оптимизатор, скорее всего, недооценивает количество строк и запрашивает слишком мало памяти. Запуск UPDATE STATISTICS для обновления статистики таблицы или перезаписи запроса, чтобы уменьшить количество строк, прежде чем сортировка может часто исключить утечку.

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

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

Динамические управляющие представления предоставляют данные о производительности как в реальном времени, так и накопленные из ядра СУБД. Для выполнения запросов к базе данных SQL Azure требуется VIEW DATABASE STATE разрешение. Хотя планы выполнения показывают, как выполняется один запрос, динамические административные представления показывают, что происходит во всех запросах, что помогает сначала найти наиболее ресурсоемкие.

Поиск самых дорогих запросов

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

sys.dm_exec_query_stats возвращает статистическую статистику производительности для кэшированных планов запросов. Соедините это с sys.dm_exec_sql_text чтобы просмотреть текст запроса и sys.dm_exec_query_plan получить план выполнения.

Следующий запрос находит первые 10 запросов по среднему времени ЦП:

SELECT TOP 10
    qs.total_worker_time / qs.execution_count AS avg_cpu_time,
    qs.execution_count,
    qs.total_logical_reads / qs.execution_count AS avg_logical_reads,
    SUBSTRING(st.text, (qs.statement_start_offset / 2) + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN DATALENGTH(st.text)
            ELSE qs.statement_end_offset
        END - qs.statement_start_offset) / 2) + 1) AS query_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY avg_cpu_time DESC;

Этот скрипт помогает определить, какие запросы заслуживают вашего внимания. Высокий avg_logical_reads относительно размера результирующих наборов часто указывает на отсутствие индексов или неэффективных планов. Однако будьте осторожны при интерпретации этих результатов. Запрос с высоким средним временем ЦП, который выполняется только один раз в день, может иметь значение менее умеренного запроса, выполняющего тысячи раз в час. Всегда учитывайте как среднюю стоимость, так и число выполнения при определении приоритета. Вы также можете упорядочить avg_logical_reads по запросам с высокой нагрузкой на операции ввода-вывода, что часто указывает на отсутствие индексов или неэффективные методы доступа.

Проверка текущих выполняемых запросов

Хотя предыдущий запрос показывает самые дорогие исторические запросы в кэше планов запросов, sys.dm_exec_requests предоставляет моментальное состояние каждого запроса, выполняемого в настоящее время. Он содержит столбцы для времени ЦП, операций чтения, записи, типа ожидания, времени ожидания и блокировки идентификатора сеанса. Это представление позволяет обнаружить активные запросы, которые используют слишком много ресурсов или зависают в ожидании блокировок. Это динамическое представление (DMV) является одним из наиболее важных для мониторинга производительности и диагностики проблем в реальном времени.

SELECT
    r.session_id,
    r.status,
    r.command,
    r.wait_type,
    r.wait_time,
    r.blocking_session_id,
    r.cpu_time,
    r.logical_reads,
    t.text AS query_text
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
WHERE r.session_id > 50
ORDER BY r.cpu_time DESC;

Этот запрос фильтрует системные сеансы (идентификаторы сеансов 1–50) и заказы по времени ЦП. Кроме того, вы можете сортировать по logical_reads, чтобы найти запросы с большим количеством операций ввода-вывода. Столбцы wait_type и wait_time помогают определить, ожидает ли запрос блокировок, ввода-вывода или других ресурсов.

Обнаружение отсутствующих индексов

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

SELECT
    mid.statement AS table_name,
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.avg_total_user_cost * migs.avg_user_impact *
        (migs.user_seeks + migs.user_scans) AS improvement_measure
FROM sys.dm_db_missing_index_groups AS mig
INNER JOIN sys.dm_db_missing_index_group_stats AS migs
    ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
    ON mig.index_handle = mid.index_handle
ORDER BY improvement_measure DESC;

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

Мониторинг активных сеансов и задач ожидания

sys.dm_exec_sessions предоставляет информацию обо всех сеансах, прошедших проверку подлинности, включая время входа, имя узла, имя программы, суммарное использование ЦП и считывания. Объедините его с sys.dm_os_waiting_tasks тем, чтобы узнать, какие задачи ждут и какие ресурсы они ожидают. Эти представления данных становятся важными при диагностике блокировки и конфликтов ресурсов в более поздних модулях.

Соберите всё вместе

Планы выполнения и динамические административные представления дают полное понимание поведения запросов. Начните с ДАП (динамических административных представлений), чтобы определить самые ресурсоёмкие запросы. Затем детализируйте свои планы выполнения, чтобы понять , почему они дороги. Потерян ли индекс, вызывающий сканирование? Устаревшая статистика, приводяющая к ошибкам оценки строк? Поиск ключей, который можно устранить? Этот систематический подход, начиная с системного представления и анализа отдельных запросов, является наиболее эффективным способом поиска и устранения узких мест производительности.

Основные выводы

Планы выполнения показывают стратегию оптимизатора для запроса, а фактические планы включают метрики среды выполнения, которые предоставляют несоответствия между предполагаемыми и фактическими числами строк. При чтении плана сосредоточьтесь на типах операторов (поиск и сканирование), оценках количества строк, предупреждениях и операторах поиска по ключу. Динамические административные представления предоставляют системные данные о производительности: используются sys.dm_exec_query_stats для поиска самых дорогих запросов, sys.dm_exec_requests для текущих запросов и отсутствующих динамических представлений индексов для возможностей оптимизации. Начните с динамических административных представлений, чтобы определить, где находятся самые большие проблемы, а затем детализировать отдельные планы выполнения, чтобы понять, почему.