How to Use sp_WhoIsActive to Find Slow SQL Server Queries

SQL Server

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

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:

You can download it in our free First Responder Kit.