sp_who_3 : An Enhanced and Efficient Version of sp_who
Last updated: August 18th '04 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New


This month's 2 click survey:
Is .NET important for a database professional?


Click here to find out the top 15 SQL Server books purchased by this site's visitors! NEW




 
NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email vyaskn@hotmail.com with 'subscribe' in the subject line
See also:  
Database coding conventions SQL Server interview questions
Evaluation of federated database servers SQL Server FAQ on programming, administration, replication and tools
SQL Server security best practices SQL Server administration best practices
Related books:
Microsoft SQL Server Performance Tuning Microsoft SQL Server 2000 Unleashed Inside SQL Server 2000

sp_who_3 : An Enhanced and Efficient Version of sp_who

As you probably know, there's a system stored procedure called sp_who, that provides information about current Microsoft SQL Server processes and users. Output of this stored procedure can be used for troubleshooting performance problems and blocking issues.

Microsoft also provided an enhanced version of sp_who, namely sp_who2, but this is not a documented stored procedure. But it is safe to use, and I use it everyday in my DBA activities. sp_who2 is not majorly enhanced than sp_who, but it provides few additional pieces of information, in the form of additional columns.

Looking for more advanced stuff? Then you are in the right place. Recently Mike Barzilli (an IT Manager and a Senior Database Architect) got in touch with me, and expressed his wish to post his sp_who_3 on this website. sp_who_3 is a very powerful and efficient version of sp_who. Co-authors include: Mike Petanovitch, Geoff Bryant, Troy Gatchell

sp_who_3 works only in SQL Server 2000, and SP3, SP3a or higher build of SQL Server is required.

sp_who_3 is a utility for DBAs. It displays processes connected to the system and what they are doing. It can find blocking processes, can return the Input Buffer for everything in the recordset returned, and provides immense filtering and sorting capabilities. It's designed to be extremely efficient, yet it provides many more features than sp_who, sp_who2, sp_lock, sp_lockinfo, and dbcc inputbuffer combined. sp_who_3 allows you to filter based on any parameters in the result set and it also includes several built-in run modes which provide filtering for you. If you run sp_who_3 '?', it will return a very detailed help file with usage examples and an explanation of everything it does.

Click here to download the create script for sp_who_3

Click here to download the script to remove sp_who_3 from your system

Here's the complete documentation for sp_who_3:


AUTHOR			: Mike A. Barzilli
AUTHOR EMAIL		: mike@barzilli.com

sp_who_3:
	Provides information about current SQL Server processes. The results can be filtered and sorted
	in many ways. It has 6 run modes which filter the information to specific needs.

	This procedure was written to avoid using sp_who, sp_who2, sp_lock, sp_lockinfo, and dbcc
	inputbuffer. Those procedures are not flexible and hang if tempdb is itself locked. sp_who_3
	returns the results without any performance or lock compromising steps such as temp tables,
	cursors, table parameters, or any locks. sp_who_3 only works with SQL Server 2000 Service Pack
	3 or higher. sp_who_3 is best viewed in grid mode.

Syntax:
	EXEC sp_who_3 [ [ @run_mode = ] 'run mode' ]
		[ , [ @spid = ] 'SPID' ]
		[ , [ @login = ] 'login name' ]
		[ , [ @host = ] 'host name' ]
		[ , [ @db = ] 'database name' ]
		[ , [ @program = ] 'program name' ]
		[ , [ @status = ] 'status' ]
		[ , [ @command = ] 'command' ]
		[ , [ @blk = ] 'blocking SPID' ]
		[ , [ @wait = ] 'wait type and wait time (msecs)' ]
		[ , [ @trans = ] 'open transactions' ]
		[ , [ @cpu = ] 'CPU time usage' ]
		[ , [ @dsk = ] 'disk usage' ]
		[ , [ @last_batch = ] 'last batch date and time' ]
		[ , [ @o = ] 'order by clause' ]

Arguments:
	[ @run_mode = ] 'run_mode'
		Is the mode to use. @run_mode is NVARCHAR(12), with a default of "normal". Valid values
		are: "normal", "active", "input", "block", "active input", "block input", and "help".
		When using "active", "block", "active input", or "block input", any filters specified
		are ignored. Sort order can always be specified.

		A run mode of "normal" returns all current connections and those results are filtered
		by all 13 filter parameters if any filter conditions are specified.

		Run mode "active" returns only active connections or ones with open transactions. All
		filter parameters are ignored.

		Run mode "input" is similar to "normal" but it also returns the input buffer. Only
		sysadmin or processadmin members can use this mode. This mode creates an SQLDMO object
		that is passed to a function. It opens a trusted connection (using the SQL Service
		Account) to the local server. Once complete, it closes the SQLDMO object. Using
		sp_oacreate is not recommended because it runs in-processes, wastes resources, and may
		crash the server if errors occur. These risks are minimized because only built-in Tools
		objects are used. The results are filtered by all 13 filter parameters if specified.

		Run mode "block" returns only connections that are either being blocked or are blocking
		other processes. All filter parameters are ignored.

		Run mode "active input" combines "active" and "input" modes. It returns active
		connections with input buffers. Only sysadmin or processadmin members can use this
		mode. The same "input" function is used. All filter parameters are ignored.

		Run mode "block input" combines "block" and "input" modes. It returns blocked
		connections with input buffers. Only sysadmin or processadmin members can use this
		mode. The same "input" function is used. All filter parameters are ignored.

		Run mode "help" only returns information about how to use sp_who_3.

	[ @spid = ] 'SPID'
		Is the SPID used to filter the results. @spid is DECIMAL(7,2), with a default of NULL.
		The input parameter @spid is NVARCHAR(50) to allow Comparison Operators ("=", "<>",
		"!=", ">", "<", ">=", "!<", "<=", "!>") to be specified. This is one of 13 possible
		filters. For all numerical filters, if a Comparison Operator is not specified and only
		a numerical value is passed in, sp_who_3 defaults to an equal comparison. When using
		the @spid filter on SPIDs that have multiple ECIDs {.00, .01, ...n}, use a whole
		number SPID (I.E. 1, 2, etc...) to return the SPIDs and all their sub-threads. If a
		decimal is used instead (I.E. 1.00, 2.01, etc...), only SPIDs matching both the SPID
		and ECID portions are returned. All filters are ignored when run_mode is "active",
		"block", "active input", or "block input".

	[ @login = ] 'login name'
		Is the login name used to filter the results. @login is NVARCHAR(25), with a default of
		NULL. The input parameter @login is NVARCHAR(50) to allow Wildcard Operators ("%", "_",
		"[]", "[n-n]", "[^]") to be specified. @login and all non-numeric filters will get
		Search Predicate (such as "=" or the keyword "LIKE") added to them internally. Do not
		include Search Predicates in these parameters.

	[ @host = ] 'host name'
		Is the host name used to filter the results. @host is NVARCHAR(12), with a default of
		NULL. The input parameter @host is NVARCHAR(50) to allow Wildcard Operators ("%", "_",
		etc...) to be specified.

	[ @db = ] 'database name'
		Is the database name used to filter the results. @db is NVARCHAR(25), with a default of
		NULL. The input parameter @db is NVARCHAR(50) to allow Wildcard Operators ("%", "_",
		etc...) to be specified.

	[ @program = ] 'program name'
		Is the program name used to filter the results. @program is NVARCHAR(25), with a
		default of NULL. The input parameter @program is NVARCHAR(50) to allow Wildcard
		Operators ("%", "_", etc...) to be specified.

	[ @status = ] 'status'
		Is the status used to filter the results. @status is NVARCHAR(12), with a default of
		NULL. The input parameter @status is NVARCHAR(50) to allow Wildcard Operators ("%",
		"_", etc...) to be specified.

	[ @command = ] 'command'
		Is the command used to filter the results. @command is NVARCHAR(16), with a default of
		NULL. The input parameter @command is NVARCHAR(50) to allow Wildcard Operators ("%",
		"_", etc...) to be specified.

	[ @blk = ] 'blocking SPID'
		Is the "blocking SPID" used to filter the results. @blk is SMALLINT, with a default of
		NULL. The input parameter @blk is NVARCHAR(50) to allow Comparison Operators ("<", ">",
		etc...) to be specified. The [Blk] column substitutes "." instead of zero for display.
		However, in the @blk filter parameter, use a input a "0" to find SPIDs with no blocks.

	[ @wait = ] 'wait type and wait time (msecs)'
		Is the wait type and wait time (msecs) used to filter the results. @wait is
		NVARCHAR(45), with a default of NULL. The input parameter @wait is NVARCHAR(50) to
		allow Wildcard Operators ("%", "_", etc...) to be specified.

	[ @trans = ] 'open transactions'
		Is the number of open transactions used to filter the results. @trans is SMALLINT, with
		a default of NULL. The input parameter @trans is NVARCHAR(50) to allow Comparison
		Operators ("<", ">", etc...) to be specified. The [Trans] column substitutes "."
		instead of zero for display. However, in the @trans filter parameter, use a input a "0"
		to find	SPIDs with no transactions.

	[ @cpu = ] 'CPU time usage'
		Is the CPU time usage (msecs) a SPID has used that is used to filter the results. @cpu
		is INT, with a default of NULL. The input parameter @cpu is NVARCHAR(50) to allow
		Comparison Operators ("<", ">", etc...) to be specified.

	[ @dsk = ] 'disk usage'
		Is the number of disk reads and writes a SPID has used that is used to filter the
		results. @dsk is INT, with a default of NULL. The input parameter @dsk is NVARCHAR(50)
		to allow Comparison Operators ("<", ">", etc...) to be specified.

	[ @last_batch = ] 'last batch date and time'
		Is the last batch time used to filter the results. @last_batch is DATETIME, with a
		default of NULL. The input parameter @last_batch is NVARCHAR(50) to allow Comparison
		Operators ("<", ">", etc...) to be specified. Do not include quotes inside the
		parameter (I.E. @last_batch = '> '01/01/01''). Here is a valid example of the
		last_batch parameter (@last_batch = '<= 01/01/01').

	[ @o = ] 'order by clause'
		Is the order by clause used to sort the results. @o is NVARCHAR(50), with a default of
		NULL. The input parameter @o cannot exceed NVARCHAR(50). @o is used to specify an
		order by clause the same way as for a normal select statement.

Return Code Values:
	0 (success) or 1 (failure).

Result Set:
	sp_who_3 returns a result set with the following information:

	Column		Data type	Description
	--
	[SPID]		DECIMAL(7,2)	The process ID (SPID) and execution context ID (ECID). ECID =
					{.00, .01, ...n}, where .00 is the parent thread, and {.01,
					.02, ...n} represent any sub-threads.
	--
	[Login]		NVARCHAR(25)	The login name associated with the particular process.
	--
	[Host]		NVARCHAR(12)	The host computer name associated with the process.
	--
	[DB]		NVARCHAR(25)	The database currently in use by the process.
	--
	[Program]	NVARCHAR(25)	The name of the program connecting to the SQL Server.
	--
	[Status]	NVARCHAR(12)	The process status (I.E. "sleeping", "RUNNABLE", etc...). See
					remarks for a complete list and descriptions.
	--
	[Command]	NVARCHAR(16)	The command currently executing for the process (I.E.
					"AWAITING COMMAND", "SELECT", etc...).
	--
	[Blk]		SMALLINT	The process ID for the blocking process, if one exists.
	--
	[Wait]		NVARCHAR(45)	The current wait type (I.E. "LCK_M_S", "LCK_U", etc...)
					followed by the current wait time (msecs) in parenthesis. See
					remarks for a complete list and descriptions.
	--
	[Trans]		SMALLINT	The number of open transactions for the process.
	--
	[CPU]		INT		The cumulative CPU time (msecs) for the process.
	--
	[Dsk]		BIGINT		The cumulative number of disk reads and writes for the SPID.
	--
	[Last_Batch]	DATETIME	The last date and time the process executed a command.
	--
	[Input_Buffer]	VARCHAR(8000)	The last SQL command the process executed. This only contains
					the first 255 characters unless the SPID was actively executing
					when sp_who_3 was run. This column only exists with run modes
					of "input", "active input", and "block input".
	--
	[SP2]		SMALLINT	The process ID repeated without the ECID for easy reading.
	----------------

	The sp_who_3 results default to sorted by SPID then ECID ascending. In the case of parallel
	processing, sub-thread SPIDs are created. The main thread is indicated as SPID = x.00 where
	ECID is the two digits after the decimal. Other sub-threads have the same SPID with ECID > 00.
	The [Blk] and [Trans] columns substitute "." instead of zero for display in the result set.
	However, in the @blk and @trans filter parameters, use a input a "0" to filter results.

Remarks:
	SQL Server 2000 reserves SPID values of 1 to 50 for internal use, SPID values 51 and higher are
	for user sessions. The input buffer of SPIDs less than 51 is not available. When using run
	modes that return the input buffers, the results will take longer.

	In SQL Server 2000, all orphaned DTC transactions are assigned the SPID value of "-2". Orphaned
	DTC transactions are distributed transactions that are not associated with any SPID. Thus, when
	an orphaned transaction is blocking another process, this orphaned distributed transaction can
	be identified by its distinctive "-2" SPID value. For more information, see "Troubleshooting MS
	DTC Transactions" in SQL Server Books Online (BOL).

	When using modes of "block" or "block input", the result set contains all processes that are
	blocked or are causing the blocking. A blocking process (which may have exclusive locks) is
	one that is holding resources other SPIDs need to continue.

	The "Status" column gives a quick look at the status of a particular SPID. Typically,
	"sleeping" means the SPID has completed execution and is waiting for the application to submit
	another batch. The following list gives brief explanations for "Status" values:

	BACKGROUND	The SPID is performing a background task. This indicates a system thread.
	--
	DEFWAKEUP	Indicates that a SPID is waiting on a resource that is in the process of being
			freed. The "Wait" column should indicate the resource in question.
	--
	DORMANT 	Same as "sleeping", except a "DORMANT" SPID was reset after completing an RPC
			event from remote system (possibly a linked server). This cleans up resources
			and is normal; the SPID is available to execute. The system may	be caching the
			connection. Replication SPIDs show "DORMANT" when waiting.
	--
	ROLLBACK	The SPID is currently rolling back a transaction.
	--
	RUNNABLE 	The SPID is currently executing.
	--
	sleeping	The SPID is not currently executing. This usually indicates that the SPID is
			awaiting a command from the application.
	--
	SPINLOOP	The SPID is trying to acquire a spinlock used for SMP (multi-processor)
			concurrency control. It is using memory protected against multiple access. If a
			SPINLOOP process does not give up control, then it is likely SQL Server will
			become unresponsive and it is unlikely a KILL command will work on a process in
			this state. You may need to restart the server.
	--
	UNKNOWN TOKEN 	Indicates that the SPID is currently not executing a batch.
	----------------

	The "Wait" column describes the resource type in question that the SPID is waiting for and how
	long it has waited. The following list gives brief explanations for "Wait" values:

	ASYNC_		During backup and restore threads are written in parallel. Indicates possible
	DISKPOOL_LOCK	disk bottleneck. See PhysicalDisk counters for confirmation.
	--
	ASYNC_I/O_	Waiting for asynchronous I/O requests to complete. Indicates possible disk
	COMPLETION	bottleneck, adding I/O bandwidth or balancing I/O across drives may help.
	--
	CMEMTHREAD	Waiting for thread-safe memory objects. Waiting on access to memory object.
	--
	CURSOR		Waiting for thread synchronization with asynchronous cursors.
	--
	CXPACKET	Waiting on packet synchronize up for exchange operator (parallel query).
	--
	DBTABLE		A new checkpoint request is waiting for a previous checkpoint to complete.
	--
	DTC		Waiting for Distributed Transaction Coordinator (DTC).
	--
	EC		Non-parallel synchronization between sub-thread or Execution Context.
	--
	EXCHANGE	Waiting on a parallel process to complete, shutdown, or startup.
	--
	EXECSYNC	Query memory and spooling to disk.
	--
	I/O_COMPLETION	Waiting for I/O requests to complete.
	--
	LATCH_x		Short-term light-weight synchronization objects. Latches are not held for the
			duration of a transaction. Latches are generally unrelated to I/O.
	--
	LATCH_DT	Destroy latch. See LATCH_x.
	--
	LATCH_EX	Exclusive latch. See LATCH_x.
	--
	LATCH_KP	Keep latch. See LATCH_x.
	--
	LATCH_NL	Null latch. See LATCH_x.
	--
	LATCH_SH	Shared latch. See LATCH_x.
	--
	LATCH_UP	Update latch. See LATCH_x.
	--
	LCK_M_BU	Bulk Update lock.
	--
	LCK_M_II_NL	Intent-Insert NULL (Key-Range) lock.
	--
	LCK_M_II_X	Intent-Insert Exclusive (Key-Range) lock.
	--
	LCK_M_IS	Intent-Shared lock.
	--
	LCK_M_IS_S	Intent-Shared Shared (Key-Range) lock.
	--
	LCK_M_IS_U	Intent-Shared Update (Key-Range) lock.
	--
	LCK_M_IU	Intent-Update lock.
	--
	LCK_M_IX	Intent-Exclusive lock.
	--
	LCK_M_RIn_NL	Range-Intent Null lock.
	--
	LCK_M_RIn_S	Range-Intent Shared lock.
	--
	LCK_M_RIn_U	Range-Intent Update lock.
	--
	LCK_M_RIn_X	Range-Intent Exclusive lock.
	--
	LCK_M_RS_S	Range-Shared Shared (Key-Range) lock.
	--
	LCK_M_RS_U	Range-Shared Update (Key-Range) lock.
	--
	LCK_M_RX_S	Range-Exclusive Shared (Key-Range) lock.
	--
	LCK_M_RX_U	Range-Exclusive Update (Key-Range) lock.
	--
	LCK_M_RX_X	Range-Exclusive Exclusive (Key-Range) lock.
	--
	LCK_M_S		Shared lock.
	--
	LCK_M_SCH_M	Schema Modification lock used for ALTER TABLE commands.
	--
	LCK_M_SCH_S	Schema Shared Stability lock.
	--
	LCK_M_SIU	Shared Intent to Update lock.
	--
	LCK_M_SIX	Shared Intent Exclusive lock.
	--
	LCK_M_U		Update lock used for the initial lock when doing updates.
	--
	LCK_M_UIX	Update Intent Exclusive lock.
	--
	LCK_M_X		Exclusive lock used for INSERT, UPDATE, and DELETE commands.
	--
	LOGMGR		Waiting for write requests for the transaction log to complete.
	--
	MISCELLANEOUS	Catch all wait types.
	--
	NETWORKIO	Waiting on network I/O. Waiting to read or write to a network client.
 	--
	OLEDB		Waiting on an OLE DB provider.
	--
	PAGEIOLATCH_x	Short-term synchronization objects used to synchronize access to buffer pages.
			PAGEIOLATCH_x is used for disk to memory transfers.
	--
	PAGEIOLATCH_DT	I/O page destroy latch.
	--
	PAGEIOLATCH_EX	I/O page latch exclusive. Waiting for the write of an I/O page.
	--
	PAGEIOLATCH_KP	I/O page latch keep.
	--
	PAGEIOLATCH_NL	I/O page latch null.
	--
	PAGEIOLATCH_SH	I/O page latch shared. Waiting for the read of an I/O page.
	--
	PAGEIOLATCH_UP	I/O page latch update.
	--
	PAGELATCH_x	Short-term light-weight synchronization objects. Page latching operations
			occur during row transfers to memory.
	--
	PAGELATCH_DT	Page latch destroy.
	--
	PAGELATCH_EX	Page latch exclusive.
	--
	PAGELATCH_KP	Page latch keep page.
	--
	PAGELATCH_NL	Page latch null.
	--
	PAGELATCH_SH	Page latch shared. Heavy concurrent inserts to the same index range can cause
			this type of contention. The solution in these cases is to distribute the
			inserts using a more appropriate index strategy.
	--
	PAGELATCH_UP	Page latch update. Contention for allocation of related pages. The contention
			indicates more data files are needed.
	--
	PAGESUPP	Release Spinlock in parallel query thread. Possible disk bottleneck.
	--
	PIPELINE_	Allows one user to perform multiple operations such as update index stats for
	INDEX_STAT	that user as well as other users waiting for the same operation.
	--
	PIPELINE_LOG	Allows one user to perform multiple operations such as writes to log for that
			user as well as other users waiting for the same operation.
	--
	PIPELINE_VLM	Allows one user to perform multiple operations.
	--
	PSS_CHILD	Waiting on a child thread in an asynchronous cursor operations.
	--
	RESOURCE_QUEUE	Internal use only.
 	--
	RESOURCE_	Waiting to a acquire a resource semaphore; must wait for memory grant. Used for
	SEMAPHORE	synchronization. Common for large queries such as hash joins.
	--
	SHUTDOWN	Wait for SPID to finish completion before shutdown completes.
	--
	SLEEP		Internal use only.
	--
	TEMPOBJ		Dropping a global temp object that is being used by others.
	--
	TRAN_MARK_DT	Transaction latch - destroy.
	--
	TRAN_MARK_EX	Transaction latch - exclusive.
	--
	TRAN_MARK_KP	Transaction latch - keep page.
	--
	TRAN_MARK_NL	Transaction latch - null.
	--
	TRAN_MARK_SH	Transaction latch - shared.
	--
	TRAN_MARK_UP	Transaction latch - update.
	--
	UMS_THREAD	Batch waiting for a worker thread to free up to run the batch.
	--
	WAITFOR		Wait initiated by a Transact-SQL WAITFOR statement.
	--
	WRITELOG	Waiting for write requests to the transaction log to complete.
	--
	XACTLOCKINFO	Waiting on bulk operation when releasing/escalating/transferring locks.
	--
	XCB		Acquiring access to a transaction control block (XCB). XCBs are usually private
			to a session, but can be shared between sessions when using bound sessions or
			multiple sessions in enlisting in the same DTC transaction.
	----------------

Permissions:
	Execute permissions default to the public role. However, only members of the sysadmin or
	processadmin roles can execute "input", "active input", or "block input" run modes.

Examples:
	A. List all current processes.
		This example uses sp_who_3 without parameters to show all current processes.

		EXEC sp_who_3

		Here, all processes ordered by SPID then ECID ascending are returned.

	B. List current processes involved in blocking.
		This example uses "block" run mode to show blocked and blocking processes.

		EXEC sp_who_3
			@run_mode = 'block'

		Here, all blocked and blocking SPIDs are returned to help diagnose locking issues.

	C. List current active processes and order the results.
		This example uses "active" run mode and orders by "Login" and "SPID" descending.

		EXEC sp_who_3
			'active',
			@o = '2 DESC, 1 DESC'

		Here, active SPIDs sorted by Login Name and SPID descending are returned.

	D. List current processes with specific filters and order the results.
		This example uses filters on "SPID", "Program", and "Trans" while ordering by "DB".

		EXEC sp_who_3
			@spid = '= 50',
			@program = '%sql%',
			@trans = '>= 1',
			@o = 'DB ASC'

		Here, processes with a SPID of 50 (including all sub-thread ECIDs of SPID 50), which
		also contain "sql" anywhere in the Program Name, which have Transaction Counts greater
		than or equal to 1, and sorted by Database Name are returned.

	E. List current processes including input buffers with filters and order the results.
		This example uses "input" run mode to show process information while filtering on
		"SPID", "DB" and "Last_Batch". It also orders the results by "Status" ascending.

		EXEC sp_who_3
			@run_mode = 'input',
			@spid = '> 50',
			@login = NULL,
			@host = NULL,
			@db = 'pubs',
			@program = NULL,
			@status = NULL,
			@blk = NULL,
			@wait = NULL,
			@trans = NULL,
			@cpu = NULL,
			@dsk = NULL,
			@last_batch = '>= 12/01/03 11:59:48',
			@o = 'Status ASC'

		Here, the results contain processes meeting all filters ordered by the @o order by
		clause. Notice the use of comparison operators in the SPID and last_batch filters.
 

Disclaimer, terms of use and privacy policy
Copyright © 1997 - 2004 Narayana Vyas Kondreddi. All rights reserved.