In order to troubleshoot performance problems or slowness we
use the above stored procs. Blocking, High
I/O, High CPU and queries running parallelism can be seen in most cases. The
First 50 results i.e results with SPID’s 1 to 49 represent system events and
generally will not cause system slowdown. Users sessions are 50 and above.
Multiple rows having the same SPID or SessionID are parallel processes. The Blkby column is used to find out if any
sessions are being blocked.
Sp_who: Provides information
about current users, sessions, and processes in an instance of the
Microsoft SQL Server Database Engine. When used with option active it
gives the filtered information of those processes that are not idle.When used
with userid or sessionid the information
retrieved belong to a specific user, or that belong to a specific session.
Syntax: Exec Sp_who
SP_who2: Is undocumented hence
not supported by Microsoft but widely used as Sp_who2 provides extra columns compared
to sp_who. Sp_who2 output results are same as sp_who with some extra columns
and can be used to get more information about session id's.
Syntax: Exec Sp_who2
