Understanding sp_who SQL Server procedure

sp_who provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine.

exec sp_who

A blocking process, which may have an exclusive lock, is one that is holding resources that another process needs.

In case of parallel processing, subthreads are created for the specific session ID. The main thread is indicated as spid = and ecid =0. The other subthreads have the same spid = , but with ecid > 0.

In SQL Server 2000 and later, all orphaned distributed transactions are assigned the session ID value of '-2'. Orphaned distributed transactions are distributed transactions that are not associated with any session ID.

SQL Server 2000 and later reserves session ID values from 1 through 50 for internal use, and session ID values 51 or higher represent user sessions.
By default user sees only the current session processes or you need VIEW SERVER STATE permission on the server to see all executing sessions on the instance of SQL Server.

Following are sp_who columns information

1. spid (smallint) Session ID
2. ecid (smallint) Execution context ID of a given thread associated with a specific session ID. i.e. 0,1,2,3,…n. values >0 represents sub-thread.
3. Status(nchar(30)) Process status.
4. Loginame(nchar(128)) Login name associated with the particular process.
5. Hostname(nchar(128)) Host or computer name for each process.
6. Blk(char(5)) Session ID for the blocking process only, if one exists. Otherwise, this column is zero. This column will return a '-2' for the blocking orphaned transaction.
7. Dbname(nchar(128)) Database used by the process.
8. Cmd(nchar(16)) Database Engine command (Transact-SQL statement, internal Database Engine process, and so on) executing for the process.
9. request_id(int) ID for requests running in a specific session.

Examples

Listing all current processes
USE master;
GO
EXEC sp_who;
GO

Listing a specific user's process
USE master;
GO
EXEC sp_who 'janetl';
GO

Displaying all active processes
USE master;
GO
EXEC sp_who 'active';
GO

Displaying a specific process identified by a session ID
USE master;
GO
EXEC sp_who '10' --specifies the process_id;
GO

you can get more information from http://msdn.microsoft.com/en-us/library/ms174313.aspx