In SQL Server, the SP_WHO stored procedure is a built-in tool used to provide information about current users, sessions, and processes running on the database server. You can use it to diagnose performance issues, check for blocking sessions, and get a sense of which users are connected to the database and what they are doing.
Usage of SP_WHO
To execute SP_WHO, you simply run the following command in your SQL Server Management Studio (SSMS) or any SQL client:
EXEC sp_who;
Output Columns
When you run SP_WHO, you will receive a result set with several important columns, which typically include:
- SPID: Server Process ID. This is the unique identifier for the session.
- Status: Current status of the session (e.g., RUNNABLE, SUSPENDED, etc.).
- Login: The name of the user that is logged into the session.
- HostName: The name of the host machine from which the session is connected.
- BlkBy: If the session is blocked, this shows the SPID of the blocking session.
- DBName: The name of the database the session is currently using.
- Command: The last command executed in the session.
- CPU: Total CPU time used by the session.
- Physical I/O: Total physical reads and writes performed by the session.
- MemUsage: Amount of memory used by the session.
Example Output
The output might look something like this:
SPID Status Login HostName DBName Command CPU Physical IO MemUsage
---- ------ ----- -------- ------- ------- --- ------------- ---------
53 RUNNABLE User1 Machine1 Database1 SELECT 10 500 5
54 SUSPENDED User2 Machine2 Database2 INSERT 0 0 3
55 BLOCKED User3 Machine3 Database1 UPDATE 20 200 4
For More Detail
If you need more detailed information about sessions, especially the blocking and wait states, consider using SP_WHO2 (another variant of SP_WHO), which provides additional columns such as the last wait type and the wait time.
EXEC sp_who2;
Filtering Results
You can also filter the result set to get information about specific sessions or statuses. For example, to find out about only the blocked sessions, you can combine it with a WHERE clause in a SELECT statement:
EXEC sp_who;
-- You will have to manually identify blocked sessions based on the BlkBy column.
Conclusion
Using SP_WHO, along with the additional monitoring and management tools in SQL Server, can help you efficiently monitor and troubleshoot user connections and session activity on your SQL Server instance.