How to Log Activity Using sp_whoisactive
What is sp_whoisactive?
Sp_whoisactive is a procedure written by Adam Machanic, and it replaces tools like sp_who, sp_who2, and Activity Monitor. Download sp_whoisactive at whoisactive.com, and Adam’s got a lot of good documentation over there too.
If you haven’t used the stored procedure before, get familiar with it first. Start here!
If you don’t know about sp_WhoIsActive already, you’ve been living under a rock. It’s just the most awesome, free SQL Server tool there ever was. Adam Machanic, the author of sp_WhoIsActive, says it “is a comprehensive DMV-based monitoring script, designed to tell you at a glance what processes are active on your SQL Server and what they’re up to.”
I’m a fan of using free tools that can help me do my job more effectively. I’ve used sp_WhoIsActive even when I’ve also had paid tools monitoring production.
How to Schedule sp_whoisactive in an Agent Job
Create a new job and plop the below code into the job step, modifying the first 3 variables as needed. The code will create the logging table if it doesn’t exist, the clustered index if it doesn’t exist, log current activity and purge older data based on the @retention variable.
How often should you collect activity? I think collecting sp_WhoIsActive data every 30-60 seconds is a good balance between logging enough activity to troubleshoot production problems and the storage needed to keep the data in a very busy environment.
SET NOCOUNT ON;
DECLARE @retention int = 7, @destination_table varchar(500) = 'WhoIsActive', @destination_database sysname = 'DBA',
@schema varchar(max), @SQL nvarchar(4000), @parameters nvarchar(500), @exists bit;
SET @destination_table = @destination_database + '.dbo.' + @destination_table;
--create the logging table
IF OBJECT_ID(@destination_table) IS NULL
EXEC sp_WhoIsActive @get_transaction_info = 1, @get_outer_command = 1, @get_plans = 1, @return_schema = 1, @schema = @schema OUTPUT;
SET @schema = REPLACE(@schema, '<table_name>', @destination_table);
--create index on collection_time
SET @SQL = 'USE ' + QUOTENAME(@destination_database) + '; IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(@destination_table) AND name = N''cx_collection_time'') SET @exists = 0';
SET @parameters = N'@destination_table varchar(500), @exists bit OUTPUT';
EXEC sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT;
IF @exists = 0
SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)';
--collect activity into logging table
EXEC dbo.sp_WhoIsActive @get_transaction_info = 1, @get_outer_command = 1, @get_plans = 1, @destination_table = @destination_table;
--purge older data
SET @SQL = 'DELETE FROM ' + @destination_table + ' WHERE collection_time < DATEADD(day, -' + CAST(@retention AS varchar(10)) + ', GETDATE());';
You could also break the DELETE out into its own job so that it runs once a day. If you do this, loop through the DELETEs in batches rather than all at once.
How to Query sp_whoisactive’s Results
This is just a starter query. I generally am looking at a specific time period based on a complaint from a user that queries were running slow earlier in the day. I once used the WhoIsActive logging table to identify who was causing tempdb to grow absurdly big. By the time I got to it, tempdb was 500GB in size! I switched my query to include tempdb_allocations and tempdb_current plus ORDER BY tempdb_allocations DESC, and it was immediately clear a business user was querying ALL.THE.THINGS.
SELECT TOP 1000 CPU, reads, collection_time, start_time, [dd hh:mm:ss.mss] AS 'run duration', [program_name], login_name, database_name, session_id, blocking_session_id, wait_info, sql_text, *
WHERE collection_time BETWEEN '2016-07-20 07:55:00.000' AND '2016-07-20 09:00:00.000'
AND login_name NOT IN ('DomainName\sqlservice')
--AND CAST(sql_text AS varchar(max)) LIKE '%some query%'
ORDER BY 1 DESC