I’m Officially A Maintainer Of sp_WhoIsActive!

Busyness, Man

sp_WhoIsActive is probably one of the most famous utilities in SQL Server. To the point where when I see people using sp_who – sp_who4762, I immediately disqualify their ability as a DBA.

If you think that’s unfair, it’s probably because you use sp_who2.

But anyway, with Mr. Machanic being busy with outside of SQL Server projects, the script hadn’t been getting much attention lately. Since I had been working on a couple issues, and saw other piling up, I offered to help with Adam’s project in the same way I help with the First Responder Kit stuff.

I’ll be working on issues over there to get new stuff and bug fixes into the script. If there’s anything you’d like to see in there, or see fixed, let us know!

Help You

If you’re hitting an issue with the script and you want to do some investigating, here’s what I suggest doing.

EXEC sp_WhoIsActive
    @show_own_spid = 1,
    @get_full_inner_text = 1;

Run that along with any of the other parameters you’re using, and click on the sql_text column, that’ll give you the whole query that WhoIsActive runs. Paste that into a new SSMS window, and get rid of the XML artifacts like <?query -- and --?>.

After that, you’ll have to declare a few variables to make things run correctly:

DECLARE
    @i bigint = 922337203685477580,
    @recursion smallint = 1,
    @blocker bit = 0;

You’ll also wanna delete some of the header information that comes along:

(@recursion SMALLINT, @filter sysname, @not_filter sysname, @first_collection_ms_ticks BIGINT)DECLARE @blocker BIT;
SET @blocker = 0;
DECLARE @i INT;
SET @i = 2147483647;

After that, you can hit F5 and it’ll run. If your problem is in the main branch of the script that generates all the complicated dynamic SQL, that’ll help you figure out exactly where the problem is.

Thanks!

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.