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 “).
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:
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.
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.
Benefits: SP_WhoIsActive provides detailed information about all of the sessions running on your SQL Server system, including what they’re doing and how they’re impacting server behavior.
System Requirements: SQL Server 2005 SP1 and later; users need VIEW SERVER STATE permissions
How to Get It: You can download SP_WhoIsActive from sqlblog.com/tags/Who+is+Active/default.aspx .