What's the difference between sp_who and sp_who2
- sp_who2 is an undocumented extension of sp_who.
- It is available in MS SQL Server 7, MS SQL Server 2000 and MS SQL Server 2005.
sp_who
sp_who provides:
- System process ID.
- Status of the process.
- Login name of the user.
- Name of the user.
- If the process is blocked, the SPID of the blocking process.
- Database the process is using.
- Command currently being executed.
USE master;
GO
EXEC SP_WHO;
go
GO
EXEC SP_WHO;
go
spid ecid status loginame hostname blk dbname cmd request_id ------ ------ ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----- -------------------------------------------------------------------------------------------------------------------------------- ---------------- ----------- 1 0 background sa 0 NULL RESOURCE MONITOR 0 2 0 background sa 0 NULL LAZY WRITER 0 3 0 suspended sa 0 NULL LOG WRITER 0 4 0 background sa 0 NULL LOCK MONITOR 0 5 0 background sa 0 master SIGNAL HANDLER 0 6 0 sleeping sa 0 master TASK MANAGER 0 7 0 background sa 0 master TRACE QUEUE TASK 0 8 0 sleeping sa 0 NULL UNKNOWN TOKEN 0 9 0 sleeping sa 0 master TASK MANAGER 0 10 0 background sa 0 master TASK MANAGER 0 11 0 suspended sa 0 master CHECKPOINT 0 12 0 background sa 0 master BRKR EVENT HNDLR 0 13 0 sleeping sa 0 master TASK MANAGER 0 14 0 background sa 0 master BRKR TASK 0 15 0 background sa 0 master BRKR TASK 0 16 0 sleeping sa 0 master TASK MANAGER 0 17 0 sleeping sa 0 master TASK MANAGER 0 18 0 sleeping sa 0 master TASK MANAGER 0 19 0 sleeping sa 0 master TASK MANAGER 0 20 0 sleeping sa 0 master TASK MANAGER 0 52 0 sleeping SQLSERVER\froggy SQLSERVER 0 master AWAITING COMMAND 0 53 0 runnable SQLSERVER\froggy SQLSERVER 0 master SELECT 0 (22 row(s) affected)
sp_who2
sp_who2 also provides:
- Total CPU time of each process.
- Total amount of disk reads for each process.
- Last time a client called a procedure or executed a query.
- Application connected.
USE master;
GO
EXEC sp_who2;
go
GO
EXEC sp_who2;
go
SPID Status Login HostName BlkBy DBName Command CPUTime DiskIO LastBatch ProgramName SPID REQUESTID ----- ------------------------------ -------------------------------- --------- ----- ------ ---------------- ------- ------ -------------- ---------------------------------------------- ----- --------- 1 BACKGROUND sa . . NULL RESOURCE MONITOR 15234 0 07/10 18:07:03 1 0 2 BACKGROUND sa . . NULL LAZY WRITER 343 0 07/10 18:07:03 2 0 3 SUSPENDED sa . . NULL LOG WRITER 0 0 07/10 18:07:03 3 0 4 BACKGROUND sa . . NULL LOCK MONITOR 0 0 07/10 18:07:03 4 0 5 BACKGROUND sa . . master SIGNAL HANDLER 0 0 07/10 18:07:03 5 0 6 sleeping sa . . master TASK MANAGER 0 0 07/10 18:07:03 6 0 7 BACKGROUND sa . . master TRACE QUEUE TASK 0 0 07/10 18:07:03 7 0 8 sleeping sa . . NULL UNKNOWN TOKEN 0 0 07/10 18:07:03 8 0 9 sleeping sa . . master TASK MANAGER 0 0 07/10 18:07:03 9 0 10 BACKGROUND sa . . master TASK MANAGER 0 0 07/10 18:07:03 10 0 11 SUSPENDED sa . . master CHECKPOINT 15 48 07/10 18:07:03 11 0 12 BACKGROUND sa . . master BRKR EVENT HNDLR 0 51 07/10 18:07:03 12 0 13 sleeping sa . . master TASK MANAGER 0 11 07/10 18:07:03 13 0 14 BACKGROUND sa . . master BRKR TASK 0 0 07/10 18:07:03 14 0 15 BACKGROUND sa . . master BRKR TASK 0 0 07/10 18:07:03 15 0 16 sleeping sa . . master TASK MANAGER 0 11 07/10 18:07:03 16 0 17 sleeping sa . . master TASK MANAGER 0 0 07/10 18:07:03 17 0 18 sleeping sa . . master TASK MANAGER 0 0 07/10 18:07:03 18 0 19 sleeping sa . . master TASK MANAGER 0 0 07/10 18:07:03 19 0 20 sleeping sa . . master TASK MANAGER 0 0 07/10 18:07:03 20 0 52 sleeping SQLSERVER\froggy SQLSERVER . master AWAITING COMMAND 16 0 07/11 17:51:17 Microsoft SQL Server Management Studio 52 0 53 RUNNABLE SQLSERVER\froggy SQLSERVER . master SELECT INTO 359 205 07/11 18:10:52 Microsoft SQL Server Management Studio - Query 53 0 (22 row(s) affected)