Keep eye on the health of your SQL Server database

HealthKeep eye on the health of your SQL Server database

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.

–>Session_ID
–>TSQL of the SP
–> Parameter of SP for which it ran
–> Login_Name
–> Wait_Info if there was any
–> CPU
–> TempDB allocation if it had
–>blocking session_id and count if there was any|
–>reads
–>writes
–>physical_reads
–>execution plan of SP
–>database name
–>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.

If you like this article, do like “Extreme-Advicepage in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.

5 thoughts on “Keep eye on the health of your SQL Server database”

    1. Hi Dirk,

      I used to call SP_WhoIsActive from SSIS package which goes to each server/instance at every 3 minutes and store the data in separate instance/database which I have configured for monitoring activity for DBA. I generally used to keep data for 5 days.

  1. Hi Ritesh,

    thx for the reply. One last question: how much data do you collect in 24 hours?
    I may try your approach by myself and would presize the destination database properly.

    1. Hi Dirk,

      I used to keep it running for peak business hours only which is approx 8 hours a day and I collect approx 9000 rows for one instance which I used to keep for 5 days.

Comments are closed.