SQL Server database administrators need to be able to quickly find out what queries and stored procedures are running slow. Microsoft includes sp_who and sp_who2 in SQL Server 2005 and 2008, but there’s a much better tool, and it’s completely free.
In this five minute tutorial video, I explain how to use sp_WhoIsActive from Adam Machanic (Blog – @AdamMachanic):
My Favorite sp_WhoIsActive Parameters
sp_WhoIsActive has all kinds of parameters, but here’s my favorites. Keep in mind that the more parameters you pass in, the more work sp_WhoIsActive has to do in order to get the data you want from SQL Server’s Dynamic Management Views (DMVs), and the slower it will run:
- @get_plans = 1 – this gives you the execution plans for running queries.
- @get_locks = 1 – gives you an XML snippet you can click on to see what table, row, object, etc locks each query owns. Useful when you’re trying to figure out why one query is blocking others.
- @get_task_info = 2 – if a query has gone parallel and you’re troubleshooting CXPACKET waits, you can figure out what each task in the query is waiting on.
Where to Download sp_WhoIsActive
- Download sp_WhoIsActive free here
- Read Adam’s posts about sp_WhoIsActive – he’s written a ton of posts going into the advanced features.
- Log sp_WhoIsActive results into a table over time for troubleshooting history.
If you like that, check out sp_BlitzFirst®.
I got sick and tired of users asking me, “Are you doing anything on the server right now? Do you know why the server is slow right now?” So I wrote sp_BlitzFirst®. Here’s how it works:
