SELECT sp.session_id
, DB_NAME(database_id) AS DBName
, HOST_NAME AS HostName
, program_name AS AppName
, login_name AS LoginName
, status
, cpu_time
, total_elapsed_time AS ElapsedTime
, (memory_usage * 8) AS MemoryUsage
, (user_objects_alloc_page_count * 8) AS AllocatedUserObjectsInKB
, (user_objects_dealloc_page_count * 8) AS DeallocatedUserObjectsInKB
, (internal_objects_alloc_page_count * 8) AS InternalObjectAllocObjInKB
, (internal_objects_dealloc_page_count * 8) AS InternalObjectDeallocInKB
,CASE is_user_process WHEN 1 THEN 'user session'
WHEN 0 THEN 'system session'
END SessionType
, row_count
FROM sys.dm_db_session_space_usage sp
INNER join sys.dm_exec_sessions s
ON s.session_id = sp.session_id
7 People are following this question.