Running Processes

A query that lists all active processes on the server with their current SQL statement, waits and resource usage.

Published Dec 15, 2007

A live view of everything the server is doing: this query pulls from sysprocesses (cross-applying dm_exec_sql_text for the actual SQL) and reports every active, non-sleeping session - duration, database, command, program, host, login, last wait type, blocked status, CPU, physical I/O and the statement text itself. A go-to first stop when something feels slow.

select
	datediff(n, SysProcess.last_batch, getdate()) as [Duration],
	SysProcess.spid as SPID,
	LTrim(RTrim([Database].name)) as [Database],
	LTrim(RTrim(SysProcess.cmd)) as [Command],
	LTrim(RTrim(SysProcess.[program_name])) as [Program],
	LTrim(RTrim(SysProcess.hostname)) as [Hostname],
	LTrim(RTrim(SysProcess.loginame)) as [Login],
	LTrim(RTrim(SysProcess.lastwaittype)) as [Last Wait],
	SysProcess.blocked as [Is Blocked],
	SysProcess.cpu as [CPU],
	SysProcess.physical_io as [Physical IO],
	SQLText.[text] as [SQL Statement]
from
	sys.sysprocesses as SysProcess
cross apply sys.dm_exec_sql_text(SysProcess.[sql_handle]) as SQLText
left outer join sys.databases as [Database]
	on [Database].database_id = SysProcess.dbid
where
	[status] not in ('background', 'sleeping')
	and SysProcess.spid <> @@SPID
order by
	last_batch