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

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:

Column	Description
dd hh: mm: ss: mss	Column 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_id	Number of the session running the query (SPID)
sql_text	XML that contains a snippet of the query that is running (or the entire query if it's just a statement)
login_name	Name of DOMAIN \ USER running this query
wait_info	If 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)
CPU	Measuring 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_allocations	Number 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_current	Number 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_id	Displays the number of the session blocking the parsed session (generating a wait LCK event in that session)
reads	Number of 8 KB logical pages read from server memory (quick read)
writes	Number of 8 KB physical pages written to server disk
physical_reads	Number of 8 KB physical pages read on server disk (slow read)
used_memory	Number of 8 KB pages used from server memory by combining procedure cache memory and workspace memory grant.
status	Defines 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_count	Column 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_complete	Displays 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_name	Name of the physical machine the connection is coming from
database_name	Name of the current database of the connection, where queries are being sent.
program_name	Name of software used during connection (Ex: Microsoft SQL Server Management Studio - Query)
start_time	Shows the date when the query started running
login_time	Shows the date the session was logged in to the instance.
request_id	Number 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_time	Shows 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]

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

Description of SP Call Parameters

Description of columns returned by SP

Minimum permissions required to run SP

One of the important points to comment on is the minimum permissions required to use sp_WhoIsActive. I have seen many DBA's grant sysadmin access to a user, so that he can use this SP, because he does not know what it is necessary to release for the user to execute the Stored Procedure in the instance, since the most common is to try 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:

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

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.

Part 1:

Step 2 —

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 search for (supports the wildcard% to perform searches like LIKE '% string%'), while the @filter_type parameter allows us to define where we want to search for this information. The 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.
• : 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):

Examples of using unique filters (@not_filter and @not_filter_type):

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:

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.

@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!

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

@get_transaction_info

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

@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 exchanges per processor / second (some DBA's consider a value below 5.000 to be acceptable). Too high values ​​may be caused by physical memory (RAM) allocation failures. Another possible aggravating factor is Intel® Hyper-Threading technology, which in some cases can cause many changes of context due to the simulation of virtual cores. If you are experiencing this problem, a good test is to disable this feature on the server's motherboard and perform performance tests.

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

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

@get_additional_info

Using this parameter, a new column will be created in the final result called “additional_info”, which is XML with various information and definitions of SET commands for each session, as shown in the example below:

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

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

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

@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 with the suffix “_delta” will be created, demonstrating the difference between the first and the second execution.

This is very useful for analyzing growth of tempdb allocation or disk readings in real time. Often, analyzing only the total allocation of the session and the current one is not enough to estimate the growth and allocation of resources, making this resource very interesting for DBA's.

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.

@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).

@format_output

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

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:

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