mike92980
- Mar 16
- 4 min read

SP_WHO2

SP_Who2:

behaves similarly to SP_Who with one major difference. It offers more granular control over which sessions are returned in the output. For example, you can specify whether to include only System Administrators or restrict it to a single database. Additionally, it allows you to further refine your result set by filtering on specific attributes such as elapsed time since last command was issued or open transactions for a given session ID.

Here are the columns that are outputted when you execute SP_WHO2

SPID: This is the session ID assigned by SQL Server to the connection.
Status: This column shows the current status of the process. Common values are "sleeping", "running", "suspended", "background", etc.
Login: This column shows the login name used to connect to SQL Server.
HostName: This column shows the name of the computer that initiated the connection.
BlkBy: If the session is blocked by another session, this column shows the session ID that is blocking it.
DBName: This column shows the name of the database being used by the session.
Command: This column shows the last command executed by the session.
CPUTime: This column shows the CPU time (in milliseconds) used by the session since it started.
DiskIO: This column shows the number of physical disk reads and writes made by the session since it started.
LastBatch: This column shows the time of the last batch or statement executed by the session.
ProgramName: This column shows the name of the client program that initiated the connection.

Analyzing Performance

When dealing with more complex scenarios, such as troubleshooting a connection issue or analyzing performance across multiple sessions, SP_WHO2 can be an invaluable tool. It provides detailed information about each user session and request that is currently active on the SQL Server instance. This includes the login name, hostname of the connected machine, CPU time used by the session, disk reads and writes, physical I/O operations and memory usage for each request. Additionally, it allows you to filter results based on specific criteria such as elapsed time since last command was issued or open transactions for a given session ID.

How To Use SP_WHO2 To Detect Blocking

You can use SP_WHO2 to detect blocking in SQL Server by looking at the "BlkBy" column in the output. If a session is being blocked by another session, the "BlkBy" column will show the session ID (SPID) of the blocking session.

To detect blocking using SP_WHO2, follow these steps:

Execute SP_WHO2 command to see a list of all active sessions in the SQL Server instance.
Look for rows where the "BlkBy" column is not null. These are the sessions that are being blocked by another session.
Note the "SPID" value of the blocking session, which is shown in the "BlkBy" column.
Use the "SPID" value to identify the blocking session in the output of SP_WHO2.
Look at the "Command" column for the blocking session to see what it is doing. This can help you identify the cause of the blocking.
If necessary, use the "KILL" command to terminate the blocking session. You can find more information on killing sessions here

https://www.bps-corp.com/post/kill-a-process-in-sql-server

Here is an example T-SQL code that you can use to detect blocking using SP_WHO2:

EXEC sp_who2;

SELECT 
    blocked_ses.session_id AS blocked_spid,
    blocked_ses.login_name AS blocked_login,
    blocking_ses.session_id AS blocking_spid,
    blocking_ses.login_name AS blocking_login,
    blocking_ses.[status] AS blocking_status,
    blocking_ses.command AS blocking_command
FROM 
    sys.dm_exec_sessions blocked_ses
    INNER JOIN sys.dm_exec_sessions blocking_ses ON blocked_ses.blocking_session_id = blocking_ses.session_id
ORDER BY 
    blocked_ses.session_id;

This code uses the DMV sys.dm_exec_sessions to join sessions that are blocking other sessions. The output shows the SPID and login name of both the blocking and blocked sessions, as well as the status and command of the blocking session.

Other Uses For SP_WHO2

Besides detecting blocking, SP_WHO2 has several other uses in SQL Server. Here are some examples:

Identify long-running queries:

SP_WHO2 can help you identify queries that have been running for a long time and may be causing performance issues. You can sort the output by the "CPUTime" or "DiskIO" columns to find the queries that are using the most resources.

Troubleshoot connectivity issues:

SP_WHO2 can show you which clients are connected to SQL Server and from which host they are connecting. This can help you troubleshoot connectivity issues and identify unauthorized connections.

Monitor server activity:

SP_WHO2 can give you a real-time view of server activity, including the number of active connections, the current status of each connection, and the commands being executed.

Identify session details:

SP_WHO2 can show you detailed information about each active session, such as the login name, the database being used, and the client program that initiated the connection. This can help you identify which users and applications are causing performance issues or other problems.

Find open transactions:

SP_WHO2 can show you which sessions have open transactions. This can help you identify potential issues with locking, blocking, or deadlocks.

Parameters

Find Active Users

USE master;  
GO  
EXEC sp_who 'active';  
GO

Find Specific Session ID

USE master;  
GO  
EXEC sp_who '103';  
GO

SP_WHO2 is particularly useful when trying to identify blocking sessions or pinpointing resource-hungry queries that are impacting overall performance. It enables DBAs to quickly diagnose queries that have been running longer than expected and take corrective action if necessary. As a result, it helps maintain database stability by providing visibility into user connections and requests which can help identify potential issues before they become serious problems.

Learn T-SQL