You are currently browsing comments. If you would like to return to the full story, you can read the full entry here: “A Better sp_who2 using DMVs (sp_who3)”.
Random MVP RSS Feeds- [Aaron Bertrand] How SQL Saturday could be better: I've been to a lot of SQL Saturdays. They are great events to... http://t.co/bLe97UD5
- [Aaron Bertrand] SQL Server 2008 R2 Service Pack 2 CTP is available: You can download the service pack 2 CTP fro... http://t.co/kblQTr2y
- [SQLTeam] Why to avoid SELECT * from tables in your Views: -- clean up any messes left over from before: if OB... http://t.co/GIDxRhEY
- [Kalen Delaney] Did You Know? Turning Off Locking: Back when the lock manager was rewritten for SQL Server 7, an... http://t.co/lzEEpC11
- [Aaron Bertrand] A cautionary tale about grandfathering CAL licenses in SQL Server 2012 Enterprise: I've tried t... http://t.co/1Uoru0DD
- [Kalen Delaney] Geek City: Build a Big Table with a Columnstore Index: I was looking all over to find a big tabl... http://t.co/kzBZMTZu
- [SQLTeam] ClearTrace Performance on 170GB of Trace Files: I’ve always worked to make ClearTrace perform well. T... http://t.co/R24uwgOl
- [SQLTeam] ClearTrace Beta for SQL Server 2012: I have a beta version of ClearTrace that supports 2012. You can ... http://t.co/IrXM7ZFb
- User Defined Functions and Performance - There is definitely a lack of... http://t.co/YdHjOTuH #fb
- [Aaron Bertrand] Seacoast SQL Users Group : What's New in SQL Server 2012: Tonight I presented my "What's New in... http://t.co/wL7bT7qg
Connect
This worked like a Charm Derek.. I was able to pinpoint the offender really quick and nip it in the bud.
This script/sp rocks!
Thanks!
Nice work but it only seems to find connections to the master,tempdb databases…
EXEC SP_WHO2 gives me about 100 result and your query only 20…
Hi Peter,
This is because this script only shows active connections. Personally I prefer this because when troubleshooting, I can immediately see how busy the server is just by the sheer number of results. sp_who2 will show all connections and I need to figure out which ones are asleep. To change this to show sleeping connections, simply join the sessions table first, then left join on the dm_exec_requests
Derek
Nice. I have a long running Select (over 50 minutes). I’m running sp_who3 with sp_who2 in the same window. who2 DiskIO column is increasing each time I rerun, but who3 IOReads and IOWrites are static at the same values as the first time I ran it in this SSMS window. I would have expected who3 to change. I can see who3 changing ElapsedMS. CPUTime looks frozen too. Is this behaving as intended? Thanks for the tool!
I see now the who3 CPUTime figure is coming from the first who2 row of 7 parallel processes for my query, which is done (or not changing) in who2.
Have you tried my Who is Active procedure?
http://tinyurl.com/WhoIsActive
It is designed with similar goals but has a lot more functionality and several options to help you find the information you need, including the ability to only return active sessions, only return sessions that are either active or have an open transaction, or to return all sessions.
Nice! Thank you Adam. That is comprehensive!
Derek
It is ok in sql 2008 but can not save in sql 2000, got an error:
Incorrect syntax near the keyword ‘OUTER’.
(OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st)
Hi adonetok,
Yes, unfortunately you cannot run this in SQL 2000. It will only work in SQL 2005+
Excellent example, this is a big help for me in our monitoring application.
Great, glad this helped. I have considered altering this to include all sessions and not just active ones. I may add an additional example for that one. This helps me sometimes but I still find myself using sp_who2 a lot.
[...] fix. It is likely you have a run away query. The first thing you want to do is run sp_who2 (or sp_who3) and look for connections that have a SPID > 50 with abnormally high CPU time. The CPU time shown [...]