SP_WhoIsActive Data Collection and Analysis

By: Jeffrey Yao   |   Comments (4)   |   Related: More > Monitoring

Problem

I use sp_whoisactive frequently to troubleshoot performance issues, but sometimes when I go to run sp_whoisactive after knowing about a performance issue, the issue is already gone. So is there a way that I can collect sp_whoisactive automatically and can I do analysis on the collected information later?

Solution

Assuming sp_whoisactive is installed, we can learn more about sp_whoisactive by running it with parameter @help=1, like the following.

EXEC sp_whoisactive @help = 1;

You will get:

You can see three sections:

1. Header information about version, copyright and contact information
2. Parameters with default values and the brief description of each parameter
3. Columns that can be returned, column data types and brief description 

Due to the complex dynamic SQL statements used in this stored procedure, we cannot use the typical way to insert what was returned from sp_whoisactive into a table directly, like the following:

-- define a table, let's call it #XYZ
CREATE TABLE #xyz ( [dd hh:mm:ss.mss] varchar(15) 
,[session_id] smallint ,[sql_text] xml ,[login_name] nvarchar(128) 
,[wait_info] nvarchar(4000) ,[CPU] varchar(30) ,[tempdb_allocations] varchar(30) 
,[tempdb_current] varchar(30) ,[blocking_session_id] smallint ,[reads] varchar(30) 
,[writes] varchar(30) ,[physical_reads] varchar(30) ,[used_memory] varchar(30) 
,[status] varchar(30) ,[open_tran_count] varchar(30) ,[percent_complete] varchar(30) 
,[host_name] nvarchar(128) ,[database_name] nvarchar(128) ,[program_name] nvarchar(128) 
,[start_time] datetime ,[login_time] datetime ,[request_id] int 
,[collection_time] datetime )
go
-- we cannot do this way to collect the result
insert into #XYZ 
exec sp_whoisactive;

If we run the above statements, we will get the following error:

Fortunately, sp_whoisactive has a function built in for this and here are the key steps.

1. Generate Table Creation Script

The following command will generate the code need to create the table that is needed to store the data.

declare @table_creation_script varchar(max);
exec sp_whoisactive @Schema = @table_creation_script output, @return_schema=1;
print @table_creation_script;

If you run this script, you will get a printout like the following (this has been reformatted or easier reading):

CREATE TABLE <table_name>
( 
   [dd hh:mm:ss.mss] varchar(8000) NULL,
   [session_id] smallint NOT NULL,
   [sql_text] xml NULL,
   [login_name] nvarchar(128) NOT NULL,
   [wait_info] nvarchar(4000) NULL,
   [CPU] varchar(30) NULL,
   [tempdb_allocations] varchar(30) NULL,
   [tempdb_current] varchar(30) NULL,
   [blocking_session_id] smallint NULL,
   [reads] varchar(30) NULL,
   [writes] varchar(30) NULL,
   [physical_reads] varchar(30) NULL,
   [used_memory] varchar(30) NULL,
   [status] varchar(30) NOT NULL,
   [open_tran_count] varchar(30) NULL,
   [percent_complete] varchar(30) NULL,
   [host_name] nvarchar(128) NULL,
   [database_name] nvarchar(128) NULL,
   [program_name] nvarchar(128) NULL,
   [start_time] datetime NOT NULL,
   [login_time] datetime NULL,
   [request_id] int NULL,
   [collection_time] datetime NOT NULL
)

All we need to do is to replace the string "<table name>" to a real table name of our choice, I use "tbl_Whoisactive".

But this table creation script contains lots of columns, which are default columns when you run sp_whoisactive without any parameters, but we may not necessarily need all the information. On the other hand, the default list may not contain what we need, for example, I may not care about [request_id] or [open_tran_count], but I do need a column that can tell which session is the head of the blocking chain in a multiple session blocking scenario.

So, I define the column list I want and then regenerate the table creation script. Here is my code:

-- generate a script to create a table that can save sp_whoisactive result
declare @table_creation_script varchar(max);
exec sp_whoisactive   @get_outer_command=1
, @output_column_list = '[dd%][session_id][sql_command][sql_text][login_name][host_name][database_name][wait_info][blocking_session_id][blocked_session_count][percent_complete][cpu][used_memory][reads][writes][program_name][collection_time]'
, @find_block_leaders=1
, @Schema = @table_creation_script output,  @return_schema=1;
print @table_creation_script;

Once this is run, I get a table creation script as follows (I formatted the lines for easier reading and also modified the varchar length for a few columns, to which I added comments), the important note is that I can add a primary key column at the end of the column list without impacting the table to be used as a parameter with sp_whoisactive.

CREATE TABLE dbo.tbl_Whoisactive ( 
[dd hh:mm:ss.mss] varchar(20) NULL -- change from varchar(8000) to varchar(20)
,[session_id] smallint NOT NULL 
,[sql_command] xml NULL
,[sql_text] xml NULL
,[login_name] nvarchar(128) NOT NULL
,[host_name] nvarchar(128) NULL
,[database_name] nvarchar(128) NULL
,[wait_info] nvarchar(1000) NULL -- change from nvarchar(4000) to nvarchar(1000)
,[blocking_session_id] smallint NULL
,[blocked_session_count] varchar(30) NULL
,[percent_complete] varchar(30) NULL
,[CPU] varchar(30) NULL
,[used_memory] varchar(30) NULL
,[reads] varchar(30) NULL
,[writes] varchar(30) NULL
,[program_name] nvarchar(128) NULL
,[collection_time] datetime NOT NULL -- better create an index on this column
,id bigint identity primary key -- we can add one and only one PK column here
); 

2. Table Creation Script is Ready, Now Run Some Tests

Assuming I run the above table creation script in a database named [MSSQLTips], and it will create table dbo.[tbl_Whoisactive], and then I can run a test to see how it goes.

In my environment (SQL Server 2016 SP2 CU11), I randomly started three sessions with SQL Server Management Studio (SSMS) and run my test code in the following sequence.

For spid=75, I have the following code, which is to generate a non-committed transaction so it can block other sessions (for demonstration purpose).

-- create a test table with two rows
USE MSSQLTIPS
drop table if exists dbo.t;
 
CREATE TABLE DBO.t (id int  primary key, a varchar(200));
insert into dbo.t (id, a) values (1,'hello'), (2, 'world');
-- start a transaction without committment
begin tran 
update dbo.t set a='hello 2' where id = 1;
-- rollback tran

For spid=71 session, I run a deletion as follows, which will be blocked by the previous spid 75.

delete from dbo.t

For spid=51 session, I run a select statement as follows, which is blocked by the previous spid=71.

select from dbo.t

Finally, I can run the following code to collect the result into to the table [tbl_whoisactive] as shown below.

-- fig-1 code
exec sp_whoisactive   @get_outer_command=1
,  @output_column_list = '[dd%][session_id][sql_command][sql_text][login_name][host_name][database_name][wait_info][blocking_session_id][blocked_session_count][percent_complete][cpu][used_memory][reads][writes][program_name][collection_time]'
, @find_block_leaders=1
, @destination_table = 'dbo.tbl_whoisactive';
 
select [dd hh:mm:ss.mss]
, session_id, sql_text
, database_name, wait_info
, blocking_session_id, blocked_session_count 
from dbo.tbl_whoisactive;

Here is what I received:

There are two columns of interest.

1. [blocking_session_id] indicates the blocking session id that blocks the current spid as shown in column [session_id], so in our case, we can see that spid 51 is blocked by spid 71, while spid 71 is further blocked by spid 75.
2. [blocked_session_count] can help to find the head of the blocking chain, in my test case, we can see spid 75 is the head of the blocking chain because its [blocked_session_count] > 0 while [blocking_session_id] = null, i.e. if session [X] is not blocked by another session but it's blocking others, it means session [X] is the head of a blocking chain.

We can use this T-SQL to find such session very easily.

-- find the head of all blocking chains
select [dd hh:mm:ss.mss]
, session_id, sql_text
, database_name, wait_info
, blocking_session_id, blocked_session_count 
from dbo.tbl_whoisactive
where blocked_session_count > 0
and blocking_session_id is null;

The result is:

3. Schedule a Job to Run Collection Script

We can schedule a job to collect the data using the code in Fig-1.

The job can actually have two steps, the first step is to collect data and the second step is to analyze the collection based on the business rules, which I will discuss in the next section, and if needed, send out email alerts.

The job schedule can be flexible from every [X] seconds to every [Y] minutes, or we may even have two different schedules, for example, from 7am to 7pm, we run the job every 30 seconds because it is core business hours, and we want to know better what is running inside our databases, but from 7pm to 7am, i.e. non core business hours, the job can be run every 3 minutes.

Analyze Collection Result

From the collected data, we can do lots of checks, for example I have the following list:

• Is there any SQL statement running longer than a specified threshold?
• Is there any SQL statement running with CPU/reads/writes more than a specified threshold?
• Is the total number of blocked sessions exceeding a specified threshold?

use mssqltips
 
--1. from the last collection, find sessions running longer than a specificed threshold
declare @duration_thresold varchar(15)='00 00:02:00' --2 min (example only, choose your own value)
select * from dbo.tbl_Whoisactive
where collection_time = (select max(collection_time) from dbo.tbl_Whoisactive)
and [dd hh:mm:ss.mss] > @duration_thresold
 
if (@@rowcount > 0)
exec msdb.dbo.sp_send_dbmail ....  -- please add your own parameters
go
 
--2. from the last collection, find sessions with cpu/reads/writes exceeing defined thresholds
declare @cpu_threshold int = 1000; --define your own
declare @reads_threshold int = 1000, @writes_threshold int = 1000;
select * from dbo.tbl_whoisactive
where collection_time = (select max(collection_time) from dbo.tbl_Whoisactive)
and (   cast(replace(cpu, ',','') as int) > @cpu_threshold
    or cast(replace(reads, ',','') as int) > @reads_threshold
    or cast(replace(writes, ',','') as int) > @writes_threshold
   );
 
if (@@rowcount > 0)
exec msdb.dbo.sp_send_dbmail .... -- please add your own parameters
go
 
--3. from the ast collection, find the total # of blocked sessions
-- if total > @threshold, email alerts
declare @blocked_session_sum int = 5 -- if blocked session is > 5, email alert
 
if (select sum(blocked_session_count)
from dbo.tbl_whoisactive
where collection_time = (select max(collection_time) from dbo.tbl_Whoisactive)
) > @blocked_session_sum
  exec msdb.dbo.sp_send_dbmail @recipients='[email protected]' -- change to your own email
  , @subject='blocked sessions exceeding threshold' 
  , @body = 'please take a look'
go

Summary

In this tip, we have discussed how to save sp_whoisactive results into a table and then how to further use the collected data for our daily monitoring. The collected data can grow very large if the database system has a large workload, I personally suggest we only keep one week of data. If we want to keep the data for a longer time, we can dump the data from [tbl_whoisactive] table to an archive table (maybe in another database).

Next Steps

For collected data, we can have even more advanced usage, for example, assuming we collect data every minute. If there is session (say spid=51) that is collected in two continuous collections, we may check the delta of CPU, reads and writes of this spid 51.

Also sp_whoisactive is a very complex and advanced tool and there are many parameters that are very helpful, like filter parameters, execution plan parameter (@get_plans) and retrieving transaction parameter (@get_transaction_info) etc, so I encourage everyone to test these parameters to better understand this stored procedure so you can use it to your best advantage.

Once you understand this tip, I’d recommend that you create a PowerShell script, which can read each SQL Server instance name from a central table (or csv file) and then run sp_whoisactive against each instance, and dump the collected data into a central table, so you can do all monitoring analysis against this central table. This will greatly simplify the monitoring work because when a new server is added or an old server is retired, all you need to do is to add/delete the server name from the central configuration table (or csv file).

Please read the following related articles for additional information:

• Sp_whoisactive source code
• sp_whoisactive documentation
• Configuring XE to find locking issues in sql server

About the author

Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

View all my tips