RunningTasks.sql

SELECT 			
		s.session_id,
		r.request_id,
		DB_NAME(r.database_id) as request_database_name,
		s.login_name,
		r.command,
		w.exec_context_id,
		w.blocking_session_id,
		w.blocking_exec_context_id,
		s.login_time, 
		s.host_name, 
		s.program_name, 
		s.client_interface_name,		
		s.cpu_time AS session_cpu_time,
		r.cpu_time AS request_cpu_time ,
		s.memory_usage, 
		s.total_scheduled_time, 
		s.total_elapsed_time, 
		s.last_request_start_time, 
		s.last_request_end_time, 
		request_start_time = r.start_time,
		s.reads as session_reads, 
		r.reads AS request_reads,
		s.logical_reads AS session_logical_reads , 
		r.logical_reads as request_logical_reads ,
		s.writes as session_writes,
		r.writes AS request_writes ,
        r.wait_type AS request_wait_type , 
        r.wait_time AS request_wait_time , 
		w.wait_type AS waiting_tasks_wait_type , 
		w.wait_duration_ms AS waiting_tasks_wait_duration,        
			SUBSTRING(qt.text,r.statement_start_offset/2,  
            (case when r.statement_end_offset = -1  
            then len(convert(nvarchar(max), qt.text)) * 2  
            else r.statement_end_offset end -r.statement_start_offset)/2) as request_query_text, 
		qt.text,
		CAST(qp.query_plan as XML) AS query_plan,		
		r.sql_handle AS request_sql_handle , 
        r.plan_handle AS request_plan_handle,
		w.resource_description,
		t.transaction_id,
		t.name, 
		t.transaction_begin_time, 
		t.transaction_type, 
		t.transaction_state,
		t.database_transaction_log_record_count,
		t.database_transaction_log_bytes_used,
		t.database_transaction_log_bytes_reserved,
		t.database_id,
		t.database_transaction_state, 
		t.enlist_count, 
		t.is_user_transaction, 
		t.transaction_descriptor,
		(SELECT 	
					lock.resource_type AS resource_type, 
					lock.resource_subtype AS resource_subtype, 
					LTRIM(RTRIM(lock.resource_description)) AS resource_description,
					lock.resource_database_id AS resource_database_id,
					lock.resource_associated_entity_id AS resource_database_entity_id, 
					lock.resource_lock_partition AS resource_lock_partition,
					lock.request_mode AS request_mode, 
					lock.request_type AS request_type, 
					lock.request_status AS request_status,
					lock.request_exec_context_id as request_context_id, 
					DB_NAME(lock.resource_database_id) AS resource_database_name
					FROM sys.dm_tran_locks lock 
						WHERE lock.request_session_id = r.session_id
						AND lock.request_exec_context_id = w.exec_context_id
				FOR XML AUTO, TYPE, ROOT('locks'))
				AS locks,
		tu.user_objects_alloc_page_count, 
		tu.user_objects_dealloc_page_count, 
		tu.internal_objects_alloc_page_count, 
		tu.internal_objects_dealloc_page_count	
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_requests r 
	ON s.session_id = r.session_id
LEFT JOIN sys.dm_os_waiting_tasks w
	ON s.session_id = w.session_id
LEFT JOIN (
			SELECT 
				DISTINCT
				at.transaction_id,
				at.name, 
				at.transaction_begin_time, 
				at.transaction_type, 
				at.transaction_state,
				dt.database_transaction_log_record_count,
				dt.database_transaction_log_bytes_used,
				dt.database_transaction_log_bytes_reserved,
				dt.database_id,
				dt.database_transaction_state, 
				st.enlist_count, 
				st.is_user_transaction, 
				st.transaction_descriptor
			FROM sys.dm_tran_active_transactions at
				JOIN sys.dm_tran_database_transactions dt
					ON at.transaction_id = dt.transaction_id
				LEFT JOIN sys.dm_tran_session_transactions st
					ON st.transaction_id = at.transaction_id
	) t
	ON t.transaction_id = r.transaction_id
LEFT JOIN sys.dm_db_task_space_usage  tu
	ON tu.exec_context_id = w.exec_context_id
	AND tu.session_id = s.session_id
OUTER APPLY sys.dm_exec_sql_text(r.sql_handle) as qt 
OUTER APPLY sys.dm_exec_query_plan(r.plan_handle) AS qp
WHERE r.session_id > 50 AND r.session_id != @@SPID
	--and program_name like 'Microsoft %'