“SP_WhoIsActive” stored procedure is one of my favorite SP which I used to create in my almost all production database from the day one since last few years. A very BIG THANKS goes to Mr. Adam Machanic (Blog) who is very well-known celebrity in the field of SQL Server since long. He is one of my favorite author and I used to follow his blog very closely.
Today I have created “SP_WhoIsActive” in one more database and thought to salute original creator of this wonderful SP.
I can not keep watch on all my databases on 24*7 basis but “SP_WhoIsActive” do it for me. Whenever I get any complain about slow running query/SP I ask for the approx date and time. When I have SP name, Date & Time, I used to query the table which is being populated by “SP_WhoIsActive” for that particular SP along with approx date & time so that I can get following crucial information about SP which it has faced during that time.
–>TSQL of the SP
–> Parameter of SP for which it ran
–> Wait_Info if there was any
–> TempDB allocation if it had
–>blocking session_id and count if there was any|
–>execution plan of SP
–>execution start time and many more important information
Once I have all these information, I can start my debugging. Without having all these information I feel helpless as I can’t watch all databases personally in each instance.
Any DBA can understand the importance of all these information which “SP_WhoIsActive” provides. This is the reason “SP_WhoIsActive” is my one of the favorite SP which I used to have in each of my SQL Server instances.
Once again, I want to give all the credit to Mr. Adam Machanic. Hats off to you Adam as you have spent hundreds of hours for “SP_WhoIsActive” to help SQL Server community.
I have provided link of new version of “SP_WhoIsActive” in the first paragraph of this article but I am still using little older SP as I have already set it up in many of my server.
I am not able to find that old SP from the blog of Mr. Adam Machanic and hence I am providing it here for the reference of my blog reader. Please download it from here.
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.