Tools of the trade

So after spending the entire week cursing not so softly to myself about having to make a blog contribution and not really having a clue what I should blog about, it dawned on me.  For most of this week the entire point of this blog has alluded me. It’s only after reading Anél Marais blog contribution that I realized that this is a great way for us to share lessons that we have learnt out in the field with our peers.

So with that in mind I would like to expand on Anél's blog contribution on sp_who2 with another tool that I use myself for monitoring called sp_WhoIsActive

Generally when one is confronted with a question that goes along the lines of “Why is the server slow???” the first port of call generally is the system stored procedure sp_who2. 

Anyone that has ever used sp_who2 will be well aware of its limitations, such as having to sift through a bunch of irrelevant processes to find what you are looking for or  having to issue another command just to find the query text for a given spid.

sp_WhoIsActive to the rescue:

For some time now I have been using sp_WhoIsActive as a replacement for sp_who2.  It is a third party stored procedure that is available for free download and was written by SQL Server MVP Adam Machanic.

What makes this stored procedure stand out from sp_who2 is that you can see the query text for each spid without having to issue another statement. Query plans for each spid can also be returned simply by setting @get_plans to 1 and wait information is also shown for each process.

WhoIsActive does everything sp_who2 does and more:

  

• Online help is available by setting the parameter  @help = 1, which enables the procedure to return commentary and details regarding all of the input parameters and output column names.
• Aggregated wait stats, showing the number of each kind of wait and the minimum, maximum, and average wait times are controlled using the @get_task_info parameter with input values of 0 (don't collect), the default of 1 (lightweight collection mode), and 2 (collect all current waits, with the minimum, maximum, and average wait times).
• Query textis available, including the statements that are currently running, or, by setting@get_outer_command = 1, optionally include the outer batch. In addition, SP_WhoIsActive can pull the execution plan for the active session statement using the @get_plans parameter.
• Deltas of numeric values between the last run and the current run of the script can be assigned using the @delta_interval = N (where N is seconds) parameter.
• Filtered results are available on session, login, database, host, and other columns using simple wildcards similar to the LIKE clause. You can filter to include or exclude values, as well as exclude sleeping SPIDs and system SPIDs so that you can focus on user sessions.
• Transaction details, such as how many transaction log entries have been written for each database, are governed by the @get_transaction_info parameter.
• Blocks and locks are easily revealed using parameters such as  @find_block_leaders, which, when combined with sorting by the [blocked_session_count] column, puts the lead blocking sessions at top. Locks are similarly revealed by setting the @get_locks parameter .
• Long-term data collection is facilitated via a set of features designed for data collection, such as defining schema for output or a destination table to hold the collected data.

System Requirements:

Requires SQL Server 2005 SP1 or later. Users of the stored procedure need VIEW SERVER STATE permissions, which can be granted via a certificate to minimize security issues.

Download:

New versions of the sp_WhoIsActive stored procedure are released at regular intervals. Get the latest version of the stored procedure here.