SQL: find loading processes

In case your SQL load is very high and you would like to understand what processes is loading its CPU,Memory,I/O, use the following queries:

--------------------------- CPU --------------------------------
select cpu, spid into cpu_usage from sysprocesses
WAITFOR DELAY '00:00:05'
select diff = p.cpu-u.cpu, p.cpu, p.spid, p.program_name, p.loginame, p.hostname, p.last_batch
from sysprocesses p join cpu_usage u on p.spid=u.spid
order by 1 desc
drop table cpu_usage
--------------------------- MEMORY -----------------------------
select memusage, spid into MEM_usagefrom sysprocesses
WAITFOR DELAY '00:00:05'
select diff = p.memusage - u.memusage , p.memusage, p.spid, p.program_name, p.loginame, p.hostname, p.last_batch
from sysprocesses p join MEM_usage u on p.spid=u.spid
order by 1 desc
drop table MEM_usage
---------------------------- I/O -------------------------------
select physical_io, spid into IO_usagefrom sysprocesses
WAITFOR DELAY '00:00:05'
select diff = p.physical_io - u.physical_io , p.physical_io, p.spid, p.program_name, p.loginame, p.hostname, p.last_batch
from sysprocesses p join IO_usage u on p.spid=u.spid
order by 1 desc
drop table IO_usage

Share on Google Plus

About Vittorio Pavesi