Кто грузит MS SQL Server в текущий момент?

Очень простой и крайне полезный скрипт. Отвечает на простой вопрос- кто грузит сервер, кто жрет ресурсы CPU и IO. Подобная информация есть в sys.sysprocesses, но некоторые процессы подключились достаточно давно, работают долго, данные накапливаются. А кто именно сейчас загрузил сервер? 

–Иногда возникает вопрос, кто грузит сервер. Эта нехитрая процедура фотографирует состояние sys.sysprocesses
–после минутной задержки вычитает начальное состояние, сортирует в порядке убывания дисковой активности
–то же самое с CPU
–20151020 Dmitry Gorchilin DP.UA
–20151021 add ecid
–20151026 add kpid,UID, total information
–20151026 add dbcc INPUTBUFFER for view sql text
–20170414 Исправлена ошибка с физической памятью, DMV изменилась после 2008 версии. Сейчас корректно работает на всех версиях

DECLARE @SQL NVARCHAR(4000),@mem_mb BIGINT

if (CHARINDEX(‘2005’,@@VERSION)>0)OR(CHARINDEX(‘2008′,@@VERSION)>0)
SET @SQL =’SELECT @mem_mb=physical_memory_in_bytes/1048576 FROM sys.dm_os_sys_info’
ELSE
SET @SQL =’SELECT @mem_mb=physical_memory_kb/1024 FROM sys.dm_os_sys_info’

EXEC sp_executeSQL @SQL,N’@mem_mb BIGINT OUTPUT’,@mem_mb=@mem_mb OUTPUT;

select * into #sysprocesses from sys.sysprocesses
waitfor delay ’00:01:00′

;with [SumProc] as (
select sum(sys.sysprocesses.physical_io-#sysprocesses.physical_io) as deffIO
FROM sys.sysprocesses,#sysprocesses
where sys.sysprocesses.loginame=#sysprocesses.loginame
and sys.sysprocesses.spid=#sysprocesses.spid
and sys.sysprocesses.login_time=#sysprocesses.login_time
and sys.sysprocesses.ecid=#sysprocesses.ecid
and sys.sysprocesses.kpid=#sysprocesses.kpid
and sys.sysprocesses.UID=#sysprocesses.UID)
select convert(numeric(7,2),100.*(sys.sysprocesses.physical_io-#sysprocesses.physical_io)/SumProc.deffIO) as ‘%OfIO’,
sys.sysprocesses.loginame,
sys.sysprocesses.lastwaittype,
sys.sysprocesses.physical_io-#sysprocesses.physical_io as IOdeff,
‘dbcc INPUTBUFFER(‘+convert(varchar(10),sys.sysprocesses.spid)+’)–‘+
convert(varchar(10),convert(numeric(7,2),100.*(sys.sysprocesses.physical_io-#sysprocesses.physical_io)/SumProc.deffIO))+
‘% Login=’+sys.sysprocesses.loginame,
*
FROM sys.sysprocesses,#sysprocesses,SumProc
where sys.sysprocesses.loginame=#sysprocesses.loginame
and sys.sysprocesses.spid=#sysprocesses.spid
and sys.sysprocesses.login_time=#sysprocesses.login_time
and sys.sysprocesses.ecid=#sysprocesses.ecid
and sys.sysprocesses.kpid=#sysprocesses.kpid
and sys.sysprocesses.UID=#sysprocesses.UID
and 100.*(sys.sysprocesses.physical_io-#sysprocesses.physical_io)/SumProc.deffIO>=0.5
order by sys.sysprocesses.physical_io-#sysprocesses.physical_io DESC

;with [SumProc] as (
select sum(sys.sysprocesses.cpu-#sysprocesses.cpu) as deffCPU
FROM sys.sysprocesses,#sysprocesses
where sys.sysprocesses.loginame=#sysprocesses.loginame
and sys.sysprocesses.spid=#sysprocesses.spid
and sys.sysprocesses.login_time=#sysprocesses.login_time
and sys.sysprocesses.ecid=#sysprocesses.ecid
and sys.sysprocesses.kpid=#sysprocesses.kpid
and sys.sysprocesses.UID=#sysprocesses.UID)
select convert(numeric(7,2),100.*(sys.sysprocesses.cpu-#sysprocesses.cpu)/SumProc.deffCPU) as ‘%OfCPU’,
sys.sysprocesses.loginame,
sys.sysprocesses.lastwaittype,
sys.sysprocesses.cpu-#sysprocesses.cpu as CPUdeff,
‘dbcc INPUTBUFFER(‘+convert(varchar(10),sys.sysprocesses.spid)+’)–‘+
convert(varchar(10),convert(numeric(7,2),100.*(sys.sysprocesses.cpu-#sysprocesses.cpu)/SumProc.deffCPU))+
‘% Login=’+sys.sysprocesses.loginame,
*
FROM sys.sysprocesses,#sysprocesses,SumProc
where sys.sysprocesses.loginame=#sysprocesses.loginame
and sys.sysprocesses.spid=#sysprocesses.spid
and sys.sysprocesses.login_time=#sysprocesses.login_time
and sys.sysprocesses.ecid=#sysprocesses.ecid
and sys.sysprocesses.kpid=#sysprocesses.kpid
and sys.sysprocesses.UID=#sysprocesses.UID
and 100.*(sys.sysprocesses.cpu-#sysprocesses.cpu)/SumProc.deffCPU>=0.5
order by sys.sysprocesses.cpu-#sysprocesses.cpu DESC

–text
select s.session_id, s.login_time, s.login_name
, s.host_name, s.program_name, s.last_request_end_time
, r.start_time, r.command, r.open_transaction_count
, 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
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
order by s.last_request_end_time;

select ‘Теоретически полностью загруженных процессоров:’,(sum(sys.sysprocesses.cpu-#sysprocesses.cpu))/(1000*60)
FROM sys.sysprocesses,#sysprocesses
where sys.sysprocesses.loginame=#sysprocesses.loginame
and sys.sysprocesses.spid=#sysprocesses.spid
and sys.sysprocesses.login_time=#sysprocesses.login_time
and sys.sysprocesses.ecid=#sysprocesses.ecid
and sys.sysprocesses.kpid=#sysprocesses.kpid
and sys.sysprocesses.UID=#sysprocesses.UID
and sys.sysprocesses.cpu-#sysprocesses.cpu>0
union
select ‘Процесссов с НЕнулевым расходом CPU:’,count(*)
FROM sys.sysprocesses,#sysprocesses
where sys.sysprocesses.loginame=#sysprocesses.loginame
and sys.sysprocesses.spid=#sysprocesses.spid
and sys.sysprocesses.login_time=#sysprocesses.login_time
and sys.sysprocesses.ecid=#sysprocesses.ecid
and sys.sysprocesses.kpid=#sysprocesses.kpid
and sys.sysprocesses.UID=#sysprocesses.UID
and sys.sysprocesses.cpu-#sysprocesses.cpu>0
union
select ‘Процесссов с нулевым расходом CPU (=0):’,count(*)
FROM sys.sysprocesses,#sysprocesses
where sys.sysprocesses.loginame=#sysprocesses.loginame
and sys.sysprocesses.spid=#sysprocesses.spid
and sys.sysprocesses.login_time=#sysprocesses.login_time
and sys.sysprocesses.ecid=#sysprocesses.ecid
and sys.sysprocesses.kpid=#sysprocesses.kpid
and sys.sysprocesses.UID=#sysprocesses.UID
and sys.sysprocesses.cpu-#sysprocesses.cpu=0
union
select ‘Процесссов всего:’,count(*)
FROM sys.sysprocesses
union
SELECT ‘Logical CPU Count: ‘,cpu_count FROM sys.dm_os_sys_info
union
SELECT ‘Hyperthread_Ratio:’,hyperthread_ratio FROM sys.dm_os_sys_info
union
SELECT ‘Physical CPU Count:’,cpu_count/hyperthread_ratio FROM sys.dm_os_sys_info
union
SELECT ‘Physical Memory MB:’,@mem_mb

drop table #sysprocesses

Скрипт в виде текстового документа, обновлено