//  home   //  advanced search   //  news   //  categories   //  sql build chart   //  downloads   //  statistics
 ASP FAQ 
Home
ASP FAQ Tutorials

   8000XXXX Errors
   ASP.NET 2.0
   Classic ASP 1.0
   Databases
      Access DB & ADO
      General SQL Server & Access Articles
      MySQL
      Other Articles
      Schema Tutorials
      Sql Server 2000
      Sql Server 2005
   General Concepts
   Search Engine Optimization (SEO)

Contact Us
Site Map

Search

Web
aspfaq.com
tutorials.aspfaq.com
sqlserver2005.databases.aspfaq.com

ASP FAQ Tutorials :: Databases :: Sql Server 2005 :: How do I mimic sp_who2


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 Articles

Where 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?

 

 


Created: 9/27/2006 | Last Updated: 9/27/2006 | broken links | helpful | not helpful | statistics
© Copyright 2006, UBR, Inc. All Rights Reserved. (701)

 

Copyright 1999-2006, All rights reserved.
Finding content
Finding content.  An error has occured...