script to quickly find out which spid is using most CPU and/or IO and what that SPID is doing

Technorati Tags: SQL Server,CPU,DBA

Hey Guys,

Thought this might be useful when the server is running slow.  Run the first part up to the 2^nd dashed line all together and then use the identified SPID(s) in the queries below the line to see what it is doing:

------identify spid with highest cpu and io usage-----
SELECT spid, sum(cpu)as cpu
into #temp1
FROM master.dbo.sysprocesses
group by spid
WAITFOR DELAY '0:0:0.3';
SELECT spid, sum(cpu)as cpu
into #temp2
FROM master.dbo.sysprocesses
group by spid

select t3.spid, t4.cpu - t3.cpu diff
from #temp1 t3 inner join #temp2 t4 on t3.spid = t4.spid
order by diff desc

SELECT spid, sum(physical_io)as physical_io
into #temp3
FROM master.dbo.sysprocesses
group by spid
WAITFOR DELAY '0:0:0.3';
SELECT spid, sum(physical_io)as physical_io
into #temp4
FROM master.dbo.sysprocesses
group by spid

select t3.spid, t4.physical_io - t3.physical_io diff
from #temp3 t3 inner join #temp4 t4 on t3.spid = t4.spid
order by diff desc

drop table #temp1
drop table #temp2
drop table #temp3
drop table #temp4
-------------------------------------------------------------------------------------
--NOW, to see what the process is ACTUALLY DOING:
--same as Activity Monitor (use from ANY db)
select * from master..sysprocesses where spid=73

--same as 'details' from Activity Monitor
DBCC inputbuffer(73) --from any db

--interesting - similar to above, but with variables, if used, instead of actual values (e.g. @strDate)
--*PLUS* this shows you the CURRENT procedure running, not just the wrapper procedure like above
--SQL2000:
DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM master..sysprocesses WHERE spid = 73
SELECT * FROM ::fn_get_sql(@Handle) --seems to cut off text at some point
--SQL2005 (doesn't always return same as sql2000 format)
SELECT session_id, text
FROM sys.dm_exec_requests AS r
     CROSS APPLY
     sys.dm_exec_sql_text(sql_handle) AS s
WHERE session_id = 73