Announcing Sp_WhoIsBlocking

This is a tongue in cheek post, basically highlighting a cool feature in sp_whoisactive, and how I created a wrapper for it to make my life easier.

I am a big fan of Adam Machanic’s sp_whoisactive procedure. I use it multiple times per day. I have it on all of my SQL Servers, and have a keyboard shortcut in SSMS that calls it. It is typically one of the first things I run when looking at an issue with a server.

I recently had a situation where I was not in the office, or by a computer, and there was a blocking issue on one of our production SQL Servers. I got a call from one of our DevOps guys about it, and told him to run the EXEC dbo.sp_WhoIsActive command and find his query in the output and look at the blocking session ID.

Instant. Information. Overload.

There’s a lot of output from sp_whoisactive. He saw all of this information about waits and CPU and tempdb and reads and writes and IO, and he thought something was seriously wrong. So the next day I decided to come up with an easy way to see if there was some blocking going on, without all of the extra information.

I wanted to be able to let someone run sp_whoisactive, but just have a few select columns to return in the output. As it turns out, this capability is built right in to sp_whoisactive, by using the @output_column_list parameter.

And so, Sp_WhoIsBlocking was born. All I did was create an easy to use wrapper for sp_whoisactive that I could tell someone over the phone how to run, and here it is:

Is it overkill? Probably.
Is it anything that a true DBA would ever want to use? Probably not.
Has it made my life easier? Most definitely!

(Visited 695 times, 1 visits today)

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.