The sp_who internal procedure allows users to view current activity on the database. This command provides a view into several system tables (e.g., syslocks, sysprocesses, etc.). The sp_who command returns the following information:
- Spid—The system process ID.
- status—The status of the process (e.g., RUNNABLE, SLEEPING).
- loginame—Login name of the user.
- hostname—Machine name of the user.
- blk—If the process is getting blocked, this value is the SPID of the blocking process.
- dbname—Name of database the process is using.
- Cmd—The command currently being executed (e.g., SELECT, INSERT)
The sp_who2 internal procedure provides the above information, but also provides the following additional information:
- CPUTime—Total CPU time the process has taken.
- DiskIO—Total amount of disk reads for the process.
- LastBatch—Last time a client called a procedure or executed a query.
- ProgramName—Application that has initiated the connection (e.g., Visual Basic, MS SQL Query Analyzer)
The sp_who2 procedure is not documented in MS SQL Server 7.0 Books Online. Using sp_who2 enables SQL Server users to better analyze database processes and more easily identify which application is associated with each process.
