Hadi Fadlallah
sp_whoisactive embedded documentation

Monitoring activities using sp_WhoIsActive in SQL Server

March 16, 2020 by

In this article, we will talk about sp_WhoIsActive stored procedure and how we can use it to monitor currently running activities in SQL Server.

Introduction

Database administrators (DBAs) are incessantly checking currently running operations over an SQL Server instance especially when the server is slowing down.

In general, Microsoft provided two system stored procedures called “sp_who” and “sp_who2” to retrieve all currently running processes on the instance but they lack much useful information that can facilitate the performance monitoring and analysis process, also they show much useless information (system processes).

For this reason, Adam Machanic (a Microsoft MVP since 2004) developed a more powerful stored procedure called “sp_whoisactive” to fill in the gap between the actual needs of DBAs and the currently provided procedures (sp_who and sp_who2).

In the following sections, we will talk briefly about sp_who and sp_who2 stored procedure, then we will illustrate how to download and use sp_whoisactive stored procedure.

sp_Who and sp_Who2

As we mentioned before, Microsoft provided sp_Who and so_Who2 stored procedures for activity monitoring in SQL Server. In this section, we will explain what is the information returned by each stored procedure and what are the differences between them.

As described in the official documentation, sp_who “provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.”

sp_who returns information such as the session process ID (SPID), the execution context ID (ECID), the process status, the blocking session ID, the database name, the login and hostname associated with this process, and the command type.

sp_who procedure output

Figure – sp_who output

sp_Who2 is similar to sp_Who but it is not documented nor supported but it returns more information and performance counter from the current processes such as the program name executing the command, Disk IO, CPU Time, last batch execution time.

sp_who2 procedure output

Figure – sp_who2 output

As shown in the screenshots above, the output of these procedures is showing all system and user processes running which is not required all the time and the user can only filter using the login name or session ID while he may need to hide system processes. Also, the outputs don’t contain any information about the currently running SQL Command such as start execution time, execution duration, WAIT info and more information.

Download and install sp_whoisactive

To download this procedure, you should go to the website downloads page and select the relevant release or you can do this from the GitHub repository.

Once the download is completed, you should open who_is_active.sql file using SQL Server Management Studio and execute the script.

Using sp_Whoisactive

After installing the procedure, if we execute it, we can see that it only returns running user processes by default and provides the following information for each process:

Column

Description

Shown by sp_who

Shown by sp_who2

dd hh:mm:ss.mss

Process elapsed time

No

No

session_id

The process session id

Yes

Yes

sql_text

The currently running SQL command

No

No

login_name

The login name associated with the process

Yes

Yes

wait_info

The process wait information (aggregated)

No

Yes

CPU

The CPU time

No

Yes

tempdb_allocations

Number of Tempdb writes done

No

No

tempdb_current

Number of Tempdb pages currently allocated

No

No

blocking_session_id

The blocking session Id

Yes

Yes

reads

number of reads done

No

Disk IO

writes

number of writes done

No

Disk IO

physical reads

number of physical reads done

No

Disk IO

used_memory

the amount of memory used

No

No

status

The process status

Yes

Yes

open_tran_count

the number of transactions used

No

No

percent_complete

the query completion percentage

No

No

host_name

The host machine name

Yes

Yes

database_name

The database name where the query is executed

Yes

Yes

program_name

The application that executed the query

No

Yes

start_time

The process start time

No

Yes

login_time

The login time

No

No

request_id

The request Id

Yes

Yes

collection_time

The time that this last select was run

No

No

part one of sp_whoisactive default output

Figure – Procedure output part 1

part two of sp_whoisactive default output

Figure – procedure output part 2

As shown below, to show the system processes you should run the following command:

showing system processes using sp_whoisactive

Figure – Showing system processes

You can visualize more information from this procedure by passing additional parameters such as @get_additional_info, @get_locks, @get_avg_time, and other parameters.

One of the amazing thing about this procedure is that it is well documented and all related information can be obtained by executing the following command:

sp_whoisactive embedded documentation

Figure – Procedure embedded documentation

As shown in the image above, the help command contains three sections:

  1. General information: where general information such as version, website, creator email are provided
  2. Parameters description: where a list of all available parameters with their description is provided
  3. Output columns description: a full list of all available output columns with their descriptions

Saving Historical data

If we need to periodically save running processes information for further analysis, and since all output columns data types and name can be found within the procedure embedded documentation, we can create an SQL agent job that periodically executes sp_whoisactive procedure within an INSERT command such as:

Then we can refer to the stored data later for further analysis.

Resources

If you are looking to learn more about this stored procedure, you can refer to the following links:

Conclusion

In this article, we have described briefly sp_who, sp_who2 and sp_whoisactive procedures and we have explained why sp_whoisactive is more powerful and more needed for Database Administrators.

Hadi Fadlallah
DBAtools, Monitoring, Performance counters, T-SQL

About Hadi Fadlallah

Hadi is an SQL Server professional with more than 10 years of experience. His main expertise is in data integration. He's one of the top ETL and SQL Server Integration Services contributors at Stackoverflow.com . Also, he published several article series about Biml, SSIS features, Search engines, Hadoop, and many other technologies. Besides working with SQL Server, he worked with different data technologies such as NoSQL databases, Hadoop, Apache Spark. He is a MongoDB, Neo4j, and ArangoDB certified professional. On the academic level, Hadi holds two master's degrees in computer science and business computing. Currently, he is a Ph.D. candidate in data science focusing on Big Data quality assessment techniques. Hadi really enjoys learning new things everyday and sharing his knowledge. You can reach him on his personal website. View all posts by Hadi Fadlallah

168 Views