Rajendra Gupta
sp_who procedure

An overview of the sp_WhoIsActive stored procedure

March 23, 2020 by

This article gives a comprehensive overview of custom stored procedure sp_whoisactive.

Introduction

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.

Sp_who

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.

sp_who procedure

Sp_who2

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.

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

download the latest version of sp_whoisactive from GitHub

Extract the ZIP file, and you can run a SQL Script who_is_active.sql

Extract the ZIP

Open this script in SSMS and execute it. It creates a custom stored procedure sp_WhoIsActive.

execute script in SSMS

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.

  1. The first section shows the version, copyright, feedback email, License, and URL information
  2. In the second section, it shows available parameters, their description, and their default information

  3. The last section shows the columns data types, formatting option, and description:

    Procedure help

Let’s run sp_WhoIsActive without any parameter. Before executing this, open a new query window and execute the following query.

Let’s understand the useful output columns of sp_WhoIsActive stored procedure.

output columns of 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

check SQL_text

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

@find_block_leaders:

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.

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.

@find_block_leaders argument

@get_plans

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.

@get_plans argument

Click on the query plan, and you can see the query execution plan.

query execution plan

@get_locks

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.

@get_locks argument

@get_additional_info

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 argument

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.

extension in Azure Data Studio

Click on Install, and it enables this extension globally.

Installing extension

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.

view default page

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.

Whoisactive: Get plans command

Similarly, for WhoIsActive: Find block leaders, we get the following query with required arguments.

Whoisactive: Find leader of block command

Conclusion

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.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)

Related posts:

  1. How to prepare for the Exam DP-300: Administering Relational Databases on Microsoft Azure
  2. How to prepare for the Exam AZ-900: Microsoft Azure Fundamentals
  3. How to prepare for the Exam DP-200: Implementing an Azure Data Solution
  4. Monitoring activities using sp_WhoIsActive in SQL Server
  5. Top SQL Server Books
168 Views