Introduction
In this article we will learn about the advanced version of "sp_who" named "sp_who2" system Stored Procedure. This sp_who2 provides more information about the current user and process compared to the sp_who system Stored Procedure.
I assume you are familiar with the sp_who system Stored Procedure. For more help visit SP_who in SQL Server 2012.
SP2_who in SQL Server 2012
SP_who2 is a undocumented system Stored Procedure similar to sp_who except that it provides additional information about the CPU time, Disk IO and last batch described in the following:
- CPU time: The total time of CPU time that the process has taken.
- Disk IO: The total amount of disk read/write time that the process has taken.
- Last batch: The date and time of the last query from the process.
Syntax
The syntax of the "sp_who2" system Stored Procedure is as in the following:
sp_who2 [usename][processid]
Where "username" is the name of the user to be queried and "processid" is the id of the process.
Example
Let's see an example.
Getting additional information about all the process and users
We can get additional information about the CPU time, Disk IO and last batch time, for all processes and users currently being used using the following command.
![sp_who2-in-sql.jpg]()
OUTPUT
![output-sp_who2-in-sql.jpg]()
Getting additional information about a specified user
Similarly we can get additional information about a specified user using the following command.
![sp_who2-for-user-in-sql.jpg]()
Getting additional information about a specified process
Similarly we can get additional information about a specified process using the following command.
![sp_who2-for-processr-in-sql.jpg]()
Difference between SP_who and SP_who2 Stored Procedure in SQL
There not a big difference in functionality of these two Stored Procedure. However sp_who is a Microsoft SQL Server's documented command while sp_who2 is an undocumented command. sp_who2 returns more information compared to sp_who like CPU time of each process, total amount of disk reads for each process and last time a client called a procedure or executed a query.