Leveraging SP_Whoisactive for Monitoring and Troubleshooting BI/DW Workloads

Wait stats analysis is often my tool of first resort for triaging performance problems. Adam Machanic’s SP_Whoisactive utility is my tool of choice for getting insight into what is happening on a server.

Data Warehouse ETL processes often occur overnight. Since I do not have the stamina or desire to stay up all night every night to get insight into what happens during overnight loads, I run a SQL Agent job every five minutes to persist snapshots from SP_Whoisactive into a table. That way I can review the table later in the day.
SP_Whoisactive has a parameter, @destination_table, that will redirect the output to a table. There is also a parameter to generate the DDL to create the destination table. See Adam’s blog post for more details. As an example, I want my output to include lock, task, and transaction information. A query that will generate the DDL for the output table looks like this:

1
2
3
4
5
6
7

DECLARE @s VARCHAR(MAX)
EXEC sp_WhoIsActive @get_locks = 1
	,@get_task_info = 2
	,@get_transaction_info = 1
	,@return_schema = 1
	,@schema = @s OUTPUT
SELECT @s

I can take the DDL created from the previous example, specify a table name, and run the query. For example:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

CREATE TABLE dbo.WhoisactiveTrace (
	[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
	,[tasks] VARCHAR(30) NULL
	,[tran_log_writes] 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
	,[context_switches] VARCHAR(30) NULL
	,[physical_io] VARCHAR(30) NULL
	,[physical_reads] VARCHAR(30) NULL
	,[locks] XML NULL
	,[used_memory] VARCHAR(30) NULL
	,[status] VARCHAR(30) NOT NULL
	,[tran_start_time] DATETIME 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
	)

The companion query to output to the newly-created table looks like this:

1
2
3
4
5

EXEC sp_WhoIsActive 
	@get_locks = 1,
	@get_task_info = 2,
	@get_transaction_info = 1,
	@destination_table = 'dbo.WhoisactiveTrace'

For my part I incorporate the destination table into a DBA repository and I incorporate the previous query into a SQL Agent job step. I schedule the job to run approximately every five minutes.  Note that in production there is a second step to clean up old log entries.
I found this to be a useful aid in troubleshooting BI Data Warehouse loads where very large tables are being loaded on a nightly basis.