I use sp_WhoIsActive a lot. I’m extremely grateful for Adam Machanic writing it. It has tons of options that let you specify exactly the information that you need for troubleshooting.
So why did I write a different query to get activity?
There’s some systems where sp_WhoIsActive is too slow. It could be resource related, like CPU, memory, or TempDB pressure, or it could be the DMVs. I’ll talk about that later, I wanted to introduce the idea that there’s situations where you can’t use sp_WhoIsActive.
In that kind of system, you need a lightweight query that can still get the SQL text that the users submitted, along with some similar metrics that you’re used to seeing.
Just three DMVs for this query
I’m just using dm_exec_requests, dm_exec_sessions and dm_exec_input_buffer.
SELECT s.session_id,
r.start_time,
s.host_name,
s.login_name,
i.event_info,
r.status,
s.program_name,
r.writes,
r.reads,
r.logical_reads,
r.blocking_session_id,
r.wait_type,
r.wait_time,
r.wait_resource
FROM sys.dm_exec_requests as r
JOIN sys.dm_exec_sessions as s
on s.session_id = r.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, r.request_id) as i
WHERE s.session_id != @@SPID
and s.is_user_process = 1
--Remove the line above if you want to see system processes
Now, I want to show you a side-by-side comparison of the results. The result sets are very similar, on purpose because I tried to get the same information from dm_exec_requests and dm_exec_sessions.
I’m running a workload using SQLQueryStress, another tool from Adam Machanic. Please click the image to view the full image, they’re huge screenshots.
First, the results from sp_WhoIsActive.
Now the results from my query.
Pros and Cons of each query
Let me the first one to say: sp_WhoIsActive is better. For 99.9% of servers, you want to use sp_WhoIsActive. It’s more user-friendly, it’s more accurate, and it has many more DMVs used to gather information.
There’s also the matter of the sql_text vs event_info. In sp_WhoIsActive, you’re seeing the exact snippet of sql text that’s currently running. This is awesome, and much better.
However, I noticed that some of my SQL Servers would take a really long time to access the DMVs for sql text. To reduce that overhead, my query uses event_info, which will include information like “@PostType = 3” which is the literal and entire text that the user submitted to SQL Server.
If you find my query useful
If this post is useful to you, please let me know. I’m very open to suggestions and ideas on how to improve my version!
