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_WhoIsActiveDescription 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:
|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]
Session and user identifiers
Factors That May Cause Slow Query
Resource usage of your session
Seeing sp_WhoIsActive Help
This tool has its own help, and we can access it using the @help = 1 parameter:
EXEC sp_whoIsActive @help = 1
and the output is divided into 3 resultsets:
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.
To do this, simply execute the command below so that the user can use sp_WhoIsActive normally:
GRANT EXECUTE ON dbo.sp_WhoIsActive TO [dominio\usuario]
GRANT VIEW SERVER STATE TO [dominio\usuario]
Using parameters in sp_WhoIsActiveStandard execution, without parameters
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
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.
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.
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!
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.
Using this parameter, we can view the amount and volume of data written to the transaction log for each session.
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.
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.
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.
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:
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:
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.
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.
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.
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.
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:
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.
That's it folks!
I hope this post is helpful to you.