>SQL Server: sp_WhoIsActive, a Must Tool for a DBA


A mail with subject “Low Database Response” or a call with words “What happened to the database?” is normal in a SQL Server DBA’s life. To cope with such bad situations normally a DBA keeps his drawer filled with necessary tools and scripts. But like me, most of DBAs first query to check “What is happening actually” is shortest query in SQL Server i.e. sp_who or sp_who2. Second returns almost same data to sp_who but with more detail information.
Though both system stored procedures are helpful to collect basic information to find out problematic sessions of database but to reach your desired rows, you have to skim through large number of rows and you will find more then 95% of rows useless. Some time information provided by sp_who or sp_who2 is not enough to understand the real problem which leads you to query some other DMVs for such information.
Thanks to Adam Machanic who resolved this problem. His stored procedure WhoIsActive is a must tool for a DBAs kit. This single stored procedure collects almost all necessary information for a DBA, which will be helpful to understand the real problem of a database. The beauty of this stored procedure is that it collects information using almost 15 DMVs but still returns only necessary rows.

Posted on May 13, 2011, in Performance Tuning, Sql Server 2005, Sql Server 2008, SQL Server Community. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: