How do I mimic sp_who2 with SQL Server 2005's new dynamic management views?
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...
Related ArticlesWhere can I learn about "Yukon," or SQL Server 2005?
Can I create a more robust and flexible version of sp_who2 using SQL Server 2005's DMVs?