SQL Server сильно грузит процессор. Проблемы CPU у процесса SQLServer. Оптимизация запросов SQL по CPU

Диагностика и устранение высокой загрузки CPU в SQL Server

Первичная диагностика: откройте Диспетчер задач, перейдите на вкладку «Подробности», найдите процесс sqlservr.exe и проверьте значение в колонке «ЦП». Если этот показатель стабильно высокий, это указывает на проблему с утечкой CPU.

В этом руководстве собраны проверенные методы для выявления и устранения причин высокой загрузки процессора SQL Server.

1. Анализ счетчиков PerfMon

Определите, вызвана ли проблема системными (Kernel) или пользовательскими (User) запросами. В оснастке PerfMon отслеживайте следующие счетчики:

• Processor: % Privileged Time – процент времени, затраченного процессором на выполнение команд ядра Windows (например, активность ОС). Значение более 30% может указывать на проблемы, связанные с администрированием Windows.
• Process (sqlservr): % Privileged Time – суммарное процессорное время на всех ядрах для всех потоков процесса SQL Server (ядро SQL).
• Processor: % User Time – процент времени, затраченного на выполнение пользовательских процессов, включая запросы SQL Server и связанные с ними операции ввода-вывода.

Если значение (% Privileged Time / Количество логических процессоров) превышает 30%, проблема, скорее всего, в системных настройках, например, в работе антивируса.

Для поиска конкретных проблемных spID, загружающих процессор, используйте эту инструкцию.

2. Поиск проблемных процессов

Выполните запрос для поиска системных процессов, потребляющих ресурсы:

SELECT * FROM sys.sysprocesses
WHERE cmd like 'LAZY WRITER' or cmd like '%Ghost%' or cmd like 'RESOURCE MONITOR'

Альтернативный вариант для поиска по потреблению CPU:

SELECT top 20 spid, kpid, dbid, cpu, memusage FROM sysprocesses
order by cpu desc

Обратите внимание: spID от 1 до 50 — системные. Для работы с пользовательскими процессами используйте фильтр spID > 50. Вы можете завершить проблемный процесс командой KILL spID и сразу оценить влияние на загрузку CPU.

Для получения информации о текущем состоянии всех процессов используйте хранимые процедуры: sp_who, sp_who1, sp_who2, sp_who3.

Чтобы найти текст запроса по spID:

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 78
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

Также можно просмотреть последний выполняющийся запрос для spID:

DBCC INPUTBUFFER(60)
GO
SELECT @@SPID  -- получить SPID текущего процесса
GO

3. Выявление проблем через специализированные запросы

Используйте следующие запросы для поиска проблемных мест по CPU.

Запрос 1: Детальная информация о выполняющихся запросах с сортировкой по времени CPU.

SELECT GETDATE() AS "RunTime", st.text AS batch, SUBSTRING(st.text,statement_start_offset / 2+1
,((CASE WHEN a.statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),st.text)) * 2)
ELSE a.statement_end_offset END)  - a.statement_start_offset) / 2+1)  AS current_statement
, qp.query_plan, a.*
FROM sys.dm_exec_requests a CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(a.plan_handle) AS qp
ORDER BY CPU_time DESC

Запрос 2: Группировка процессов по выполняемому коду для выявления наиболее ресурсоемких групп.

/*
Disclaimer: I am not sure for the origin of this script/query.
This query is used in our team to identify and resolve high CPU issue

*/
--define the temptables that will hold intermediary results
IF OBJECT_ID('tempdb..#dbcc') IS NOT NULL
    DROP TABLE #dbcc

create table #dbcc(c1 varchar(15), c2 int, c3 varchar(255),spid int default 0)

IF OBJECT_ID('tempdb..#cpugroups') IS NOT NULL
    DROP TABLE #cpugroups

create table #cpugroups (sql_handle binary(20), sql_text nvarchar(50),total_cpu bigint,total_io bigint,total_sessions int, total_threads int)


--take the SPID groups that are running same code (NOT statement)
insert into #cpugroups
select top 10 sql_handle,substring((select text from fn_get_sql(sql_handle)),1,50), SUM(CPU) TotalCPUForGroup, SUM(physical_io) TotalIOForGroup, COUNT(distinct spid) TotalNoOfSessions,COUNT(*) TotalNoOfThreads
from master..sysprocesses (nolock)
where spid>50 and status<>'sleeping'
and sql_handle<>0x0 and spid<>@@spid
group by sql_handle
order by TotalCPUForGroup desc


declare @sql nvarchar(max)
declare @t table (spid int)

INSERT INTO @t
SELECT DISTINCT spid FROM master..sysprocesses WHERE spid>50 and sql_handle in (select sql_handle from #cpugroups)


declare @spid int


WHILE EXISTS(select * from @t)
BEGIN
  select top 1 @spid=spid from @t
  set @sql='dbcc inputbuffer('+LTRIM(STR(@spid))+')'

  --try to retrieve the original command for all SPIDs
  BEGIN TRY
    INSERT INTO #dbcc(c1, c2, c3)
    EXEC (@sql)

    update #dbcc
    set spid=@spid
    where spid=0

  END TRY
  BEGIN CATCH
  END CATCH


  delete from @t where spid=@spid

END


select * from #cpugroups
select c3 [sql_text], count(*) NoOfSessionsRunning from #dbcc group by c3 order by 2 desc
select * from #dbcc

Для очистки плана конкретного запроса из кэша используйте его plan_handle:

DBCC FREEPROCCACHE (plan_handle_id_goes_here)

Запрос 3: Поиск запросов с наибольшим временем CPU (с планом выполнения).

SELECT
	r.session_id
	,st.TEXT AS batch_text
	,SUBSTRING(st.TEXT, statement_start_offset / 2 + 1, (
			(
				CASE
					WHEN r.statement_end_offset = - 1
						THEN (LEN(CONVERT(NVARCHAR(max), st.TEXT)) * 2)
					ELSE r.statement_end_offset
					END
				) - r.statement_start_offset
			) / 2 + 1) AS statement_text
	,qp.query_plan AS 'XML Plan'
	,r.*
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
CROSS APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
ORDER BY cpu_time DESC

Запрос 4: Комплексный анализ сессий с блокировками и ожиданиями.

SELECT s.session_id,
r.status,
r.blocking_session_id 'Blk by',
r.wait_type,
wait_resource,
r.wait_time / (1000 * 60) 'Wait M',
r.cpu_time,
r.logical_reads,
r.reads,
r.writes,
r.total_elapsed_time / (1000 * 60) 'Elaps M',
Substring(st.TEXT,(r.statement_start_offset / 2) + 1,
((CASE r.statement_end_offset
WHEN -1
THEN Datalength(st.TEXT)
ELSE r.statement_end_offset
END - r.statement_start_offset) / 2) + 1) AS statement_text,
Coalesce(Quotename(Db_name(st.dbid)) + N'.' + Quotename(Object_schema_name(st.objectid, st.dbid)) + N'.' +
Quotename(Object_name(st.objectid, st.dbid)), '') AS command_text,
r.command,
s.login_name,
s.host_name,
s.program_name,
s.last_request_end_time,
s.login_time,
r.open_transaction_count
FROM sys.dm_exec_sessions AS s JOIN sys.dm_exec_requests AS r
ON r.session_id = s.session_id CROSS APPLY sys.Dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != @@SPID
ORDER BY r.cpu_time desc

Также воспользуйтесь встроенным отчетом: кликните правой кнопкой мыши на сервере в SSMS → Reports → Standard Reports → «Top CPU queries».

4. Анализ найденных проблемных запросов

Для каждого найденного запроса изучите его план выполнения (execution plan) и определите операции с наибольшей стоимостью (cost).

Рекомендации по оптимизации:

• Проверьте наличие и актуальность индексов для таблиц, участвующих в проблемных запросах.
• Максимально сужайте выборку данных на ранних этапах выполнения запроса (в секции WHERE). Избегайте сканирования больших таблиц без эффективной фильтрации.
• Проверьте наличие неявных или затратных преобразований типов данных.
• Если используются функции, убедитесь, что они минимизируют количество операций внутри себя.
• Рассмотрите установку актуальных обновлений для SQL Server, например, исправление для высокой загрузки CPU в SQL Server 2016 и 2017.

    select * from sys. dm_os_spinlock_stats -- проверьте, нет ли аномально высоких значений у счетчиков SECURITY_CACHE и CMED_HASH_SET. Если есть — установите обновления.

• Используйте sys.dm_exec_query_plan для просмотра планов тяжелых запросов.
• Для получения текста запроса по sql_handle или plan_handle используйте sys.dm_exec_sql_text.

Полезные источники по теме утечек CPU

• Microsoft Support: High CPU use occurs in your queries on SQL Server
• SQL Authority: How to Fix High CPU Consumption on SQL Server 2017 and 2016
• C# Corner: Difference between sp_who, sp_who2
• Microsoft Archive: SQL High CPU Troubleshooting Checklist (очень подробная статья)
• MSSQLTips: How to find out how much CPU a SQL Server process is really using
• YouTube: Troubleshooting High CPU in SQL Server

Альтернативный чек-лист для диагностики проблем CPU

Что проверить в первую очередь:

• Конфигурация аппаратного обеспечения сервера.
• Состояние дисковой подсистемы (скорость, задержки, свободное место).
• Наличие и настройки антивируса (добавьте папки SQL Server и файлы БД в исключения).
• Активность базы данных: какие операции выполняются и с какой частотой.
• Выполнение проверки целостности БД (DBCC CHECKDB).
• Перестройка индексов и обновление статистики.
• Операции сжатия (shrink) данных.

Общие системные рекомендации

Статья о производительности SQL Server после миграции

• Электропитание: Установите схему «Высокая производительность».
• Дисковая подсистема: Настройте кэширование записи на диск (Write-Back Cache).
• Параллелизм: Проверьте и настройте параметры cost threshold for parallelism и max degree of parallelism.
• Hyper-Threading: Оцените влияние и при необходимости скорректируйте настройки.

Мониторинг через SSMS: Стандартные отчеты

В SQL Server Management Studio доступен набор стандартных отчетов для быстрой диагностики. Доступ: «Обозреватель объектов» → Правый клик по базе данных → «Отчеты» → «Стандартный отчет».

Наиболее полезные отчеты для диагностики CPU:

• Все транзакции
• Все блокирующие транзакции
• Самые продолжительные транзакции
• Транзакции, блокирующие наибольшее количество транзакций
• Транзакции с наибольшим количеством блокировок
• Статистика блокировки ресурсов по объектам
• Статистика выполнения объектов
• Статистика использования индекса
• Физическая статистика индекса

Мониторинг через Activity Monitor (Монитор активности)

Откройте через CTRL+ALT+A или через стандартную панель инструментов SSMS. Предоставляет обзорную информацию по процессам, ожиданиям, вводу-выводу и последним ресурсоемким запросам, аналогично Диспетчеру задач Windows.

Мониторинг через Performance Dashboard Reports

Пакет отчетов SQL Server Performance Dashboard Reports для Reporting Services. Скачать и ознакомиться. Отчеты предназначены для использования с функцией Custom Reports в SSMS.

Платные решения для мониторинга

Обзор платных решений

• Idera — SQL Diagnostic Manager
• Red-Gate — SQL Monitor
• ApexSQL — ApexSQL Monitor
• Quest — Spotlight on SQL Server Enterprise
• SentryOne — SQL Sentry

Также возможен мониторинг SQL Server через Zabbix.

Полезные системные скрипты и процедуры

sp_who и sp_who2

Для поиска блокирующих и ожидающих запросов.

1. sys.dm_exec_query_stats (наибольшее время CPU)

Запрос для определения запросов, потребляющих больше всего процессорного времени.

2. sys.dm_exec_query_stats (утилизация CPU, IO, памяти)

Пример результата:

3. sys.dm_exec_query_stats (тяжелые запросы)

Скрипт на основе представления sys.dm_exec_query_stats.

4. sys.dm_tran_locks (заблокированные запросы)

Быстрый способ найти заблокированные запросы. Для разблокировки используйте KILL blocking_session_id.

5. Нагрузка на процессор

Запрос для определения, какой процессор и чем загружен.

6. Информация о пользователях и подключениях

Основан на представлениях: sys.dm_exec_connections, master.sys.sysprocesses, sys.dm_exec_sessions.

Скрипт: sp_WhoIsActive

Комплексная хранимая процедура для мониторинга текущей активности сервера. Источник: whoisactive.com. Показывает, кто и что вызывает нагрузку в данный момент. Подходит для сбора и последующего анализа данных. Использует 15 DMV.

Пример вызова с ключевыми параметрами:

                               @show_sleeping_spids = 2,  -- Показать спящие сессии

                               @show_system_spids = 1, -- Показать системные сессии

                               @show_own_spid = 1  --  Показать вашу собственную сессию

                               , @get_full_inner_text = 1 -- видеть всю активность,

                               , @get_outer_command = 1 -- что вызвало этот [sql_text]

                               , @get_task_info = 1 -- Вывести в столбец [wait_info] не только самое важное ожидание, но и все остальные:

                               , @get_transaction_info = 1 --9. Можно так же убрать агрегацию транзакций одной сессии и вывести их по отдельности:

                               , @get_additional_info = 1 --10. Вывести более детальную информацию. Будет добавлен столбец [additional_info] с информацией в формате XML

                               -- , @find_block_leaders = 1 -- количество заблокированных процессов каждой сессией:

                               , @sort_order = '[CPU] DESC' -- сортировку вывода

Скрипт: sp_Blitz

Внешняя процедура для быстрой проверки состояния сервера. Устанавливается в любую БД. Источники: Brent Ozar: Ask Brent, Brent Ozar: First Aid.

Как работает:

1. Установите процедуру.
2. Запустите в любое удобное время (выполняется 5-15 секунд, минимально влияя на других пользователей).

Варианты использования:

--dbo.sp_Blitz -- общие параметры
--sp_BlitzFirst - "Why is my SQL Server slow right now?"
--sp_BlitzCache - "What are the most resource-intensive queries on this server?"
--sp_BlitzIndex - "How could I tune indexes to make this database faster?"
--sp_BlitzQueryStore - "How has this query performed over time?" (для SQL Server 2016+)
--sp_BlitzWho --"Who's running what queries right now?"

SQL Profiler и Extended Events

SQL Profiler — быстрый способ просмотра выполняемых запросов. Несмотря на то, что считается устаревшим, остается популярным инструментом. Является графической надстройкой для SQL Trace (также устаревшего). SQL Trace находится в режиме поддержки и может быть удален в будущем.

Extended Events — современная замена SQL Profiler. В SSMS находятся в разделе «Управление» → «Расширенные события» → «Сеансы».