Contact Us
Site Map

Search

Part 2 of the task from Microsoft was to produce ideas for "sp_who3... how to take sp_who2 to the next level using SQL Server 2005 DMVs." Here are some of the things I found lacking in sp_who and sp_who2:

• they don't support ordering.
• other than 'active', no filtering supported (e.g. filter by spid, database name, hostname, IP address, program name, and SQL command).
• parameter @loginame for sp_who2 does not work as "advertised."
• a lot of useful information is missing, such as splitting i/o between reads and writes, showing memory usage, adding DBCC INPUTBUFFER results, and other data like rowcounts, transaction counts, and lock counts.

So, I decided to write my own, which I'll admit I started long before this challenge. In the following procedure, I have addressed all of these limitations and made the procedure more forward-compatible by eradicating references to sysprocesses, syslockinfo and other objects that are supposed to be off-limits.

Like with ab_who2, there are some caveats here. I resorted to using MAX on a lot of columns to avoid repeating expressions in the GROUP BY clause, and even more so because I was too lazy to figure out which outer join was producing duplicate rows. Pretty sure it is sys.dm_tran_locks, but I am not in the mood to rewrite any of it at this point. There is a #temp table and a cursor in this one. Because of the overhead, I opted to only use the sys.dm_exec_sql_text() function when I explicitly demanded it. The #temp table is also useful to delete rows based on filtering *after* the main SELECT has completed, instead of complicating the initial query further. The cursor is useful, again only when explicitly requested, to execute DBCC INPUTBUFFER for each row remaining after filtering. I couldn't find a way to get the level of detail provided by DBCC INPUTBUFFER either from fn_get_sql or sys.dm_exec_sql_text, and I also didn't know of a quick and dirty way to apply DBCC to a set. So, this is what you get.

USE master;
GO

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


CREATE PROCEDURE dbo.ab_who3

	-- only show logins with this name
	-- 'active' also works like sp_who2/ab_who2
	
	@Loginame			VARCHAR(255) = NULL, 

	-- ShowBlockersOnly will reduce resultset to
	-- those that are blocking or being blocked
	
	@ShowBlockersOnly		BIT = 0,
	
	-- optional search conditions
	
	@SearchSPID		INT = NULL,
	@SearchDBName		NVARCHAR(255) = '%',
	@SearchHostName		NVARCHAR(255) = '%',
	@SearchCommand		NVARCHAR(255) = '%',
	@SearchIP			VARCHAR(16)   = '%',
	@SearchProgramName		NVARCHAR(255) = '%',
	
	-- unlikely you want to see yourself,
	-- but stranger things can happen
	
	@IncludeSelf		BIT = 0,
	
	-- show system SPIDs?
	
	@IncludeSystemSPIDs		BIT = 0,
	
	-- augment exec_sql info with DBCC INPUTBUFFER
	
	@IncludeSQL		BIT = 0,
	
	-- displays only the first 255 characters of SQL
	
	@Brief			BIT = 0,

	-- optional sorting... allowed values:
	-- 'Elapsed_Time', 'CPU_Time', 
	-- 'Logical_Reads', 'Reads', 'Writes'
	
	@OrderBy			VARCHAR(32) = NULL
AS
BEGIN
	SET NOCOUNT ON;
	SET ANSI_WARNINGS OFF;

	DECLARE
		@spid		INT,
		@sql		NVARCHAR(MAX),
		@briefsize		SMALLINT;
	
	-- used in conjunction with @Brief and @IncludeSQL
	SET @briefsize = 255;
	
	-- override @IncludeSQL otherwise nothing to search
	IF @SearchCommand != '%'
		SET @IncludeSQL = 1;

	SELECT 
	
		[Spid] = s.[session_id],

		[Status] = MAX(UPPER(COALESCE
			(
				r.[status],
				tt.[task_state],
				s.[status],
				''
			))),
		
		[Command] = MAX(COALESCE
			(
				r.[command],
				r.[wait_type],
				wt.[wait_type],
				r.[last_wait_type], 
				''
			)),

		[Blocked_By] = MAX(CONVERT(VARCHAR(12), COALESCE
			(
				RTRIM(NULLIF(r.[blocking_session_id], 0)),
				'  .'
			))),

		[Database_Name] = MAX(DB_NAME(COALESCE
			(
				tl.[database_id],
				r.[database_id],
				t.[database_id], 
				''
			))),

		[Logical_Reads] = MAX(COALESCE
			(
				NULLIF(r.[logical_reads], 0),
				s.[logical_reads],
				0
			)),
			
		[Reads] = MAX(COALESCE
			(
				NULLIF(r.[reads], 0),
				NULLIF(s.[reads], 0),
				c.[num_reads],
				0
			)),
		
		[Writes] = MAX(COALESCE
			(
				NULLIF(r.[writes], 0),
				NULLIF(s.[writes], 0),
				c.[num_writes],
				0
			)),

		[CPU_Time] = MAX(COALESCE
			(
				NULLIF(tt.[CPU_Time], 0),
				NULLIF(r.[cpu_time], 0), 
				NULLIF(s.[cpu_time], 0),
				s.[total_scheduled_time], 
				0
			)),

		[Elapsed_Time] = MAX(COALESCE
			(
				r.[total_elapsed_time],
				s.[total_elapsed_time]
			)),

		[Row_Count] = MAX(s.[row_count]),
		
		[Memory_In_Pages] = MAX(COALESCE
			(
				NULLIF(r.[granted_query_memory], 0),
				s.[memory_usage],
				0
			)),

		[Tran_Count] = MAX(COALESCE
			(
				t.[trancount], 
				0
			)),
		
		[Lock_Count] = MAX(COALESCE
			(
				tl.[lockcount], 
				0
			)),
		
		[Login_Name] = s.[login_name],

		[Host_Name] = MAX(COALESCE
			(
				s.[host_name],
				'  .'
			)),

		[IP_Address] = MAX(COALESCE
			(
				c.[client_net_address], 
				'  .'
			)),

		[Program_Name] = MAX(COALESCE
			(
				s.[program_name], 
				''
			)),

		[Login_Time] = MAX(COALESCE
			(
				s.[login_time],
				c.[connect_time]
			)),
		
		[Last_Request] = MAX(COALESCE
			(
				r.[start_time],
				s.[last_request_start_time]
			)),
			
		[Handle] = MAX(COALESCE
			(
				r.[sql_handle],
				c.[most_recent_sql_handle]
			)),

		[Exec_SQL] = CONVERT(NVARCHAR(MAX), N'')
	INTO
		#Spids

	FROM
		sys.dm_exec_sessions s

	LEFT OUTER JOIN
		sys.dm_exec_connections c
		ON c.[session_id] = s.[session_id]

	LEFT OUTER JOIN 
		sys.dm_exec_requests r
		ON s.[session_id] = r.[session_id]

	LEFT OUTER JOIN
	(
		SELECT
			[session_id],
			[database_id] = MAX([database_id]),
			[trancount] = COUNT(*)
		FROM
			sys.dm_tran_session_transactions t
		INNER JOIN
			sys.dm_tran_database_transactions dt
		ON
			t.[transaction_id] = dt.[transaction_id]
		GROUP BY
			[session_id]
	) t
		ON s.[session_id] = t.[session_id]

	LEFT OUTER JOIN
	(
		SELECT 
			[request_session_id],
			[database_id] = MAX([resource_database_id]),
			[lockcount] = COUNT(*)
		FROM
			sys.dm_tran_locks WITH (NOLOCK)
		GROUP BY
			[request_session_id]
	) tl
	ON
		s.[session_id] = tl.[request_session_id]

	LEFT OUTER JOIN
		sys.dm_os_waiting_tasks wt
	ON 
		s.[session_id] = wt.[session_id]

	LEFT OUTER JOIN
	(
		SELECT
			ot.[session_id],
			ot.[task_state],
			[CPU_Time] = MAX(oth.[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],
			ot.[task_state]
	) tt
	ON
		s.[session_id] = tt.[session_id]

	WHERE
		s.[login_name] = COALESCE
		(
			NULLIF(@Loginame, 'active'),
			s.[login_name]
		)
	
	GROUP BY
		s.[session_id],
		s.[login_name];


	-- delete rows we're not interested in
	
	IF LOWER(@Loginame) = 'active'
	BEGIN
		DELETE #spids 
			WHERE UPPER([Status]) = 'SLEEPING'
			OR UPPER([Command]) = 'AWAITING COMMAND';
	END	

	IF @ShowBlockersOnly = 1
	BEGIN
		DELETE s1
			FROM #spids s1
			WHERE s1.[Blocked_By] = '  .'
			AND NOT EXISTS
			(
				SELECT 1
				FROM
					#spids
				WHERE
					[Blocked_By] = RTRIM(s1.[Spid])
			);
	END
	
	IF @SearchSPID IS NOT NULL
	BEGIN
		DELETE #spids
		WHERE [Spid] != @SearchSPID;
	END
	
	IF @SearchDBName != '%'
	BEGIN
		DELETE #spids
		WHERE [Database_Name] IS NULL
		OR [Database_Name] NOT LIKE @SearchDBName;
	END
	
	IF @SearchHostName != '%' 
		OR @SearchProgramName != '%'
		OR @SearchIP != '%'
	BEGIN
		DELETE #spids
		WHERE [Host_Name] NOT LIKE @SearchHostName
		OR [Program_Name] NOT LIKE @SearchProgramName
		OR [IP_Address] NOT LIKE @SearchIP;
	END

	IF @IncludeSelf = 0
	BEGIN
		DELETE #spids
		WHERE [Spid] = @@SPID;
	END
	
	IF @IncludeSystemSPIDs = 0
	BEGIN
		DELETE #spids
		WHERE [Spid] <= 50;
	END

	CREATE TABLE #dbcc
	(
		a SYSNAME,
		b SYSNAME,
		[Input_Buffer] NVARCHAR(MAX),
		[Spid] INT NULL
	);

	IF @IncludeSQL = 1
	BEGIN
		UPDATE #spids
			SET [Exec_SQL] = 
			(
				SELECT [text] 
				FROM sys.dm_exec_sql_text([handle])
			);
		
		DECLARE dbcc_cursor CURSOR 
			LOCAL FORWARD_ONLY STATIC READ_ONLY
		FOR 
			SELECT [Spid]
				FROM #spids;

		OPEN dbcc_cursor;
		
		FETCH NEXT FROM dbcc_cursor INTO @spid;
			
		WHILE @@FETCH_STATUS = 0
		BEGIN
			SET @sql = 'DBCC INPUTBUFFER('
				+RTRIM(@spid)+') 
				WITH NO_INFOMSGS;';

			INSERT #dbcc(a,b,[Input_Buffer]) 
				EXEC sp_executesql @sql;

			UPDATE #dbcc
				SET [Spid] = @Spid
				WHERE [Spid] IS NULL;

			FETCH NEXT FROM dbcc_cursor INTO @spid;
		END
			
		CLOSE dbcc_cursor;
		DEALLOCATE dbcc_cursor;

		IF @SearchCommand != '%'
		BEGIN
			DELETE #dbcc 
			WHERE COALESCE([Input_Buffer], '') 
			NOT LIKE @SearchCommand;
			
			DELETE #spids
			WHERE COALESCE([Exec_SQL], '')
			NOT LIKE @SearchCommand;
		END

		IF @Brief = 1
		BEGIN
			UPDATE #dbcc 
			SET [Input_Buffer] = 
			COALESCE(LEFT([Input_Buffer], @briefsize), '');
				
			UPDATE #spids 
			SET [Exec_SQL] = 
			COALESCE(LEFT([Exec_SQL], @briefsize), '');
		END
	END
		
	SELECT
		s.[Spid],
		s.[Status],
		s.[Command],
		s.[Blocked_By],
		s.[Database_Name],
		s.[Logical_Reads],
		s.[Reads],
		s.[Writes],
		s.[CPU_Time],
		s.[Elapsed_Time],
		s.[Row_Count],
		s.[Memory_In_Pages],
		s.[Tran_Count],
		s.[Lock_Count],
		s.[Login_Name],
		s.[Host_Name],
		s.[IP_Address],
		s.[Program_Name],
		s.[Login_Time],
		s.[Last_Request],
		s.[Exec_SQL], 
		[Input_Buffer] = COALESCE(d.[Input_Buffer], '')
	FROM
		#spids s
	LEFT OUTER JOIN
		#dbcc d
	ON
		s.[Spid] = d.[Spid]
	ORDER BY
		CASE @OrderBy
			WHEN 'Elapsed_Time'	THEN s.[Elapsed_Time]
			WHEN 'CPU_Time'	THEN s.[CPU_Time] 
			WHEN 'Logical_Reads'	THEN s.[Logical_Reads] 
			WHEN 'Reads'	THEN s.[Reads] 
			WHEN 'Writes'	THEN s.[Writes] 
		END DESC,
		s.[Spid];
	
	DROP TABLE #dbcc, #Spids;
END
GO

/*
-- some sample usage:

EXEC dbo.ab_who3;

EXEC dbo.ab_who3 'active';

EXEC dbo.ab_who3 
	'active',
	@IncludeSQL = 1,
	@Brief = 1;

EXEC dbo.ab_who3 'active', @IncludeSelf = 1;

EXEC dbo.ab_who3 @Loginame = 'sa';

EXEC dbo.ab_who3 @OrderBy = 'CPU_Time';

EXEC dbo.ab_who3 @IncludeSystemSPIDs = 1;

EXEC dbo.ab_who3 @ShowBlockersOnly = 1;

EXEC dbo.ab_who3 @SearchSPID = 58;

EXEC dbo.ab_who3 @SearchDBName = 'SomeDatabaseName';

EXEC dbo.ab_who3 
	@IncludeSQL = 1,
	@SearchCommand = '%update%sometable%';
*/

As always, let me know if you have any comments or questions.