Monitoring SQL Server activities using sp_WhoIsActive

 Generally, Microsoft provided two system stored procedures called “sp_who” and “sp_who2” to retrieve all currently running processes on the instance.

To get more information Adam Machanic (Microsoft MVP) developed a more powerful stored procedure called “sp_whoisactive” which correlates information from several different SQL Dynamic Management Views (DMVs). It is thus able to present a richer and more detailed set of information than older tools such as sp_who and sp_who2. Because it is so powerful, it is beyond the scope of this article to go into detail about its capabilities.

This article describes how to obtain and use sp_whoisactive. The tool collects information commonly of interest when troubleshooting database issues.     

Installing sp_whoisactive

NOTE: The sp_whoisactive tool is hosted on Github, and is licensed under GPLv3. To install:

1. Download the source code ZIP file from https://github.com/amachanic/sp_whoisactive/releases.
2. Extract the ZIP file to a temporary location.
3. Open the who_is_active. sql script file in SQL Management Studio.
4. Make sure that your master database is selected in the Available databases dropbox.
5. Click Execute.

The sp_whoisactive stored procedure is now installed in your master database.

Using sp_whoisactive

To collect information, execute sp_whoisactive from a query window in Microsoft SQL Management Studio:

exec sp_WhoIsActive

The above command runs the procedure using the default settings. These settings provide an overview of current activity.

To modify the results, override the default settings by passing parameters when the query executes:

The following parameters are the most useful.
 

EXEC sp_WhoIsActive

@get_plans = 1,

@get_outer_command = 1,

@get_transaction_info = 1

The above example displays the results in the Management Studio window.

By default, sp_whoisactive formats some results, such as sql_text, in XML format, which makes them interactive. You can click any given result to open a new query window with the details.       
 
To save the results to a file, do the following to remove the formatting for easier reading:
 

EXEC sp_WhoIsActive

@get_plans = 1,

@get_outer_command = 1,

@get_transaction_info = 1,

@format_output = 0
 

If you want to safe result set to a file, Before you execute the query, right-click anywhere in the query window and select Results To / Results To File. When you execute the query, you are prompted for a location to save the file. After the save completes, right-click in the query window and select Results To / Results To Grid. This action returns the environment to the default setting.