This article gives a comprehensive overview of custom stored procedure sp_whoisactive.
Usually, DBAs use sp_who, and sp_who2 system stored procedures to view the current sessions, users, and processes in a SQL instance. We can also identify blocking and active sessions from these procedures. We can use dynamic management views (DMVs) and dynamic management functions (DMFs) to get detailed internal useful information about SQL Server processes, waits, memory, and CPU.
We use sp_who procedure to get user and background process information. It gives necessary information such as login, hostnames, command, blocking, and database against which it is running.
It gives more information than the sp_who procedure. The DBA mainly uses this stored procedure for general information about the processes. It is an undocumented procedure but still useful. We get additional columns such as CPUTime, DiskIO, LastBatch, and ProgramName from this stored procedure.
These procedures do not give much useful information such as wait information, execution plan, current running statements, duration. Now, let me introduce another useful stored procedure sp_WhoIsActive to get an instant view of SQL Server user processes. It is developed by Microsoft MVP Adam Machanic. We can use this stored procedure from SQL Server 2005 onwards. You can refer to the official documentation on whoisactive. It collects data from various DMV’s and shows information in a tabular format.
It is a custom stored procedure. We can download the latest version from GitHub. The current version is 11.33. Open the URL and download the ZIP version of it.
Extract the ZIP file, and you can run a SQL Script who_is_active.sql
Open this script in SSMS and execute it. It creates a custom stored procedure sp_WhoIsActive.
It is always a best practice to check documentation before using the procedure. We can execute this with @help = 1 argument to get the following information.
- The first section shows the version, copyright, feedback email, License, and URL information
- In the second section, it shows available parameters, their description, and their default information
The last section shows the columns data types, formatting option, and description:
Let’s run sp_WhoIsActive without any parameter. Before executing this, open a new query window and execute the following query.
/****** Script for SelectTopNRows command from SSMS ******/
SELECT TOP (1000) [EmployeeID]
Let’s understand the useful output columns of sp_WhoIsActive stored procedure.
- dd hh:mm:ss:mss: It shows the duration of the command. We can use this column to identify long-running transactions. For example, we can identify SQL Server sessions running from more than 1 hr
- It shows query running time for an active request
- In case of a sleeping session, it shows the time since the last completed batch
- session_id: It is the SP ID of the user session
- Sql_text: We can find out the SQL text for the running session. It is a hyperlink. We click on it and get the complete t-SQL
- Login name: It is the login that is connected to SQL Server and executing the SQL specified in the session
- Wait_info: It is a useful column to identify current wait for the SPID such as CXPACKET, ASYNC_NETWORK_IO along with wait time. Refer to the article for SQL Server wait types
- Tempdb_allocations and tempdb_current: If a query is using the TempDB database, we get the information about tempdb allocations using this column. For example, if we use a table variable or temporary table, it gets created in the TempDB, and we can track information using these columns
- CPU: We get the total CPU time consumed by the query from this
- Blocking_session_id: In the case of blocking, we can get the blocking session-id from this column
- Reads and writes: It gives the number of reads and writes for the current query
- Open_tran_count: It shows the number of open transactions for the session
- Percent complete: We can check the percentage completion status of the few commands using the DMV sys.dm_exec_requests, such as backup and restore database commands. Sp_WhoIsActive uses this DMV information and displays it the output
- Program name: It gives the application name from where the user is connected, such as Microsoft SQL Server Management Studio – Query, Azure Data Studio, SQL Server Agent
Let’s look at a few optional parameters and their usage with sp_WhoIsActive procedure.
We can set value for @find_block_leaders argument to 1 and sorts the results for blocked_session_count column to check lead blockers and blocked sessions.
@find_block_leaders = 1,
@sort_order = '[blocked_session_count] DESC'
In the output, we see session-id 58 is the lead blocker, and it blocked 2 sessions that are listed below in the output table.
Suppose you are investigating a performance issue in SQL Server and you identified a problematic query. It is good if we can get an execution plan of it for looking at costly operators involved in query execution.
We can specify the argument @get_plans=1, and it includes an additional column in the output with the XML execution plan.
EXEC sp_WhoIsActive @get_plans = 1;
Click on the query plan, and you can see the query execution plan.
We can use this argument to get an XML snippet to get detail information about locks held in a session of SQL Server. In the output, we get an additional column locks, as shown below. Click on the locks, and you get XML format locks information.
We can set several session parameters that might affect query performance as well. Sp_WhoIsActive gives you an argument @get_additional_info and shows information about those parameters.
@get_additional_info = 1
Sp_WhoIsActive extension in Azure Data Studio
Azure Data Studio is a new cross-platform client tool suitable for both developers and DBAs. Extensions play an important role in Azure Data Studio as they enhance functionalities of it. We can install the extensions from the market place and use it as per our requirement. You can explore a few useful Azure Data Studio extensions on SQLShack.
It contains an extension for sp_whoisactive and gives output in graphical format. Currently, it is in the preview phase.
Click on Install, and it enables this extension globally.
Now, connect to SQL instance in Azure Data Studio. Right-click on the database and select Manage. You get the following default page as output.
In the tasks, we can click on the respective block, and it automatically gives you a query with the required parameter.
For example, if we click on WhoIsActive: Get plans, it gives the following query.
Similarly, for WhoIsActive: Find block leaders, we get the following query with required arguments.
Sp_WhoIsActive is a useful custom stored procedure and gives important insights from the user sessions in SQL Server with information such as lead blocker, execution plan, wait stats, query text. I would recommend you installing this stored procedure and be familiar with it. You can also use it in Azure Data Studio using the extension, as shown in this article.
- Identify and remediate non-compliant Azure SQL Databases for auditing on SQL Server - September 16, 2021
- Azure Alerts for creating, modifying and deleting Azure SQL Databases - September 13, 2021
- Azure Automation: Automatic scaling Azure SQL databases based on CPU usage threshold - September 10, 2021