Проверка утилизации процессоров запросами за 45 секунд: от симптомов к первопричине

Автор: Luca Biondi, Check Top CPU Queries in 45 Seconds. From Symptoms to Root Clause. The "45 Seconds DBA Series" – What Real DBAs Check First | Part 7

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

В двух словах

• ✔️ Время работы (Worker Time) против затраченного времени (Elapsed Time): Высокое время работы относительно продолжительности выполнения указывает на запрос, утилизирующий ЦП, или на высокий параллелизам. 
• ✔️ Анализ кэша планов (Plan Cache Mining): Используйте sys.dm_exec_query_stats, чтобы найти суммарное потребление ЦП с момента последнего перезапуска. 
• ✔️ Сосредоточьтесь на количестве выполнений: Запрос, который выполняется 1 миллион раз, потребляя по 10 мс, часто опаснее, чем запрос, выполняющийся один раз 10 секунд. 
• ✔️ Анализ первопричины: Чрезмерные сортировки, хэширование и скалярные пользовательские функции (Scalar UDF) — обычные подозреваемые в скачках ЦП. 

В моей предыдущей статье мы говорили о различии между загруженным ЦП и тем, у которого накапливаются задачи в очереди. Но после того как вы подтвердили, что ваши планировщики действительно задыхаются, вам нужно найти «виновника».  Тут я не верю в гадания. Нам нужно идти прямо в кэш планов.

Если ваши запросы написаны плохо — используют не поисковые предикаты (non-sargable predicates) или построчную логику — у SQL Server нет выбора, кроме как сжигать такты ЦП. Давайте найдём истину за 45 секунд.

Что это на самом деле: расшифровка времени работы (Worker Time)

В терминологии SQL Server Время работы (Worker Time) — это фактическое время, которое ЦП потратил на выполнение запроса. Затраченное время (Elapsed Time) — это общее время, которое ждал пользователь.

💣 Проблема: Когда total_worker_time значительно превышает total_elapsed_time, это явный признак высокой параллелизации (несколько ядер работают над одним запросом). Хотя параллелизация может быть полезной, чрезмерная параллелизация на маленьких запросах создаёт для планировщиков ЦП сценарий «смерти от тысячи порезов».

• 🔍 sys.dm_exec_query_stats: Динамическое административное представление, отслеживающее показатели производительности для каждого кэшированного плана.
• 🔍 Контекст выполнения: Определение, исходит ли нагрузка на ЦП от нескольких больших пакетных операций или от миллионов маленьких неэффективных вызовов.

🧪 Скрипт для поиска самых нагружающих ЦП запросов за 45 секунд

Используйте этот скрипт, чтобы получить топ-10 запросов, которые в данный момент пожирают ваш ЦП. Он учитывает общее время работы по всем выполнениям:

SELECT TOP 10
    SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
    ((CASE statement_end_offset 
        WHEN -1 THEN DATALENGTH(st.text) 
        ELSE qs.statement_end_offset END 
            - qs.statement_start_offset)/2) + 1) AS [Текст запроса],
    qs.execution_count,
    [Среднее время ЦП] = qs.total_worker_time / qs.execution_count,
    [Общее время ЦП] = qs.total_worker_time,
    qp.query_plan
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
ORDER BY qs.total_worker_time DESC;

Согласно документации Microsoft, total_worker_time измеряется в микросекундах, что даёт нам точность, необходимую для поиска даже самых маленьких неэффективностей.

🚀 Моя работающая стратегия

Перестаньте добавлять ядра ЦП только для того, чтобы они были съедены скалярными пользовательскими функциями (Scalar User Defined Functions, UDF).

1. Выявление UDF: Если вы видите скалярную UDF среди ваших главных запросов по потреблению ЦП, она, скорее всего, принудительно вызывает построчную обработку (RBAR — Row-By-Agonizing-Row). Преобразуйте их во встроенные табличные функции (Inline Table-Valued Functions, iTVF).

2. Проверка на чрезмерный параллелизм: Если Общее время ЦП >> Затраченное время, обратите внимание на настройку Cost Threshold for Parallelism (порог стоимости для параллелизма). Если она всё ещё равна значению по умолчанию (5), немедленно измените её на 50.

3. «План» — это ключ: Нажмите на ссылку query_plan в результатах выше. Ищите операторы Hash Match (хэш-соответствие) или Sort (сортировка). Они интенсивно потребляют ЦП. Замена Hash Match на Nested Loop (вложенный цикл) через лучшее индексирование может снизить использование ЦП на 80%.