14 September,2015 by Jack Vamvas
One of my favourite utilities for Production SQL Server troubleshooting is the sp_WhoIsActive utility.
My main uses are to identify blocking leaders, lock escalation,slow running queries. It is possible to get this information in other queries, but I like the way this utility is easy to deploy and get the critical detail .
Quite often this relieves the pressure – buying me time to apply some root-cause analysis.
The utility is built by Adam Machanic . A big thank you!
My favourite uses for sp_WhoIsActive
Find Blocking leaders
EXEC sp_WhoIsActive @find_block_leaders = 1
Returns the same data as in comment header
EXEC sp_WhoIsActive @help = 1
Outer ad-hoc query, query plans, full stored procedure or batch
EXEC sp_whoisactive @get_outer_command = 1,@get_plans=1,@get_full_inner_text=1
Get Locks per request . XML format
EXEC sp_WhoIsActive @get_locks = 1
Get waits of all waits pending on a request
EXEC sp_WhoIsActive @get_task_info = 2
How to download sp_WhoIsActive
Follow this link
Read on how to incorporate sp_whoIsActive with SQL Alerts How to monitor Blocked Processes with SQL Alert and email sp_whoisActive report