Get TOP CPU or I/O SPID

Scenario:┬áHigh CPU or I/O utilization through task manager or resource monitor by the SQL Server process and you need to know what session is causing it and what it’s executing.

Solution: Run this nifty piece of T-SQL and choose the order by.


-- Get top CPU / I/O spid
-- @niftysql / niftysql.wordpress.com

DECLARE @t1 TABLE (
spid int
,cpu bigint
,physical_io bigint
);

INSERT INTO @t1 (spid,cpu,physical_io)
(
SELECT
spid
,cpu
,physical_io
FROM sys.sysprocesses
);

WAITFOR DELAY '00:00:10'; -- Found this to be a good start.

SELECT
p.spid
,p.cpu
,p.physical_io
,p.cpu - t.cpu as cpu_diff
,p.physical_io - t.physical_io as physical_io_diff
,p.*
,text
FROM sys.sysprocesses p
CROSS apply sys.dm_exec_sql_text (sql_handle)
JOIN @t1 t ON p.spid = t.spid
WHERE 1=1
AND p.cpu - t.cpu > 10
--AND p.physical_io - t.physical_io > 10 --uncomment this if you're interested in I/O.
ORDER BY cpu_diff DESC;
--ORDER BY physical_io_diff; -- Great for seeing which spid is hammering the disk.

Advertisements

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