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

Technorati Tags: ,,

Hey Guys,

This is a bit off-topic, and specific to SQL Server, but this script has been very useful to me when the server is running slow.  Run the first part up to the 2nd 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
Advertisements

About scottedwards2000

data guy
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s