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

Как понять что проблема именно в SQL Server - Заходим в Диспетчер задач, на вкладке Подробности находим sqlserver и смотрим колонку ЦП.

Если это значение постонно высокое, то значит где-то идет утечка CPU.

В этом руководстве мы собрали различные советы как решать подобную проблему

Поиск проблемных мест в SQL Server по CPU

1. Cмотрим счетчики perfmon

Определяем проблема в Kernel или User запросах.

В perfmon смотрим следующие параметры:

Processor: % Privileged Time – Percentage of time processor spends on execution of Microsoft Windows kernel commands such as OS activity. (If more than 30% involve Windows Admins)
Process (sqlservr): % Privileged Time – the sum of processor time on each processor for all threads of the process (SQL Kernel)
Processor: % User Time – percentage of time the processor spends on executing user processes such as SQL Server. This includes I/O requests from SQL Server

Если это значение % Privileged Time / No of logical cpus больше 30%, то скорее всего дело в системных настройках, возможно антивирус.

Используя данную инструкцию вы можете найти проблемные spID, которые в данный момент загружают процессор - https://www.mssqltips.com/sqlservertip/2454/how-to-find-out-how-much-cpu-a-sql-server-process-is-really-using/

2. Ищем проблемные процессы

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

альтернативный вариант:

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

spID с 1 до 50 - это системные. Мы можем отключать (kill spID) или смотреть запрос только для пользовательских (spID>50).

Также пробуем использовать хранимки exec 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

А также можно убить процесс через kill spID. Убили процесс - и посмотрели как это сказалось на загрузке.

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

Также попробуйте выполнить следующие запросы для поиска проблемных мест по 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

Еще один скрипт для поиска проблемных запросов по CPU:

/*
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

Для найденных элементов можно удалить план в кеше (подставив sql_handle):

DBCC FREEPROCCACHE (plan_handle_id_goes_here)

Еще 1 запрос на поиск проблем по 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

И еще один:

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

Также посмотрите правой кнопкой на Сервере > reports> Standard reports > Top CPU queries.

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

В найденных запросах посмотрите execution plan и посмотрите где наибольший cost.

Дополнительные рекомендации:

проверьте что стоят все необходимые индексы на таблицах, которые участвуют в проблемных запросах.
постарайтесь как можно "быстрее" в where обрезать данные для уменьшения выборки для последующей обработки. Т.е. старайтесь не допускать сканирования по очень большой таблице без использования какой либо фильтрации (либо эта фильтрация содержит затратную логику проверки условий)
проверьте, что у вас нет затратных конвертаций типов
если используете множественно функции, проверьте что они используют минимум извлечений внутри.
Вариант - поставить обновление https://support.microsoft.com/ru-ru/help/3195888/fix-high-cpu-usage-causes-performance-issues-in-sql-server-2016-and-20

    select * from sys. dm_os_spinlock_stats -- провериить что нет больших чисел у  SECURITY_CACHE и CMED_HASH_SET -  если есть  то установить обновления

https://docs.microsoft.com/ru-ru/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-query-plan-transact-sql?view=sql-server-ver15 находить тяжелые процессы и смотреть их планы запросов
Как посмотреть запрос по sql handle или plan handle - https://docs.microsoft.com/ru-ru/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sql-text-transact-sql?view=sql-server-ver15

Источники и что почитать по теме утечек CPU

Альтернативная документация по поиску CPU проблем SQL Server

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

Конфигурация железа сервера.
Дисковая подсистема.
Свободное место.
Антивирус на сервере стоит?
БД (операции) не выполняются / выполняются, как часто.
Проверка целостности
Индексы перестройка
Обновление статистики
Сжатие (шринк)

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

http://sqlcom.ru/optimization_query/sql-server-performance-problems-after-moved-to-new-server/

Электропитания - использовать «Высокая производительность»

настройка кэширования записи на диск

антивирус, - добавить папку SQL Server и файлов БД в исключения

настройка настроены параметры параллелизма (cost threshold for parallelism, max degree of parallelism)

настройка Hyper-Threading.

Мониторинг SSMS - «Стандартные отчеты»

«Стандартные отчеты» в пользовательском интерфейсе Management Studio

SQL Server Management Studio предоставляет минимальный необходимый набор стандартных отчетов для получения информации в режиме пользовательского интерфейса.

Доступ к этим отчетам может быть выполнен через «Обозреватель объектов» (Object explorer) → Правый клик мыши по базе данных → «Отчеты» (Reports) → «Стандартный отчет» (Standard reports)

Перечень «Стандартные отчеты»:

Перечень «Стандартные отчеты»:

Занято место на диске
Использование дисковой памяти верхними таблицами
Использование дисковой памяти таблицей
Использование дисковой памяти секцией
События резервного копирования и восстановления
Все транзакции
Все блокирующие транзакции
Самые продолжительные транзакции
Транзакции, блокирующие наибольшее кол-во транзакций при выполнении
Транзакции с наибольшим кол-вом блокировок
Статистика блокировки ресурсов по объектам
Статистика выполнения объектов
Журнал согласованности баз данных
Статистика использования индекса
Физическая статистика индекса
Журнал изменений схемы
Статистика пользователей
Перечень «Пользовательские отчеты»

Мониторинг Activity Monitor - Монитор активности

Открыть монитор активности CTRL+ALT+A или SSMS стандарт. панель инструментов значок.

Монитор активности SQL Server 2008 объединяет данные о процессах, предоставляя наглядную информацию по выполняющимся и недавно выполнявшимся процессам.

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

Мониторинг Reporting Services - Performance Dashboard Reports

Для наблюдения за SQL Server есть интересный пакет отчетов Reporting Services, называется он SQL Server Performance Dashboard Reports.

http://sqlcom.ru/dba-tools/sql-server-dashboard-reports/

Можноскачать

The SQL Server 2012 Performance Dashboard Reports are Reporting Services report files designed to be used with the Custom Reports feature of SQL Server Management Studio.

Вопрос – используется ли Reporting Services?

Мониторинг (платный)

http://sqlcom.ru/monitor/microsoft-sql-server-monitoring-solution/

(платный) от разных компаний:

Idera — SQL Diagnostic Manager

Red-Gate — SQL Monitor

ApexSQL — ApexSQL Monitor

Quest — Spotlight on SQL Server Enterprise

SentryOne — SQL centry

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

СКРИПТЫ - системные

sp_who и sp_who2

найти блокирующие и ожидающие запросы

1 -- 100_1_sys.dm_exec_query_stats - most time cpu

-- 100_1_sys.dm_exec_query_stats_Which Queries are taking the most time cpu to execute

sys.dm_exec_query_stats OUTER APPLY sys.dm_exec_query_pl

3 - 100_3_sys.dm_exec_query_stats cpu-utilization

100_3_sys.dm_exec_query_stats_sql-server-cpu-utilization-io-usage-and-memory-usage.sql

sys.dm_exec_query_stats CROSS APPLY sys.dm_exec_plan_attributes

Результат имеет вид.

row_num	DatabaseName	CPU_Time(Ms)	CPUPercent
1	master	6355553	88.11
2	АutoParts_shop_v2	357018	4.95
3	testDB_1	255776	3.55
4	tempdb	244863	3.39
5	msdb	142	0

4 - 100_4_sys.dm_exec_query_stats_Тяжелые запросы

-- 100_4_sys.dm_exec_query_stats_Тяжелые запросы

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

5 - 100_5_sys.dm_tran_locks_заблокированные запросы

Быстрый способ найти заблокированные запросы

-- 100_5_sys.dm_tran_locks_заблокированные запросы

-- мы должны убить blocking_session_id.

-- Изучите столбец BlockingText

-- Мы можем убить сессию с помощью KILL 52

6 - 100_6_Какой процессор чем загружен

-- 100_6_Какой процессор чем загружен

7 - 100_7_Информация о пользователях и подключения.sql

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

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

СКРИПТЫ - sp_WhoIsActive

Вызов:

200_2_sp_WhoIsActive_Параметры.sql

Источник:

http://whoisactive.com/

sp_whoisactive is a comprehensive activity monitoring stored procedure that works for all versions of SQL Server from 2005 through 2017.

«Who Is Active» показывает только текущую активность сервера ( кто и что вызывает нагрузку в данный момент времени.). Можно использовать для сбора и последующего анализа данных.

Внутри используется 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

Нужно ставить внешнюю процедуру. Можно ставить в любую БД.

Источник:

https://www.brentozar.com/askbrent/

https://www.brentozar.com/first-aid/

Быстрая проверка вашего SQL Server (sp_Blitz)

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

Вы устанавливаете процедуру
Запускаете когда вам удобно

Обычно процедура выполняется от 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?"
--Sorry, sp_BlitzQueryStore doesn't work on versions of SQL prior to 2016, or Azure Database compatibility < 130.
--sp_BlitzWho --Who’s running what queries right now?

SQL Profiler

SQL Profiler - используйте, где нужно быстро посмотреть, что там за запрос.

SQL Profiler - остается одним из самых используемых инструментов для диагностики работы SQL Server, несмотря на то, что считается устаревшим. Может быть удален в будущих версиях СУБД, является графической надстройкой для SQL Trace . Как и SQL Profiler, SQL Trace считается устаревшим инструментом и может быть удален в будущем. SQL Trace находится в режиме поддержки и не дополняется новым функционалом.

Кол-во счетчиков (событий) = 180.