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.
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.
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 |
Figure – Procedure output part 1
Figure – procedure output part 2
As shown below, to show the system processes you should run the following command:
|
1 |
Exec sp_whoisactive @show_system_spids = 1 |
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:
|
1 |
Exec sp_whoisactive @help = 1 |
Figure – Procedure embedded documentation
As shown in the image above, the help command contains three sections:
- General information: where general information such as version, website, creator email are provided
- Parameters description: where a list of all available parameters with their description is provided
- 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:
|
1 |
Insert Into [dbo].[HistInformation] Exec sp_whoisactive |
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:
- The Whoisactive official documentation
- Brent Ozar: How to Use sp_WhoIsActive to Find Slow SQL Server Queries
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.
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
- An overview of SQL Server monitoring tools - December 12, 2023
- Different methods for monitoring MongoDB databases - June 14, 2023
- Learn SQL: Insert multiple rows commands - March 6, 2023