Week Four: sp_WhoIsActive Automation – Smarter Query Monitoring

For Week Four, I've put up the work in a more systematic way. If Week Three was about collecting baselines, Week Four is about using that knowledge to automate query activity monitoring. The goal is to capture and analyze active sessions over time, detect long-running queries and send alerts, all wrapped neatly inside SQL Agent jobs.

If you’ve been working with SQL Server and haven’t heard of sp_WhoIsActive, well… it’s kind of like saying you’ve never heard of coffee. It’s one of those tools that everyone eventually stumbles upon, usually while trying to figure out why the server’s on fire.

Created by Adam Machanic, sp_WhoIsActive is a powerful, DMV-based script that gives you a clear view of what’s running on your SQL Server and what those queries are up to. He calls it “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.” In practice, it’s basically the Swiss Army knife for anyone trying to make sense of SQL Server activity in real time.

Why Automate sp_WhoIsActive?

Manually running sp_WhoIsActive is fine when you're reacting to performance issues. But why not proactively collect data and send alerts before things escalate? Automating it lets you:

• Collect query snapshots every minute (or whatever interval you choose)

• Store history to analyze trends (CPU, memory, tempdb usage, etc.)

• Alert on long-running queries in near-real time

• Retain full control (you decide what to log, when, and how)

  
  

This isn't meant to be the final word on sp_WhoIsActive automation. It’s a starter kit. You can tweak, extend and evolve it to fit your environment.

Step 1: Create the Agent Job to Log Activity

Here’s the full working SQL Agent job script. This creates a job that logs sp_WhoIsActive output every minute into a table called dbo.QueryHistoryLog inside the DBA database (assuming you have a dedicated database). It also builds the table and index on first run and purges old data after 30 days.

USE msdb;
GO

-- Clean up if job exists
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N'DBA: Query History Logging')
BEGIN
    EXEC msdb.dbo.sp_delete_job @job_name = N'DBA: Query History Logging';
END
GO

-- Add Job
EXEC msdb.dbo.sp_add_job
    @job_name = N'DBA: Query History Logging',
    @enabled = 1,
    @description = N'Logs query activity every 1 minute using DBA.dbo.sp_WhoIsActive.',
    @category_name = N'Database Maintenance',
    @owner_login_name = N'sa';
GO

-- Add Job Step
EXEC msdb.dbo.sp_add_jobstep
    @job_name = N'DBA: Query History Logging',
    @step_name = N'Capture and Retain sp_WhoIsActive Data',
    @subsystem = N'TSQL',
    @database_name = N'DBA',
    @command = N'
SET NOCOUNT ON;
DECLARE @retention_days INT = 30;
DECLARE @destination_table SYSNAME = ''QueryHistoryLog'';
DECLARE @destination_schema SYSNAME = ''dbo'';
DECLARE @schema_sql NVARCHAR(MAX);
DECLARE @full_table_name NVARCHAR(500) = QUOTENAME(@destination_schema) + ''.'' + QUOTENAME(@destination_table);

-- Create table if not exists
IF OBJECT_ID(@full_table_name) IS NULL
BEGIN
    EXEC DBA.dbo.sp_WhoIsActive
        @get_transaction_info = 1,
        @get_outer_command = 1,
        @get_plans = 1,
        @find_block_leaders = 1,
        @return_schema = 1,
        @schema = @schema_sql OUTPUT;

    SET @schema_sql = REPLACE(@schema_sql, ''varchar(8000)'', ''varchar(30)'');
    SET @schema_sql = REPLACE(@schema_sql, ''nvarchar(4000)'', ''nvarchar(1000)'');

    IF CHARINDEX(''id bigint identity'', @schema_sql) = 0
    BEGIN
        SET @schema_sql = REPLACE(@schema_sql, ''NULL,'', ''NULL,[id] BIGINT IDENTITY(1,1) PRIMARY KEY,'');
    END

    SET @schema_sql = REPLACE(@schema_sql, ''<table_name>'', @full_table_name);

    EXEC (@schema_sql);
END

IF NOT EXISTS (
    SELECT 1 FROM sys.indexes
    WHERE object_id = OBJECT_ID(@full_table_name) AND name = N''ix_collection_time''
)
BEGIN
    EXEC(''CREATE NONCLUSTERED INDEX ix_collection_time ON '' + @full_table_name + '' (collection_time)'');
END

EXEC DBA.dbo.sp_WhoIsActive
    @get_transaction_info = 1,
    @get_outer_command = 1,
    @get_plans = 1,
    @find_block_leaders = 1,
    @destination_table = @full_table_name;

DECLARE @purge_sql NVARCHAR(MAX) = N''DELETE FROM '' + @full_table_name + N'' WHERE collection_time < DATEADD(DAY, -'' + CAST(@retention_days AS NVARCHAR) + '', GETDATE())'';
EXEC (@purge_sql);
',
    @on_success_action = 1,
    @on_fail_action = 2;
GO

-- Create schedule (every 1 minute)
EXEC msdb.dbo.sp_add_schedule
    @schedule_name = N'Every 1 Minute',
    @enabled = 1,
    @freq_type = 4,
    @freq_interval = 1,
    @freq_subday_type = 4,
    @freq_subday_interval = 1,
    @active_start_time = 0;
GO

-- Attach job and schedule
EXEC msdb.dbo.sp_add_jobserver
    @job_name = N'DBA: Query History Logging';

EXEC msdb.dbo.sp_attach_schedule
    @job_name = N'DBA: Query History Logging',
    @schedule_name = N'Every 1 Minute';
GO

Step 2: Sample Queries to Analyze Activity

SELECT
	CAST(collection_time AS DATE) AS log_date,
	DATEPART(HOUR, collection_time) AS hour,
	COUNT(*) AS sample_count,
	SUM(TRY_CAST(CPU AS BIGINT)) AS total_cpu,
	SUM(TRY_CAST(tempdb_allocations AS BIGINT)) AS total_tempdb_alloc,
	SUM(TRY_CAST(tempdb_current AS BIGINT)) AS total_tempdb_current,
	SUM(TRY_CAST(reads AS BIGINT)) AS total_reads,
	SUM(TRY_CAST(writes AS BIGINT)) AS total_writes,
	SUM(TRY_CAST(physical_reads AS BIGINT)) AS total_physical_reads,
	SUM(TRY_CAST(used_memory AS BIGINT)) AS total_used_memory
FROM dbo.QueryHistoryLog
GROUP BY
	CAST(collection_time AS DATE),
	DATEPART(HOUR, collection_time)
ORDER BY log_date DESC, hour DESC;

It gives you a feel for how busy your SQL Server is by the hour. Handy when diagnosing time-based spikes or planning maintenance windows.

SELECT 
	[dd hh:mm:ss.mss],
	session_id,
	sql_text,
	database_name,
	wait_info,
	blocking_session_id,
	blocked_session_count
FROM dbo.QueryHistoryLog
WHERE blocked_session_count > 0
AND blocking_session_id IS NULL;	

This identifies sessions that are causing blocks but aren't blocked themselves. Basically, the "culprits" at the top of a blocking chain.

Step 3: Long-Running Query Email Alert

This part is where things get proactive. You can schedule this as a separate job or add it as a second step. It checks for any queries running for 10 minutes or more and sends a quick email alert.

USE DBA;
GO

DECLARE @AlertBody NVARCHAR(MAX);

WITH LongRunning AS (
    SELECT
        collection_time,
        session_id,
        [dd hh:mm:ss.mss] AS run_duration,
        host_name,
        login_name,
        database_name,
        program_name,
        sql_text,
        start_time
    FROM dbo.QueryHistoryLog
    WHERE TRY_CAST(SUBSTRING([dd hh:mm:ss.mss], 5, 2) AS INT) * 60 + 
          TRY_CAST(SUBSTRING([dd hh:mm:ss.mss], 8, 2) AS INT) >= 10
        AND collection_time >= DATEADD(MINUTE, -11, GETDATE())
)

SELECT @AlertBody = 
    COALESCE(@AlertBody + CHAR(13) + CHAR(10), '') + 
    'Time: ' + CONVERT(VARCHAR, collection_time, 120) + ' | SPID: ' + CAST(session_id AS VARCHAR) + 
    ' | Host: ' + host_name + ' | User: ' + login_name + ' | DB: ' + database_name + 
    ' | Duration: ' + run_duration + CHAR(13) + CHAR(10) +
    'SQL: ' + LEFT(sql_text, 500) + CHAR(13) + CHAR(10)
FROM LongRunning;

IF @AlertBody IS NOT NULL
BEGIN
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'YourMailProfile',
        @recipients = 'dba-team@example.com',
        @subject = 'ALERT: Long Running Queries Detected (>= 10 mins)',
        @body = @AlertBody,
        @body_format = 'TEXT';
END

Automating sp_WhoIsActive isn’t just a neat trick, it’s an investment in visibility. It buys you awareness, trend data and quicker response times. All with relatively little setup.

Whether you're using this as a base or extending it further with your own filters, joins or visualization dashboards, it's a flexible tool worth building into your daily monitoring. as DBA, this setup is a solid foundation. Start with this, adjust for your needs and avoid surprises in production.

Resources:

• sp_WhoIsActive GitHub

• Official Documentation