[DMV] Active sessions, Sleeping and Background

-- output information for active sessions, sleeping AND background

-- the output is similar to what you get FROM sys.sysprocesses

SELECT

             es.session_id

             , case when es.status IN ('sleeping', 'background') then 'Sleep/Bkgnd' else 'Active' end AS SessStatus

             , db_name(er.database_id) AS DBName

             , es.login_name

             , er.blocking_session_id

             , er.wait_time

             , (case

                           when er.wait_time = 0 then ''

                           when er.wait_time <> 0 then er.last_wait_type

             end) AS WaitOn

             , es.cpu_time + er.cpu_time AS TotalCPU

             , es.cpu_time AS SessCPU

             , (es.reads + es.writes) AS SessAggIO

             , es.memory_usage

             , er.cpu_time AS ReqCPU

             , (er.reads + er.writes) AS ReqAggIO

             , er.open_transaction_count

             , er.status

             , es.host_name

             , es.program_name

             , er.commAND

             , (SELECT SUBSTRING(text, statement_start_offset/2, (case when statement_end_offset = -1 then len(convert(nvarchar(max), text)) * 2 else statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS QueryText

FROM

             sys.dm_exec_sessions AS es JOIN

             sys.dm_exec_requests AS er on es.session_id = er.session_id

WHERE

             es.is_user_process = 1

ORDER by

             es.session_id

 

-- info for sleeping sessions that do NOT have associated requests

SELECT

             es.session_id

             , es.login_name

             , es.cpu_time AS SessCPU

             , (es.reads + es.writes) AS SessAggIO

             , es.memory_usage

             , es.host_name

             , es.program_name

FROM

             sys.dm_exec_sessions AS es

WHERE

             lower(es.status) = 'sleeping'

             AND es.session_id NOT IN (SELECT session_id FROM sys.dm_exec_requests)

             AND es.is_user_process = 1

ORDER by

             es.session_id

go