Tags

,


sp_who2 is a useful tool in learning about the current activity in SQL Server. However, there are some flaws in the design, including using (NOLOCK) on virtual tables (which is harmless but useless), ignoring the @loginame parameter when it is neither ‘active’ nor NULL, and relying on soon-to-be-deprecated objects like sysprocesses and syslockinfo.

Someone at Microsoft tasked the MVPs with replicating sp_who2 in SQL Server 2005, using only the dynamic management views — and, in particular, staying away from sysprocesses.

I managed to whip something up quickly, with a few caveats. I did not bother replicating the @loginame functionality in this version, because Microsoft’s version doesn’t work either. Also, there are a few pieces of information that are simply not available in the new DMVs, for example commands with values ‘AWAITING COMMAND’ and ‘UNKNOWN TOKEN’ are nowhere to be found. Finally, some metrics for tasks like ‘LOG WRITER’ and ‘CHECKPOINT’ seem to be missing, or I just don’t know which DMV is missing from my joins. But it does come pretty close, with the majority of the discrepancies involving system SPIDs:

 

USE master;
GO

IF EXISTS
(
	SELECT 1 
		FROM sys.procedures
		WHERE name = 'ab_who2'
)
	DROP PROCEDURE dbo.ab_who2;
GO

CREATE PROCEDURE dbo.ab_who2
	@status VARCHAR(255) = NULL
	-- only 'active' supported
AS
BEGIN
	SET NOCOUNT ON;

	SELECT 
		SPID = s.session_id,
		Status = UPPER(COALESCE
			(
				r.status,
				ot.task_state,
				s.status, 
			'')),
		[Login] = s.login_name,
		HostName = COALESCE
			(
				s.[host_name],
				'  .'
			),
		BlkBy = COALESCE(RTRIM
			(
				NULLIF(r.blocking_session_id, 0)),
				'  .'
			),
		DBName = COALESCE
			(
				DB_NAME(COALESCE
				(
					r.database_id,
					t.database_id
				)),
				''
			),
		Command = COALESCE
			(
				r.Command,
				r.wait_type,
				wt.wait_type,
				r.last_wait_type,
				''
			),
		CPUTime = COALESCE
			(
				NULLIF(r.cpu_time, 0), 
				NULLIF(s.cpu_time, 0),
				NULLIF(s.total_scheduled_time, 0), 
				NULLIF(tt.CPU_Time, 0),
				0
			),
		DiskIO = COALESCE
			(
				NULLIF(r.reads + r.writes, 0),
				NULLIF(s.reads + s.writes, 0),
				NULLIF(c.num_reads + c.num_writes, 0),
				0
			),
		LastBatch = COALESCE
			(
				r.start_time,
				s.last_request_start_time
			),
		ProgramName = COALESCE
			(
				s.program_name, 
				''
			),
		SPID = s.session_id,
		REQUESTID = 0
	FROM
		sys.dm_exec_sessions s
	LEFT OUTER JOIN
		sys.dm_exec_requests r
	ON
		s.session_id = r.session_id
	LEFT OUTER JOIN
		sys.dm_exec_connections c
	ON
		s.session_id = c.session_id
	LEFT OUTER JOIN
	(
		SELECT 
			request_session_id,
			database_id = MAX(resource_database_id)
		FROM
			sys.dm_tran_locks
		GROUP BY
			request_session_id
	) t
	ON
		s.session_id = t.request_session_id
	LEFT OUTER JOIN
		sys.dm_os_waiting_tasks wt
	ON 
		s.session_id = wt.session_id
	LEFT OUTER JOIN
		sys.dm_os_tasks ot
	ON 
		s.session_id = ot.session_id
	LEFT OUTER JOIN
	(
		SELECT
			ot.session_id,
			CPU_Time = MAX(usermode_time)
		FROM
			sys.dm_os_tasks ot
		INNER JOIN
			sys.dm_os_workers ow
		ON
			ot.worker_address = ow.worker_address
		INNER JOIN
			sys.dm_os_threads oth
		ON
			ow.thread_address = oth.thread_address
		GROUP BY
			ot.session_id
	) tt
	ON
		s.session_id = tt.session_id
	WHERE
		COALESCE
		(
			r.command,
			r.wait_type,
			wt.wait_type,
			r.last_wait_type,
			'a'
		) >= COALESCE
		(
			@status, 
			'a'
		)
	ORDER BY
		s.session_id;
END
GO

EXEC ab_who2;
EXEC ab_who2 'active';
GO

Please let me know if you spot any problems, or if you have any clues about the missing information. There have definitely been some learning curves along the way…

Advertisements