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.
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.
Learn About More of the Best Free SQL Server Downloads
I’ve put together a 90-minute video training class you can watch right now. It covers dozens of my other favorite powerful freebies – free books, performance tuning tools, and more. Learn more about the Best Free SQL Server Downloads.