Log Activity with sp_whoisactive

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 = 'DBAtools',
        @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
    BEGIN;
        EXEC dbo.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);
        EXEC ( @schema );
    END;

--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 sys.sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT;

IF @exists = 0
    BEGIN;
        SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)';
        EXEC ( @SQL );
    END;

--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());';
EXEC ( @SQL );

SET NOCOUNT ON;

DECLARE @retention INT = 7,

@destination_table VARCHAR(500) = 'WhoIsActive',

@destination_database sysname = 'DBAtools',

@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

BEGIN;

EXEC dbo.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);

EXEC ( @schema );

END;

--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 sys.sp_executesql @SQL, @parameters, @destination_table = @destination_table, @exists = @exists OUTPUT;

IF @exists = 0

BEGIN;

SET @SQL = 'CREATE CLUSTERED INDEX cx_collection_time ON ' + @destination_table + '(collection_time ASC)';

EXEC ( @SQL );

END;

--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());';

EXEC ( @SQL );

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.

If you run this, and then upgrade sp_WhoIsActive to a newer version that outputs more columns, the inserts will fail because the newer columns don’t exist. The easiest way to fix that is drop the output table whenever you upgrade sp_WhoIsActive, and this code will just regenerate the table with the latest results structure. If you need to preserve your old sp_WhoIsActive data, you’ll need to alter the output table manually to include any new columns that have been added in the newer version of sp_WhoIsActive.

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, *
FROM WhoIsActive
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

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, *

FROM WhoIsActive

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