SP_WhoIsActive

To say I like SP_WhoIsActive is an understatement. This is probably the most useful and effective stored procedure I’ve ever encountered for activity monitoring. The purpose of the SP_WhoIsActive stored procedure is to give DBAs and developers as much performance and workload data about SQL Server’s internal workings as possible, while retaining both flexibility and security. It was written by Boston-area consultant and writer Adam Machanic, who is also a long-time SQL Server MVP, a founder of SQLBlog.com, and one of the most elite individuals who are qualified to teach the Microsoft Certified Master classes.

Adam, who has exhaustive knowledge of SQL Server internals, knew that he could get more detailed information about SQL Server performance than what was offered natively through default stored procedures, such as SP_WHO2 and SP_LOCK, and SQL Server Management Studio (SSMS). Therefore, he wrote the SP_WhoIsActive stored procedure to quickly retrieve information about users’ sessions and activities. Let’s look at SP_WhoIsActive’s most important features. (See also, "Secerno DataWall 3.3 Offers Stored Procedure Monitoring and Enhanced User role Auditing").

Key Parameters

SP_WhoIsActive does almost everything you’d expect from an activity-monitoring stored procedure, such as displaying active SPIDs, transactions, and locking and blocking, but it also does a variety of things that you aren’t typically able to do unless you buy a commercial activity-monitoring solution. One key feature of the script is flexibility, so you can enable or disable (or even specify different levels of information for) any of the following parameters:

• 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.

SP_WhoIsActive is the epitome of good T-SQL coding practices. I encourage you to spend a little time perusing the code. You’ll note, from beginning to end, the strong internal documentation, intuitive and readable naming of variables, and help-style comments describing all parameters and output columns. The procedure is completely safe against SQL injection attacks as well, since it parses input parameter values to a list of allowable and validated values.

System Requirements

Adam releases new versions of the SP_WhoIs Active stored procedure at regular intervals. Get the latest version of the stored procedure at tinyurl.com/WhoIsActive.

SP_WhoIsActive 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.