Sp_who2 – System Stored procedure
Many of you as dba must be knowing the use of sp_who2 in sql server, If not let me tell you it will help to troubleshoot performance issue of database also monitors the current activity on SQL Server. SP_WHO2 is system stored procedure which is installed by default while installation of SQL Server.
It will give you the list of SPID along with useful information like CPU/IO usage, login user name , status of SPID and so on…
Here SPID -> Server Process ID
If you may see queries are in suspended state that means it is waiting for resource which is not currently available. It can be logical resource like row locked in table or physical resource like memory and data pages which consume High CPU/IO use. Once resource is free it will start running again.
Also use to identify blocking issues in sql server.
Permissions : A Login must have either sysadmin or VIEW SERVER STATE to execute sp_who2 stored procedure to view full list of SPID on sql server.