Database File Usage by Process

Who is eating tempdb? This query (run against tempdb) reports each session's current space usage - GB used, allocated and deallocated for both user and internal objects - joined to sysprocesses for the login, command, host and CPU. Sorted by usage, it quickly identifies the sessions responsible for tempdb pressure.

use tempdb
GO

select
	SP.spid AS [ProcessId],
	FirstSP.loginame AS [User Name],
	MAX(FirstSP.cmd) AS [Command],
	MAX(FirstSP.hostname) AS [Host Name],
	--SP.[status] AS [Status],
	MAX(SP.login_time) AS [Login Time],
	MAX(SP.last_batch) AS [Last Batch],
	SUM(Cast(SP.cpu as Decimal(32))) AS [CPU],
	SUM(SP.physical_io) AS [Physical IO],
	SUM(Convert(Decimal(16, 2), ((
		((SU.user_objects_alloc_page_count + SU.internal_objects_alloc_page_count)
		- (SU.user_objects_dealloc_page_count + SU.internal_objects_dealloc_page_count))
	 * 8192) / 1024.0/1024.0/1024.0))) AS [GB Used],
	SUM(Convert(Decimal(16, 2), ((SU.user_objects_alloc_page_count + SU.internal_objects_alloc_page_count) * 8192) / 1024.0/1024.0/1024.0)) AS [GB Allocated],
	SUM(Convert(Decimal(16, 2), ((SU.user_objects_dealloc_page_count + SU.internal_objects_dealloc_page_count) * 8192) / 1024.0/1024.0/1024.0)) AS [GB Deallocated]
from
	sys.dm_db_task_space_usage AS SU
inner join master..sysprocesses AS SP
	ON SP.spid = SU.session_id
	AND SP.ecid = SU.exec_context_id
inner join master..sysprocesses AS FirstSP
	ON FirstSP.spid = SU.session_id
	AND FirstSP.ecid = 0
where
	(SU.user_objects_alloc_page_count + SU.internal_objects_alloc_page_count) > 0
group by
	SP.spid,
	FirstSP.loginame
order by
	[GB Used] desc