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:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
USE master GO CREATE PROCEDURE dbo.sp_whoisblocking AS BEGIN SET NOCOUNT ON; EXEC dbo.sp_WhoIsActive @output_column_list = '[dd{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3}][session_id][sql_text][sql_command][login_name][block{9aaef31dc5e5b7f5049f8f082b0a92ca8701bd6baf49d5704ff4650a929dabe3}]'; END GO |
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!