Today, we’re going through how to check who is active on your SQL Server.
This is really useful for monitoring performance, especially if there’s a drop in server performance. It helps you identify people running queries — whether they’re long-running queries — and allows you to monitor their activity. You can also check for things like blocking and locks.
Built-In SQL Server Tools: sp_who and sp_who2
There are two built-in commands in SQL Server that help with this: sp_who and sp_who2.
These commands allow you to check the status of different commands.
The main difference between the two is that sp_who2 shows additional information, like CPU time, disk I/O, and more. Both are useful for getting an overview of what’s happening, but there’s an even better tool available.
EXEC sp_who or EXEC sp_who2
A Better Option: WhoIsActive by Adam Machanic
A better way to monitor SQL Server activity is by using WhoIsActive, a script created by Adam Machanic.
(Really fitting name for the work he’s done!)
You can easily download the latest version of the script — I’ll leave a link below. It’s just a simple bit of SQL that you can grab and run inside your SQL Server.
Once you run it, it creates a stored procedure in the master database.
When you execute it, you’ll see detailed information about active queries.
For example, I ran a long-running query, then executed sp_WhoIsActive, and immediately saw:
- The user running the query
- The exact query text
- How long it had been running
- CPU usage
- Whether there was any blocking
This gives you much more detailed insight compared to sp_who or sp_who2 and is especially good if you’re trying to trace performance issues.
EXEC sp_whoisactive
https://whoisactive.com/downloads
Bonus Tip: Dynamic Management Views (DMVs)
As a bonus, you can also use SQL Server Dynamic Management Views (DMVs).
These provide a lot of the same information, but through views instead of stored procedures. If you prefer working with views, or want even deeper customization, it’s worth exploring DMVs as well.
(I’ll hopefully make a dedicated video/blog about DMVs soon!)
SELECT
session_id,
login_name,
status,
host_name,
program_name,
cpu_time,
memory_usage
FROM sys.dm_exec_sessions
WHERE is_user_process = 1;
Summary
There are a few different ways you can check who is active on SQL Server:
sp_whosp_who2- WhoIsActive (recommended)
- Dynamic Management Views (bonus)
Whether you’re learning for the first time or troubleshooting production, dev, or test environments, these methods will help you identify long-running queries and performance issues quickly.
