Sample System Processes Over Time

A continuous server activity sampler: for a configurable number of seconds (default 60), at a configurable resolution (default 100ms), this script snapshots dm_exec_requests joined to sysprocesses, computes per-session deltas for CPU, elapsed time, reads, writes, logical reads and row count between samples, flags new sessions, and keeps only the rows you care about (filter with the @CareAbout* switches). The final result is a time-series chart-ready dataset of everything the server did during the window - status, waits, isolation level, granted memory and all.

SET NOCOUNT ON;
GO

DECLARE @StartTime DateTime
DECLARE @CollectionIndex Int
DECLARE @SecondsToRun Int
DECLARE @SampleResolution VarChar(100)
DECLARE @CareAboutCPU bit
DECLARE @CareAboutReads bit
DECLARE @CareAboutWrites bit
DECLARE @CareAboutDuration bit
DECLARE @CareAboutEverything bit

--BEGIN User settable variables.
SET @SecondsToRun = 60
SET @SampleResolution = '00:00:00.100'
SET @CareAboutCPU = 1
SET @CareAboutReads = 1
SET @CareAboutWrites = 1
SET @CareAboutDuration = 1
SET @CareAboutEverything = 0
--END User settable variables.

SET @StartTime = GETDATE()
SET @CollectionIndex = 0

SELECT
	ER.session_id,
	ER.[user_id],

	MIN(IsNull(SP.login_name, '')) as login_name,
	MIN(IsNull(SP.login_time, '')) as login_time,
	MAX(IsNull(SP.[host_name], '')) as [host_name],
	MAX(IsNull(SP.application_name, '')) as application_name,
	
	SUM(IsNull(ER.cpu_time, 0)) as cpu_time,
	SUM(IsNull(ER.total_elapsed_time, 0)) as total_elapsed_time,
	SUM(IsNull(ER.reads, 0)) as reads,
	SUM(IsNull(ER.writes, 0)) as writes,
	SUM(IsNull(ER.logical_reads, 0)) as logical_reads,
	SUM(IsNull(ER.row_count, 0)) as row_count,
	
	MIN(ER.start_time) as start_time,
	MAX(IsNull(ER.[status], '')) as [status],
	MAX(IsNull(ER.command, '')) as command,
	MAX(IsNull(db_name(ER.database_id), '')) as database_name,
	MAX(IsNull(ER.blocking_session_id, 0)) as blocking_session_id,
	MAX(IsNull(ER.wait_type, '')) as wait_type,
	MAX(IsNull(ER.wait_time, 0)) as wait_time,
	MAX(IsNull(ER.wait_resource, '')) as wait_resource,
	MAX(IsNull(ER.open_transaction_count, 0)) as open_transaction_count,
	CASE IsNull(MAX(ER.transaction_isolation_level), -1)
		WHEN 0 THEN 'Unspecified'
		WHEN 1 THEN 'Read Uncomitted'
		WHEN 2 THEN 'Read Committed'
		WHEN 3 THEN 'Repeatable'
		WHEN 4 THEN 'Serializable'
		WHEN 5 THEN 'Snapshot'
		ELSE 'Unknown'
	END as transaction_isolation_level,
	MAX(ER.granted_query_memory) as granted_query_memory
INTO
	#CurrentValues
FROM
	sys.dm_exec_requests as ER
INNER JOIN (
	SELECT
		iSP.spid as session_id,
		MAX(IsNull(iSP.loginame, '')) as login_name,
		MAX(iSP.login_time) as login_time,
		MAX(IsNull(iSP.hostname, '')) as [host_name],
		MAX(IsNull(iSP.[program_name], '')) as application_name
	FROM
		sys.sysprocesses AS iSP
	GROUP BY
		iSP.spid
	) as SP
	ON SP.session_id = ER.session_id
WHERE
	ER.session_id <> @@spid
GROUP BY
	ER.session_id,
	ER.[user_id]

SELECT
	GETDATE() as CollectionDate,
	@CollectionIndex as CollectionIndex,
	1 as is_new_session,
	null as delta_cpu_time,
	null as delta_total_elapsed_time,
	null as delta_reads,
	null as delta_writes,
	null as delta_logical_reads,
	null as delta_row_count,

	[user_id],
	session_id,
	login_name,
	login_time,
	[host_name],
	application_name,

	cpu_time,
	total_elapsed_time,
	reads,
	writes,
	logical_reads,
	row_count,
	start_time,
	[status],
	command,
	database_name,
	blocking_session_id,
	wait_type,
	wait_time,
	wait_resource,
	open_transaction_count,
	transaction_isolation_level,
	granted_query_memory
INTO
	#ChartValues
FROM
	#CurrentValues
SELECT * INTO #PreviousValues FROM #CurrentValues

WAITFOR DELAY '00:00:01'

WHILE(DATEDIFF(s, @StartTime, GetDate()) <= @SecondsToRun)
BEGIN--WHILE
	WAITFOR DELAY @SampleResolution
	SET @CollectionIndex = @CollectionIndex + 1

	TRUNCATE TABLE #CurrentValues

	INSERT INTO #CurrentValues
	SELECT
		ER.session_id,
		ER.[user_id],
		
		MIN(IsNull(SP.login_name, '')) as login_name,
		MIN(SP.login_time) as login_time,
		MAX(IsNull(SP.[host_name], '')) as [host_name],
		MAX(IsNull(SP.application_name, '')) as application_name,

		SUM(IsNull(ER.cpu_time, 0)) as cpu_time,
		SUM(IsNull(ER.total_elapsed_time, 0)) as total_elapsed_time,
		SUM(IsNull(ER.reads, 0)) as reads,
		SUM(IsNull(ER.writes, 0)) as writes,
		SUM(IsNull(ER.logical_reads, 0)) as logical_reads,
		SUM(IsNull(ER.row_count, 0)) as row_count,
		
		MIN(ER.start_time) as start_time,
		MAX(IsNull(ER.[status], '')) as [status],
		MAX(IsNull(ER.command, '')) as command,
		MAX(IsNull(db_name(ER.database_id), '')) as database_name,
		MAX(IsNull(ER.blocking_session_id, 0)) as blocking_session_id,
		MAX(IsNull(ER.wait_type, '')) as wait_type,
		MAX(IsNull(ER.wait_time, 0)) as wait_time,
		MAX(IsNull(ER.wait_resource, '')) as wait_resource,
		MAX(IsNull(ER.open_transaction_count, 0)) as open_transaction_count,
		CASE MAX(IsNull(ER.transaction_isolation_level, -1))
			WHEN 0 THEN 'Unspecified'
			WHEN 1 THEN 'Read Uncomitted'
			WHEN 2 THEN 'Read Committed'
			WHEN 3 THEN 'Repeatable'
			WHEN 4 THEN 'Serializable'
			WHEN 5 THEN 'Snapshot'
			ELSE 'Unknown'
		END as transaction_isolation_level,
		MAX(ER.granted_query_memory) as granted_query_memory
	FROM
		sys.dm_exec_requests AS ER
	INNER JOIN (
		SELECT
			iSP.spid as session_id,
			MAX(iSP.loginame) as login_name,
			MAX(iSP.login_time) as login_time,
			MAX(iSP.hostname) as [host_name],
			MAX(iSP.[program_name]) as application_name
		FROM
			sys.sysprocesses AS iSP
		GROUP BY
			iSP.spid
		) as SP
		ON SP.session_id = ER.session_id
	WHERE
		ER.session_id <> @@spid
	GROUP BY
		ER.session_id,
		ER.[user_id]

	INSERT INTO #ChartValues
	(
		CollectionDate,
		CollectionIndex,
		is_new_session,
		
		delta_cpu_time,
		delta_total_elapsed_time,
		delta_reads,
		delta_writes,
		delta_logical_reads,
		delta_row_count,
		
		session_id,
		[user_id],
		
		login_name,
		login_time,
		[host_name],
		application_name,

		cpu_time,
		total_elapsed_time,
		reads,
		writes,
		logical_reads,
		row_count,
		start_time,
		[status],
		command,
		database_name,
		blocking_session_id,
		wait_type,
		wait_time,
		wait_resource,
		open_transaction_count,
		transaction_isolation_level,
		granted_query_memory	
	)
	SELECT
		GETDATE() as CollectionDate,
		@CollectionIndex as CollectionIndex,
		(CASE WHEN PV.session_id IS NULL THEN 1 ELSE 0 END)as is_new_session,
		
		CV.cpu_time - PV.cpu_time as delta_cpu_time,
		CV.total_elapsed_time - PV.total_elapsed_time as delta_total_elapsed_time,
		CV.reads - PV.reads as delta_reads,
		CV.writes - PV.writes as delta_writes,
		CV.logical_reads - PV.logical_reads as delta_logical_reads,
		CV.row_count - PV.row_count as delta_row_count,
		
		CV.session_id,
		CV.[user_id],
		
		CV.login_name,
		CV.login_time,
		CV.[host_name],
		CV.application_name,

		CV.cpu_time,
		CV.total_elapsed_time,
		CV.reads,
		CV.writes,
		CV.logical_reads,
		CV.row_count,
		CV.start_time,
		CV.[status],
		CV.command,
		CV.database_name,
		CV.blocking_session_id,
		CV.wait_type,
		CV.wait_time,
		CV.wait_resource,
		CV.open_transaction_count,
		CV.transaction_isolation_level,
		CV.granted_query_memory
	FROM
		#CurrentValues AS CV
	LEFT OUTER JOIN #PreviousValues AS PV
		ON PV.session_id = CV.session_id
		AND PV.[user_id] = CV.[user_id]
		AND PV.login_time = CV.login_time
	WHERE
		@CareAboutEverything = 1
		OR (
			CASE WHEN @CareAboutCPU = 1 THEN
				CASE WHEN CV.cpu_time - PV.cpu_time > 0 THEN 1 ELSE 0 END
			ELSE
				0
			END
		) = 1
		OR (
			CASE WHEN @CareAboutReads = 1 THEN
				CASE WHEN CV.logical_reads - PV.logical_reads > 0 THEN 1 ELSE 0 END
			ELSE
				0
			END
		) = 1
		OR (
			CASE WHEN @CareAboutWrites = 1 THEN
				CASE WHEN CV.writes - PV.writes > 0 THEN 1 ELSE 0 END
			ELSE
				0
			END
		) = 1
		OR (
			CASE WHEN @CareAboutDuration = 1 THEN
				CASE WHEN CV.total_elapsed_time - PV.total_elapsed_time > 0 THEN 1 ELSE 0 END
			ELSE
				0
			END
		) = 1

	TRUNCATE TABLE #PreviousValues
	INSERT INTO #PreviousValues SELECT * FROM #CurrentValues

END--WHILE

SELECT
	1 as [Count],

	CV.CollectionDate as CollectionDate,
	CV.CollectionIndex as CollectionIndex,
	CV.is_new_session as IsNewSession,

	CV.session_id as SessionId,
	CV.[user_id] as UserId,
		
	CV.login_name as LoginName,
	CV.login_time as LoginTime,
	CV.[host_name] as HostName,
	CV.application_name as ApplicationName,
	
	CV.delta_cpu_time as DeltaCPUTime,
	CV.delta_total_elapsed_time as DeltaTotalElapsedTime,
	CV.delta_reads as DeltaReads,
	CV.delta_writes as DeltaWrites,
	CV.delta_logical_reads as DeltaLogicalReads,
	CV.delta_row_count as [RowCount],
	
	CV.cpu_time as CPUTime,
	CV.total_elapsed_time as TotalElapsedTime,
	CV.reads as Reads,
	CV.writes as Writes,
	CV.logical_reads as LogicalReads,
	CV.row_count as [RowCount],

	CV.start_time as StartTime,
	CV.[status] as [Status],
	CV.command as Command,
	CV.database_name as DatabaseName,
	CV.blocking_session_id as BlockingSessionId,
	CV.wait_type as WaitType,
	CV.wait_time as WaitTime,
	CV.wait_resource as WaitResource,
	CV.open_transaction_count as OpenTransactionCount,
	CV.transaction_isolation_level as TransactionIsolationLevel,
	CV.granted_query_memory as GrantedQueryMemory
FROM
	#ChartValues AS CV
WHERE
	CollectionIndex > 0
ORDER BY
	CollectionIndex,
	session_id
	
DROP TABLE #ChartValues
DROP TABLE #CurrentValues
DROP TABLE #PreviousValues