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:
- Header information about version, copyright and contact information
- Parameters with default values and the brief description of each parameter
- 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:
![cannot do
insert into [table] exec sp_whoisactive](/tipimages2/6345_collect-sp_whoisactive-results-sql-server.002.png)
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.
- [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.
- [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

View all my tips