Check current SQL Server activity

fnRunningQueries.sql

-- Usage:
--   SELECT * FROM fnRunningQueries
--   SELECT * FROM fnRunningQueries(2) ORDER BY totalCpu DESC
--   SELECT GETDATE() DateTime, * INTO QueryHistory FROM fnRunningQueries(0)
--   INSERT INTO QueryHistory SELECT GETDATE() DateTime, * FROM fnRunningQueries(0)
--   SELECT * FROM QueryHistory ORDER BY DateTime DESC


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO

IF OBJECT_ID('[dbo].[fnRunningQueries]') IS NOT NULL
BEGIN
	DROP FUNCTION [dbo].[fnRunningQueries]
END

GO

CREATE FUNCTION [dbo].[fnRunningQueries]
(
	@include TINYINT
	-- 2 = all,
	-- 1 = all except 'sleeping',
	-- 0 = all except 'sleeping' and 'background'
)
RETURNS TABLE
AS
RETURN
SELECT
	x.session_id spid,
	COALESCE(x.blocking_session_id, 0) AS blockingSpid,
	x.Status status,
	x.Start_time startTime,
	x.totalElapsedTime elapsedTime,
	x.TotalCPU totalCpu,
	x.totalReads reads,
	x.totalWrites writes,
	x.Writes_in_tempdb writesInTempdb,
	( 
		SELECT substring(text,x.statement_start_offset/2,
			case when x.statement_end_offset - x.statement_start_offset < 0 then 0 else (x.statement_end_offset - x.statement_start_offset)/2 end
			)
		FROM sys.dm_exec_sql_text(x.sql_handle)
		FOR XML PATH(''), TYPE 
	) AS subText,
	( 
		SELECT text
		FROM sys.dm_exec_sql_text(x.sql_handle)
		FOR XML PATH(''), TYPE 
	) AS fullText,
	db_name(x.database_id) as dbName,
	( 
		SELECT object_name(objectid) FROM sys.dm_exec_sql_text(x.sql_handle)
	) AS objectName,
	x.Wait_type waitType,
	x.Wait_time waitTime,
	x.Wait_resource waitResource,
	x.Last_wait_type lastWaitType,
	x.Login_name loginName,
	x.Host_name hostName, 
	x.program_name programName,
	x.host_process_id processId,
	( 
		SELECT p.text
		FROM 
		( 
			SELECT MIN(sql_handle) AS sql_handle
			FROM sys.dm_exec_requests r2
			WHERE r2.session_id = x.blocking_session_id
		) AS r_blocking
		CROSS APPLY 
		( 
			SELECT substring(text, x.statement_start_offset/2,
				case when x.statement_end_offset - x.statement_start_offset < 0 then 0 else (x.statement_end_offset - x.statement_start_offset)/2 end
				)
			FROM sys.dm_exec_sql_text(r_blocking.sql_handle)
			FOR XML PATH(''), TYPE 
		) p (text)
	) AS blockingText
FROM 
( 
	SELECT
		r.session_id,
		s.host_name,
		s.program_name,
		s.host_process_id,
		s.login_name,
		r.start_time,
		r.sql_handle,
		r.database_id,
		r.blocking_session_id,
		r.wait_type,
		r.wait_time,
		r.wait_resource,
		r.last_wait_type,
		r.status,
		r.statement_start_offset,
		r.statement_end_offset,
		SUM(CONVERT(BIGINT, r.total_elapsed_time)) as totalElapsedTime,
		SUM(CONVERT(BIGINT, r.reads)) AS totalReads,
		SUM(CONVERT(BIGINT, r.writes)) AS totalWrites,
		SUM(CONVERT(BIGINT, r.cpu_time)) AS totalCpu,
		SUM(CONVERT(BIGINT, tsu.user_objects_alloc_page_count + tsu.internal_objects_alloc_page_count)) AS writes_in_tempdb
	FROM sys.dm_exec_requests r
		JOIN sys.dm_exec_sessions s ON s.session_id = r.session_id
		JOIN sys.dm_db_task_space_usage tsu ON s.session_id = tsu.session_id and r.request_id = tsu.request_id
		WHERE @include = 2
		or (@include = 1 and not r.status in ('sleeping') and r.session_id <> @@SPID)
		or (@include = 0 and not r.status in ('sleeping', 'background') and r.session_id <> @@SPID)
		GROUP BY
		r.session_id,
		s.host_name,
		s.program_name,
		s.host_process_id,
		s.login_name,
		r.start_time,
		r.sql_handle,
		r.database_id,
		r.blocking_session_id,
		r.wait_type,
		r.wait_time,
		r.wait_resource,
		r.last_wait_type,
		r.status,
		r.statement_start_offset,
		r.statement_end_offset
) x