Запрос для поиска проблемных SQL по процессору

Как быстро найти «пожирателя» CPU в SQL Server (мой рабочий скрипт)

Бывает, сидишь такой, сервер тормозит, пользователи жалуются. А кто виноват? Начинаем искать.

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

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

Шаг 1: Ищем самого активного

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

-- Сохраняем состояние сессий для сравнения
if object_id('tempdb..#tmp') is NULL
BEGIN
    SELECT * into #tmp from sys.dm_exec_sessions s
    PRINT 'Ждем секунду для накопления статистики при первом запуске'
    WAITFOR DELAY '00:00:01';
END

-- Удаляем старые данные, если они есть
if object_id('tempdb..#tmp1') is not null drop table #tmp1

declare @d datetime
declare @dd float
select @d = crdate from tempdb.dbo.sysobjects where id=object_id('tempdb..#tmp')

-- Делаем новый снимок
select * into #tmp1 from sys.dm_exec_sessions s
select @dd=datediff(ms,@d,getdate())
select @dd AS [интервал времени, мс]

-- Выводим топ-30 самых активных сессий по CPU
SELECT TOP 30 s.session_id, s.host_name, db_name(s.database_id) as db, s.login_name,s.login_time,s.program_name,
       s.cpu_time-isnull(t.cpu_time,0) as cpu_Diff, 
       convert(numeric(16,2),(s.cpu_time-isnull(t.cpu_time,0))/@dd*1000) as cpu_sec,
       s.reads+s.writes-isnull(t.reads,0)-isnull(t.writes,0) as totIO_Diff, 
       convert(numeric(16,2),(s.reads+s.writes-isnull(t.reads,0)-isnull(t.writes,0))/@dd*1000) as totIO_sec,
       s.reads-isnull(t.reads,0) as reads_Diff, 
       convert(numeric(16,2),(s.reads-isnull(t.reads,0))/@dd*1000) as reads_sec,
       s.writes-isnull(t.writes,0) as writes_Diff, 
       convert(numeric(16,2),(s.writes-isnull(t.writes,0))/@dd*1000) as writes_sec,
       s.logical_reads-isnull(t.logical_reads,0) as logical_reads_Diff, 
       convert(numeric(16,2),(s.logical_reads-isnull(t.logical_reads,0))/@dd*1000) as logical_reads_sec,
       s.memory_usage, s.memory_usage-isnull(t.memory_usage,0) as [mem_D],
      s.nt_user_name,s.nt_domain
from #tmp1 s
LEFT join #tmp t on s.session_id=t.session_id
order BY
cpu_Diff desc  -- <-- Сортируем по CPU. Можешь поменять на totIO_Diff desc или logical_reads_Diff desc, если ищешь другой "жор"

-- Чистим за собой
drop table #tmp
GO
select * into #tmp from #tmp1
drop table #tmp1

Пример из практики: Недавно так нашел сессию, которая жрала 80% CPU. Оказалось, какой-то отчет без индексов гонял. Заменили сортировку cpu_Diff desc на logical_reads_Diff desc — и нашли еще парочку проблемных запросов, которые создавали тонны логических чтений.

Шаг 2: Смотрим, что именно он делает

Нашли номер сессии (session_id) — например, 182. Теперь смотрим, какой запрос он выполняет и какой у него план.

DECLARE @sql_handle varbinary(64)
DECLARE @plan_handle varbinary(64)
DECLARE @sid INT = 182  -- <-- Вставь сюда номер сессии из первого шага
Declare @statement_start_offset int, @statement_end_offset INT, @session_id SMALLINT

-- Получаем хендлы для запроса и плана
IF @sid IS NOT NULL
SELECT @sql_handle=der.sql_handle, @plan_handle=der.plan_handle, 
       @statement_start_offset=der.statement_start_offset, 
       @statement_end_offset=der.statement_end_offset, 
       @session_id = der.session_id
FROM sys.dm_exec_requests der WHERE der.session_id=@sid

-- Печатаем текст запроса
DECLARE @txt VARCHAR(max)
IF @sql_handle IS NOT NULL
SELECT @txt=[text] FROM sys.dm_exec_sql_text(@sql_handle)
PRINT @txt

-- Смотрим план всего батча или процедуры
IF @plan_handle IS NOT NULL
select * from sys.dm_exec_query_plan(@plan_handle)

-- А это — план конкретного оператора внутри батча
IF @plan_handle IS NOT NULL
SELECT dbid, objectid, number, encrypted, CAST(query_plan AS XML) AS planxml
from sys.dm_exec_text_query_plan(@plan_handle, @statement_start_offset, @statement_end_offset)

Совет: Когда увидишь план — ищи в нём Table Scan или Key Lookup. Это как красные флажки. Обычно именно они сжирают ресурсы. Добавь индекс — и сервер вздохнет с облегчением.

Вот так, два скрипта — и ты уже не гадаешь, а точно знаешь, кто и где тормозит.