Sp_who2 Stored Procedure in SQL Server 2012

In this article I will describe the significance of the sp_who2 system stored procedure in SQL Server 2012.

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.