-- Queries/stored procedures to assess the server state

use master
go

if object_id('dbo.sp_session_list','P') is not null
	drop procedure dbo.sp_session_list
go

create procedure dbo.sp_session_list
-- output similar to sysprocesses/sp_who 
as
begin
set nocount on
	-- 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
end
go

--if object_id('dbo.sp_session_list','P') is not null
--	drop procedure dbo.sp_session_list
--go
