Thursday, 28 March 2013

Differences between sp_who and sp_who2

There is a very useful system stored procedure called sp_who on SQL Server that lists the users and processes running on the system. It comes handy when you want to know the loading on the SQL Server or see if any process is blocked.

You run:

exec sp_who

The result will be similar to the following list:


You can filter the data returned to show only those processes that are not idle. If you specify a particular user like sp_who [@login_name =] 'login' then you get processes for that user, otherwise all processes for all users will be listed.

Look at http://msdn.microsoft.com/en-us/library/aa260384(v=sql.80).aspx to get the meaning of each column in the list.

There is another stored procedure called sp_who2 that gives some more information like total CPU time for each process, total amount of disk reads for each process, etc.

If you want see what is running under each process you can use below command.

 DBCC INPUTBUFFER (SPID)

The SPID will get from SP_Who or SP_Who2 commnad.