CPU Usage by SPID Over Time

Who is burning CPU right now? This query snapshots the cumulative CPU time of every active request, waits 30 seconds, takes a second snapshot, and reports the delta per SPID - joined to sysprocesses for session details, the executing object name, and the full SQL text. Only sessions that actually consumed CPU in the window are shown.

SELECT
	session_id AS SPID,
	Sum(cast(cpu_time as bigint)) AS CPU
INTO
	#OldCPU
FROM
	sys.dm_exec_requests
GROUP BY
	session_id

WAITFOR DELAY '00:00:30'

SELECT
	session_id AS SPID,
	Sum(cast(cpu_time as bigint)) AS CPU
INTO
	#NewCPU
FROM
	sys.dm_exec_requests
GROUP BY
	session_id

SELECT
	IsNull(Old.SPID, New.SPID) AS SPID,
	(New.CPU - IsNull(Old.CPU, 0)) AS CPU,
	SysP.*,
	Obj.[name] as ObjectName,
	[tSQL].[text] as [SQLText]
FROM
	#NewCPU AS New
LEFT OUTER JOIN #OldCPU AS Old
	ON Old.SPID = New.SPID
INNER JOIN sys.sysprocesses as SysP
	ON SysP.spid = IsNull(Old.SPID, New.SPID)
CROSS APPLY sys.dm_exec_sql_text(SysP.sql_handle) as [tSQL]
LEFT OUTER JOIN sys.objects as Obj
	ON Obj.object_id = [tSQL].objectid
WHERE
	(New.CPU - IsNull(Old.CPU, 0)) > 0
ORDER BY
	(New.CPU - IsNull(Old.CPU, 0)) DESC

DROP TABLE #NewCPU
DROP TABLE #OldCPU