sp_who/sp_who2 [ [ @loginame = ] 'login' | session ID | 'ACTIVE' ]
sp_who returns a result set with the following information.
Column | Data type | Description |
---|---|---|
spid | smallint | Session ID. |
ecid | smallint | Execution context ID of a given thread associated with a specific session ID. ECID = {0, 1, 2, 3, ...n }, where 0 always represents the main or parent thread, and {1, 2, 3, ...n } represent the subthreads. |
status | nchar(30) | Process status. The possible values are: dormant . SQL Server is resetting the session. running . The session is running one or more batches. When Multiple Active Result Sets (MARS) is enabled, a session can run multiple batches. For more information, see Using Multiple Active Result Sets (MARS) . background . The session is running a background task, such as deadlock detection. rollback . The session has a transaction rollback in process. pending . The session is waiting for a worker thread to become available. runnable . The session's task is in the runnable queue of a scheduler while waiting to get a time quantum. spinloop . The session's task is waiting for a spinlock to become free. suspended . The session is waiting for an event, such as I/O, to complete. |
loginame | nchar(128) | Login name associated with the particular process. |
hostname | nchar(128) | Host or computer name for each process. |
blk | char(5) | Session ID for the blocking process, if one exists. Otherwise, this column is zero. When a transaction associated with a specified session ID is blocked by an orphaned distributed transaction, this column will return a '-2' for the blocking orphaned transaction. |
dbname | nchar(128) | Database used by the process. |
cmd | nchar(16) | Database Engine command (Transact-SQL statement, internal Database Engine process, and so on) executing for the process. |
request_id | int | ID for requests running in a specific session. |
In case of parallel processing, subthreads are created for the specific session ID. The main thread is indicated as spid = <xxx>
and ecid =0
. The other subthreads have the same spid = <xxx>
, but with ecid > 0.
Above refer to http://msdn.microsoft.com/en-US/library/ms174313.aspx
SPIDs less than 50 are system oriented.