Script to check whats hogging the system

--Script: SQLPS
--Author: Dave L 2003
--quick script to find out whats hogging the system
--currently has a 10 second delay but this can be changed

--retrives the current state of play from the sysprocesses table
select spid, sum(cpu) as totcpu,sum(physical_io) as totphys 
into #samp1
from master..sysprocesses
group by spid

--wait for 10 seconds
waitfor delay '00:00:10'

--take a second sample from the sysprocesses table
select spid, sum(cpu) as totcpu,sum(physical_io) as totphys 
into #samp2
from master..sysprocesses
group by spid

--subtract 1 from the other and find out what's going on
select distinct s1.spid, 
case
when (s2.totcpu - s1.totcpu) < 0 then 0 
else (s2.totcpu - s1.totcpu)/10
end as cpusec,
case
when (s2.totphys-s1.totphys) < 0 then 0
else (s2.totphys-s1.totphys)/10
end as iosec, 
rtrim(loginame) login, rtrim(program_name) program, rtrim(hostname) hostname
from #samp1 s1, #samp2 s2, master..sysprocesses sp
where s1.spid = s2.spid
and s1.spid = sp.spid
and (s2.totcpu-s1.totcpu>1
or s2.totphys-s1.totphys>1)
order by 2 desc

--tidy up a bit
drop table #samp1
drop table #samp2