When managing SQL Server performance, the built-in sp_who2 procedure is a commonly used tool for quickly checking active processes and their resource usage. However, it doesn’t always give you much flexibility when analyzing SQL sessions.
In this post, I’m sharing 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. Unlike custom queries that only capture currently active sessions, this method ensures you see a more complete picture of SQL activity, including sessions that may still be holding locks or consuming resources in the background.
Logging sp_who2 Results into a Temp Table
The following script logs the output of sp_who2 into a temporary table, allowing for easier querying, filtering, and sorting of session information:
Once executed, this script captures all active sessions at that moment, letting you filter by database, order by SPID, or focus on blocked processes with simple queries.
Logging the output of sp_who2 into a table ensures you catch everything, even non-active SPIDs that could be affecting system performance without showing up in real-time monitoring.
This SQL script has helped me in various occasions when diving deep into troubleshooting issues. I hope it also serves you well too!
Leave a Reply