SQL Server - Using SP WhoIsActive to identify Locks, Blocks, Slow Queries, Running Queries, and more

Views: 10.318
Reading Time: 10 minutes

Hello people,
Good night!

In this post I will demonstrate a tool used by 99.99% of SQL Server DBA's around the world and you probably already know it, which is the excellent and famous stored procedure. sp_WhoIsActive, by Adam Machanic, which allows us to get a lot of information about the active sessions of a SQL Server instance such as the query being executed, the user who is executing, the wait event, runtime, CPU usage, usage Tempdb, disk reads (IO), and more.

The purpose of this post is to demonstrate this tool and how different parameters and customizations change the final result of the Stored Procedure. Nowadays, many DBAs always use this SP with the default parameters, either due to lack of knowledge about the parameters or even their existence.

Before starting, I will make available here the latest version of sp_WhoIsActive for you to download if you are unable to download it from Adam Machanic's website.

UPDATE: On 25 / 07 / 2017 I released a query that would be a "lite" version of sp_WhoIsActive. Check it out this post.

If you are visiting this article for performance tips, please also read my article series on this subject:
Understanding Index Functioning in SQL Server
SQL Server - Performance Comparison between Scalar Function and CLR Scalar Function
SQL Server - Introduction to Performance Tuning Study
SQL Server - How to Identify a Slow or Heavy Query in Your Database

I hope you enjoy this series 🙂

A little more about sp_WhoIsActive

Description of the columns

Description of the columns

Before showing what changes with each parameter used, I will show you what each column of this SP returns to us:

ColumnDescription
dd hh: mm: ss: mssColumn that tells you how long the query has been running (for active sessions) or how long the last statement was executed by the session (for inactive sessions - sleeping)
session_idNumber of the session running the query (SPID)
sql_textXML that contains a snippet of the query that is running (or the entire query if it's just a statement)
login_nameName of DOMAIN \ USER running this query
wait_infoIf the session has a wait event, tell how many milliseconds this event is occurring and what type of event (Ex: LCK_M_S, CXPACKET, OLEDB, etc)
CPUMeasuring the number of CPU cycles used by the session (a very high number means that this session has already used a lot of server CPU but does not mean it is currently using it)
tempdb_allocationsNumber of TempDB pages (8 KB each page) that have already been allocated for this session through temporary tables, spools, LOBs, etc.).

A very high number here means that this session has too many pages allocated, but it does not mean that it is the cause of TempDB Autogrow events.
tempdb_currentNumber of TempDB pages currently being allocated by this session. This account comes down to number of pages allocated - number of deallocated pages from TempDB.

A very high number here means that it is a possible cause of Autogrow events in TempDB.
blocking_session_idDisplays the number of the session blocking the parsed session (generating a wait LCK event in that session)
readsNumber of 8 KB logical pages read from server memory (quick read)
writesNumber of 8 KB physical pages written to server disk
physical_readsNumber of 8 KB physical pages read on server disk (slow read)
used_memoryNumber of 8 KB pages used from server memory by combining procedure cache memory and workspace memory grant.
statusDefines the current query execution situation, which can be one of the values ​​below:

Running: Means that the session is active, executing one or more batches. This state means that the session is connected to the database, has already sent commands to the server, and is waiting to be processed by SQL Server.

Suspended: This state means that the session is not active as it is waiting for some server resource (I / O, Network, etc.). When this feature is released, the session becomes active again and returns processing.

Runnable: This state means that the session has already been assigned to a processor worker thread, but is unable to send to the CPU to execute. If you are experiencing this event too often and for too long in your environment, it may mean that you need to increase your server processor or decrease the parallelism of running queries (read MAXDOP), which may be occupying all cores. .

Pending: This state means that the session is ready and waiting for a processor worker thread to get it to execute. Importantly, this does not mean that you need to increase the "Max. Worker threads" parameter, you may need to check what other threads are doing and why they are not running.

background: The request is running in the background, usually used by Resource Monitor or Deadlock Monitor.

Sleeping: The session is open and connected to the bank, but has no request to process.
open_tran_countColumn taken from the deprecated view sysprocesses, which allows you to view how many active open transactions the session is using and how deep the nesting level of these transactions is.
percent_completeDisplays how many% completed of long queries (ALTER INDEX REORGANIZE, AUTO_SHRINK option with ALTER DATABASE, BACKUP DATABASE, DBCC CHECKDB, DBCC CHECKFILEGROUP, DBCC CHECKTABLE, DBCC SHRINKDATABASE, DBCC SHRINKFILE, RECOVERY, REST DATEBASE
host_nameName of the physical machine the connection is coming from
database_nameName of the current database of the connection, where queries are being sent.
program_nameName of software used during connection (Ex: Microsoft SQL Server Management Studio - Query)
start_timeShows the date when the query started running
login_timeShows the date the session was logged in to the instance.
request_idNumber of the current session request. This column does not have a very clear interpretation of its usefulness. When session status is "sleeping", the value of request_id is usually NULL, otherwise it is 0 (zero). If you find a value greater than 0 (zero) in the request_id column, it means that this session is running more than one batch simultaneously using multiple active result sets (MARS).
collection_timeShows data collection date (sp_WhoIsActive run date)

These columns can be grouped into 4 categories:

Time and status

  • [dd hh: mm: ss.mss]
  • [start_time]
  • [percent_complete]
  • [collection_time]
  • [status]

Session and user identifiers

  • [session_id]
  • [request_id]
  • [login_name]
  • [host_name]
  • [database_name]
  • [program_name]

Factors That May Cause Slow Query

  • [wait_info]
  • [blocking_session_id]

Resource usage of your session

  • [sql_text]
  • [CPU]
  • [tempdb_allocations]
  • [tempdb_current]
  • [reads]
  • [writes]
  • [physical_reads]
  • [used_memory]
  • [open_tran_count]
Using sp_WhoIsActive Help

Seeing sp_WhoIsActive Help

This tool has its own help, and we can access it using the @help = 1 parameter:

and the output is divided into 3 resultsets:

SP Creator Information
SQL Server - sp_WhoIsActive - Help 1

Description of SP Call Parameters
SQL Server - sp_WhoIsActive - Help 2

Description of columns returned by SP
SQL Server - sp_WhoIsActive - Help 3

Minimum permissions required to run SP

Minimum permissions required to run SP

One of the important points to note is the minimum permissions required to use sp_WhoIsActive. I have seen many DBA's grant a sysadmin access to a user, so that he can use this SP, because he does not know what is necessary to allow the user to perform the Stored Procedure on the instance, since the most common is trying to release only the EXECUTE permission on the Stored Procedure.

If you do not release the view server state permission, you will see the error message below:
sql-server-viewserverstate-permissions-denied

To do this, simply execute the command below so that the user can use sp_WhoIsActive normally:

Using parameters in sp_WhoIsActive

Standard execution, without parameters
If you do not use any parameters, this is the view you get when executing sp_WhoIsActive. As there are many columns, I had to cut the result in 2 images.

The 1:
SQL Server - sp_WhoIsActive - Default 1

Part 2
SQL Server - sp_WhoIsActive - Default 2

Using filters on sp_WhoIsActive
A very nice feature of this tool is the ability to filter the results without exporting to a physical table and then filtering. For this we can use the filter parameters of SP itself.

The @filter parameter allows us to define what we want to fetch (supports wildcard% to perform searches like LIKE '% string%'), while the @filter_type parameter allows us to define where we want to fetch this information. Possible types for @filter_type are:

  • Session: Allows you to search for a specific session.
  • program: Allows you to search for sessions using a particular client software to connect to the database.
  • database: This type of filter is used to filter queries running on a given database.
  • login: Filter used to filter the sessions of a particular user.
  • host: Use this filter to view only sessions coming from a specific hostname

Examples of using inclusive filters (@filter and @filter_type):
SQL Server - sp_WhoIsActive Filter 1

Examples of using unique filters (@not_filter and @not_filter_type):
SQL Server - sp_WhoIsActive Filter 2

Viewing your session information, system sessions, and idle sessions
By detailing more parameters of this SP, I will demonstrate the usefulness of the @show_own_spid, @show_system_spids and @show_sleeping_spids parameters.

The @show_own_spid (BIT) parameter determines whether the session performing the procedure itself will be part of the final result that will be shown on the screen. The default value is 0 (zero), making the session itself not shown by default.

The @show_system_spids (BIT) parameter determines whether SQL Server internal system sessions will be displayed in the SP end result. The default value is 0 (zero), causing these sessions to be skipped.

The @show_sleeping_spids (TINYINT) parameter determines whether sleeping sessions will be displayed in the SP end result. The default value is 0 (zero), causing these sessions to be skipped. The 1 value displays all inactive sessions that have transaction open and the 2 value displays all inactive sessions.

Examples of use:
SQL Server - sp_WhoIsActive show_own_pid show_system_spids show_sleeping_spids

Returning Additional Information
The end result of this stored procedure is very interesting, and with these parameters will be even more complete. The default value of all these BIT type parameters is 0 (zero), causing none of them to be shown (unless you change it to 1).

@get_full_inner_text

By default, the SQL statement that is returned in XML form in the sql_text column is just the batch currently being processed. Using this parameter, we can see all the batch content that has been sent for SQL Server processing.

SQL Server - sp_WhoIsActive get_full_inner_text

SQL Server - sp_WhoIsActive get_full_inner_text2

@get_plans

Using this parameter with the value 1 will generate a demonstration of the current query execution plan for each session returned by this SP. Using the 2 value in this parameter, the execution plan of the entire session query is generated. When you click the ResultSet XML, Management Studio already displays the execution plan for this query. Fantastic!

SQL Server - sp_WhoIsActive get_plans

SQL Server - sp_WhoIsActive get_plans example

@get_outer_command

This parameter is similar to @get_full_inner_text, but instead of overriding the sql_text column value, it keeps this column at its default value (just the running snippet) and adds a new column named sql_command, which contains the entire query that the session is running. In this way we have both views.

SQL Server - sp_WhoIsActive get_outer_command

@get_transaction_info

Using this parameter, we can view the amount and volume of data written to the transaction log for each session.

SQL Server - sp_WhoIsActive get_transaction_info

@get_task_info

A very interesting parameter for performance analysis, @get_task_info allows you to view more information about running sessions. By using the 1 value, we can view the largest wait events (other than CXPACKET).

Using the 2 parameter, we will see the full mode, which includes the columns:

  • physicial_io: Shows physical read / write (I / O) numbers on disk
  • context_switches: Shows the number of context changes for the active connection. A context change is when the OS kernel swaps the processor from one thread to another (eg a higher priority thread).

    This indicator is very important in identifying if one process is using more CPU than other processes and preventing them from reaching the processor. A very high index means that a lot of concurrency is occurring on the processor and it may be overloaded. A low number means that some process is allocating more CPU than it should, generating a lot of wait time (and probably sessions with status Pending and Runnable).

    Expected values ​​should be below 2.000 per processor / second exchanges (some DBA's consider a value below 5.000 as acceptable). Very high values ​​may be caused by physical memory (RAM) allocation failures. Another potential aggravation is Intel® Hyper-Threading Technology, which in some cases can cause many context changes due to the simulation of virtual cores. If you are experiencing this problem, a good test is to disable this feature on the server motherboard and perform performance testing.

  • tasks: Number of tasks being used by the current execution.

@get_locks

Very useful parameter for maintaining and identifying locks in the instance. When enabled, shows the reserved objects of each request, as well as the type of lock requested by the session.

SQL Server - sp_WhoIsActive get_locks

SQL Server - sp_WhoIsActive get_locks xml

@get_avg_time

Using this parameter, a new column appears in the final result (dd hh: mm: ss.mss (avg)). This column shows the average execution time of the current query running for each session. As you can see from the example, my query has been running for more than 2h, but the current snippet is taking, on average, 79 ms, in a loop of 850.000 iterations. This time is estimated based on the plan and execution histories.

SQL Server - sp_WhoIsActive get_avg_time

@get_additional_info

Using this parameter will create a new column in the final result called “additional_info”, which is an XML with various SET command information and definitions from each session, as shown in the example below:

SQL Server - sp_WhoIsActive get_additional_info

SQL Server - sp_WhoIsActive get_additional_info xml

If any SQL Server Agent jobs are running, the additional_info column of this session that Job opened will have Job information:
SQL Server - sp_WhoIsActive get_additional_info xml agent_job_info

If you use the @get_task_info = 2 and @get_additional_info = 1 parameters and there is a lock in some session, the XML in the “additional_info” column of this session that is in lock will have a node called block_info with block information:
SQL Server - sp_WhoIsActive get_additional_info xml block_info

@find_block_leaders

One of my favorite parameters, @find_block_leaders when enabled, allows me to analyze each session and count how many other sessions are in lock waiting for objects to be released for that session. Do you know when lock events start on your production instance and you have to keep looking for who is causing these locks? This parameter is the solution for you.

SQL Server - sp_WhoIsActive find_block_leaders

@delta_interval

This interesting feature is for you to be able to perform two data collections over a given period of time (this period is the parameter value, in seconds) and to analyze the difference in tempdb allocation, readings, writes, etc. between the two. collections performed.

In the example below, I specified an interval of 10 seconds between each collection. At the end of 10 seconds, columns will be created with the suffix “_delta”, demonstrating the difference between the first and second run.

This is very useful for analyzing tempdb allocation growth or realtime disk reads. Often, analyzing just the total and current session allocation is not enough to estimate resource growth and allocation, making this feature very interesting for DBAs.
SQL Server - sp_WhoIsActive delta_interval

Formatting data output
In addition to being very complete, this SP allows us to customize in many ways the end result and output generated. I will now demonstrate how to do this.

@output_column_list

As I have shown in some examples above, this parameter is used to define which columns should be part of the final result of SP execution.

SQL Server - sp_WhoIsActive output_column_list

@sort_order

As its name suggests, this parameter is used to sort the results according to your needs, where you choose which columns to use for sorting and which criteria (asc or desc).
SQL Server - sp_WhoIsActive sort_order

@format_output

This parameter is used to change the display of some columns to a more “human” reading mode. With the value 1, the output format will use variable length fonts. With the value 2, the output format will use fixed length fonts.

SQL Server - sp_WhoIsActive format_output

Being honest, I see differences between the 0 and 1 values, but I don't see any differences between the 1 and 2 values.

@return_schema and @schema

These parameters together serve to generate the SP result creation script. The @return_schema parameter when set to 1, instead of returning the execution result, generates the result's CREATE TABLE script. This script should be read using an OUTPUT variable in the @schema parameter, as shown below:
SQL Server - sp_WhoIsActive return_schema schema

@destination_table

And lastly, we have the @destination_table parameter. It serves to insert the result of the execution of SP in a physical table, where we can store history and consult when we want.

To use this parameter, the table must be previously created, because this parameter will only insert the data, will not create the table. To get the CREATE TABLE command that results from running this SP, just look at the parameters explained above (@return_schema and @schema) to do it easily and within seconds.

SQL Server - sp_WhoIsActive destination_table

That's it folks!
I hope this post is helpful to you.