Ever wondered what’s happening inside your SQL Server instance? sp_who, sp_who2 and sp_whoisactive are stored procedures that allow you to view current users, sessions, and processes within a SQL Server instance. This kind of information helps you diagnose blocking issues and monitor general activity.
Among them, sp_whoisactive is a go-to tool for many SQL DBAs & Software Engineers. Created by Adam Machanic, it offers a much richer set of details compared to sp_who and sp_who2. It doesn’t show system processes and provides valuable data, such as running full text of SQL queries and Execution Plans. I would
Let’s take a closer look at these three procedures and how they can help with SQL Server monitoring and troubleshooting.
sp_who
sp_who retrieves information on all active processes in SQL Server at the time of execution. It includes login names, hostnames, current commands being run (not full SQL text), and target databases.
The first 50 results are system SPIDS which you’d never try kill. If you’re looking terminating SQL SPIDs, check out my other post: Killing SQL Server Processes.
sp_who2
sp_who2 is an undocumented stored procedure by Microsoft, meaning it could be modified or deprecated without notice. It offers additional columns beyond sp_who, making it slightly more informative.
In my other post I share a simple SQL script that logs sp_who2 results into a temporary table, allowing you to filter and sort sessions in SQL for review. This can be especially useful when investigating blocking issues, long-running queries, or hidden inactive SPIDs that standard scripts might miss.
sp_whoisactive
Now, this is where things improve. sp_whoisactive provides a comprehensive snapshot of SQL Server’s current activity. Info includes full SQL queries, query plans, hardware usage, temp DB allocations, blocks and more. This 4 minute video by Brent Ozar gives the perfect run-through of it:
To try it yourself, follow these steps:
1. Download sp_whoisactive and execute the script in a query window.
2. Run EXEC sp_whoisactive; to see active sessions.
3. If your test environment is empty, simulate a blocking scenario:
— Open a session and start an explicit transaction without committing.
— In another session, run a SELECT on the same table—it will get blocked.
— Now execute sp_whoisactive to see the blocking details.
As you can see in the screenshot above, session_id 55 is blocking the SELECT statement from being run. As mentioned above in this post, you might want to kill the lead blocker in SQL if it’s causing a bottleneck.
Most of the time, I run sp_whoisactive without parameters or use @get_plans = 1 to capture execution plans. Depending on the situation, additional parameters can provide more useful info.
A great way to leverage this stored proc is by running it as a SQL Agent job every 30–60 seconds, storing results into a table for historical analysis.
I hope all this was useful for you! Cheers.
Leave a Reply