A
sp_whoisactive tool is a diagnostic tool created by
Adam Machanic, which correlates information from several different SQL Dynamic Management Views (DMVs). Thus, it's able to present a richer and more detailed set of information than older tools such as
sp_who and
sp_who2. Because it's so powerful, it's beyond the scope of this article to go into detail about its capabilities.
The
sp_whoisactive tool is an SQL stored procedure that, when executed, returns information about the current SQL activity. You can control the results and their presentation by passing parameters to the procedure when it executes.
This article describes how to obtain and use
sp_whoisactive. The tool collects information commonly of interest when troubleshooting ePO database issues.
Installing sp_whoisactive
NOTE: The
sp_whoisactive tool is hosted on
GitHub and is licensed under
GPLv3. To install, do the following:
- Download the source code ZIP file.
- Extract the ZIP file to a temporary location.
- Open the who_is_active.sql script file in SQL Management Studio.
- Make sure that your ePO database is selected in the Available databases dropbox.
NOTE: If you're using ePO 5.10.x, make sure that you select the main ePO database, and not the separate events database.
- Click Execute.
The
sp_whoisactive stored procedure is now installed in your ePO database.
Using sp_whoisactive
To collect information, execute
sp_whoisactive from a query window in Microsoft SQL Management Studio:
exec sp_WhoIsActive
The command above 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:
NOTE: It's beyond the scope of this article to detail all supported parameters. But, for most cases, the following parameters are the most useful.
EXEC sp_WhoIsActive
@get_plans = 1,
@get_outer_command = 1,
@get_transaction_info = 1
The example above 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. But, the XML formatting makes the results hard to read if you save the results to a file. You might need to save the results to a file if Technical Support requests the file.
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
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're 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.
