Four Collectors, Four Different Rhythms
AG health every few minutes if you’re running Availability Groups, index fragmentation weekly, Query Store’s top queries every 30 minutes, error log entries every 10. Each script below is a generator: run it, review the DDL it prints, then run that DDL on the target instance to create the collector table and its SQL Agent job.
Generate-CollectorJob-AgHealth.sql
Snapshots Availability Group replica state, synchronization health, and log send/redo queue depth per database, system-versioned for automatic history.
/*
Script Name : Generate-CollectorJob-AgHealth
Category : collectors
Purpose : Generates DDL to create the DBA - Collect AG Health SQL Agent job.
Creates the target database and a system-versioned (temporal) collector table
if absent, then outputs T-SQL to install a recurring AG replica state MERGE job.
Each run upserts current replica and database synchronization state — SQL Server
automatically records every change in the paired history table, capturing exactly
when replicas became disconnected, unsynchronized, or changed role.
On instances with no AG configured, a NO_AG sentinel row is inserted once
so the job always succeeds and the table remains queryable.
Edit parameters, review output, then run on the target instance.
Author : Peter Whyte (https://sqldba.blog)
Requires : sysadmin (to run generated DDL); VIEW SERVER STATE at job runtime
Notes : Default interval: every 5 minutes. Requires SQL Server 2016+ (temporal support).
History is written only when state/health/connected columns change — lag metrics
(queue sizes, timing) are not tracked in history to avoid noise.
Filter WHERE ag_name <> 'NO_AG' when querying production replica data.
If upgrading from the non-temporal version, drop collector.AgHealth manually first.
PK is NONCLUSTERED deliberately: the four key columns are all identifiers
(sysname, nvarchar(128)), so the key is 1024 bytes. That exceeds SQL Server's
900-byte CLUSTERED index key limit but fits the 1700-byte nonclustered limit.
Narrowing the columns instead would truncate legal 128-character identifiers.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
-- ── Parameters ────────────────────────────────────────────────────────────────
DECLARE @TargetDatabase sysname = N'DBAMonitor';
DECLARE @JobOwner sysname = N'sa';
DECLARE @CategoryName nvarchar(128) = N'DBA Collectors';
DECLARE @IntervalMinutes int = 5;
-- ─────────────────────────────────────────────────────────────────────────────
DECLARE @q nchar(1) = NCHAR(39);
DECLARE @crlf nvarchar(2) = CHAR(13) + CHAR(10);
DECLARE @ddl nvarchar(max) = N'';
DECLARE @jobName sysname = N'DBA - Collect AG Health';
DECLARE @stepCmd nvarchar(max);
-- ── Step command (| = single-quote placeholder) ────────────────────────────────
SET @stepCmd = REPLACE(
N'SET NOCOUNT ON;
IF NOT EXISTS (SELECT 1 FROM sys.availability_groups)
BEGIN
MERGE [<<DB>>].[collector].[AgHealthCurrent] AS target
USING (SELECT @@SERVERNAME, |NO_AG|, ||, ||)
AS source(server_name, ag_name, replica_server_name, database_name)
ON target.server_name = source.server_name
AND target.ag_name = source.ag_name
AND target.replica_server_name = source.replica_server_name
AND target.database_name = source.database_name
WHEN NOT MATCHED BY TARGET THEN
INSERT (server_name, ag_name, replica_server_name, database_name)
VALUES (source.server_name, source.ag_name,
source.replica_server_name, source.database_name);
RETURN;
END
MERGE [<<DB>>].[collector].[AgHealthCurrent] AS target
USING (
SELECT
@@SERVERNAME AS server_name,
ag.name AS ag_name,
ar.replica_server_name,
ISNULL(adb.database_name, ||) AS database_name,
ars.role_desc,
ars.operational_state_desc,
ars.connected_state_desc,
ars.synchronization_health_desc,
ars.last_connect_error_description,
drs.synchronization_state_desc AS db_synchronization_state_desc,
drs.synchronization_health_desc AS db_synchronization_health_desc,
drs.log_send_queue_size AS log_send_queue_kb,
drs.log_send_rate AS log_send_rate_kb_s,
drs.redo_queue_size AS redo_queue_kb,
drs.redo_rate AS redo_rate_kb_s,
drs.last_sent_time,
drs.last_received_time,
drs.last_hardened_time,
drs.last_redone_time,
drs.last_commit_time
FROM sys.availability_groups ag
JOIN sys.availability_replicas ar ON ar.group_id = ag.group_id
JOIN sys.dm_hadr_availability_replica_states ars ON ars.replica_id = ar.replica_id
LEFT JOIN sys.dm_hadr_database_replica_states drs ON drs.replica_id = ars.replica_id
LEFT JOIN sys.availability_databases_cluster adb ON adb.group_id = ag.group_id
AND adb.group_database_id = drs.group_database_id
) AS source
ON target.server_name = source.server_name
AND target.ag_name = source.ag_name
AND target.replica_server_name = source.replica_server_name
AND target.database_name = source.database_name
WHEN MATCHED AND (
ISNULL(target.role_desc, |_|) <> ISNULL(source.role_desc, |_|) OR
ISNULL(target.operational_state_desc, |_|) <> ISNULL(source.operational_state_desc, |_|) OR
ISNULL(target.connected_state_desc, |_|) <> ISNULL(source.connected_state_desc, |_|) OR
ISNULL(target.synchronization_health_desc, |_|) <> ISNULL(source.synchronization_health_desc, |_|) OR
ISNULL(target.db_synchronization_state_desc, |_|) <> ISNULL(source.db_synchronization_state_desc, |_|) OR
ISNULL(target.db_synchronization_health_desc, |_|) <> ISNULL(source.db_synchronization_health_desc, |_|)
) THEN UPDATE SET
role_desc = source.role_desc,
operational_state_desc = source.operational_state_desc,
connected_state_desc = source.connected_state_desc,
synchronization_health_desc = source.synchronization_health_desc,
last_connect_error_description = source.last_connect_error_description,
db_synchronization_state_desc = source.db_synchronization_state_desc,
db_synchronization_health_desc = source.db_synchronization_health_desc,
log_send_queue_kb = source.log_send_queue_kb,
log_send_rate_kb_s = source.log_send_rate_kb_s,
redo_queue_kb = source.redo_queue_kb,
redo_rate_kb_s = source.redo_rate_kb_s,
last_sent_time = source.last_sent_time,
last_received_time = source.last_received_time,
last_hardened_time = source.last_hardened_time,
last_redone_time = source.last_redone_time,
last_commit_time = source.last_commit_time
WHEN NOT MATCHED BY TARGET THEN
INSERT (server_name, ag_name, replica_server_name, database_name,
role_desc, operational_state_desc, connected_state_desc,
synchronization_health_desc, last_connect_error_description,
db_synchronization_state_desc, db_synchronization_health_desc,
log_send_queue_kb, log_send_rate_kb_s,
redo_queue_kb, redo_rate_kb_s,
last_sent_time, last_received_time, last_hardened_time,
last_redone_time, last_commit_time)
VALUES (source.server_name, source.ag_name, source.replica_server_name,
source.database_name, source.role_desc, source.operational_state_desc,
source.connected_state_desc, source.synchronization_health_desc,
source.last_connect_error_description, source.db_synchronization_state_desc,
source.db_synchronization_health_desc, source.log_send_queue_kb,
source.log_send_rate_kb_s, source.redo_queue_kb, source.redo_rate_kb_s,
source.last_sent_time, source.last_received_time, source.last_hardened_time,
source.last_redone_time, source.last_commit_time);'
, N'|', NCHAR(39));
SET @stepCmd = REPLACE(@stepCmd, N'<<DB>>', @TargetDatabase);
-- ═══════════════════════════════════════════════════════════════════════════════
-- DDL output
-- ═══════════════════════════════════════════════════════════════════════════════
SET @ddl =
N'-- ================================================================' + @crlf +
N'-- Generated by Generate-CollectorJob-AgHealth.sql' + @crlf +
N'-- Server : ' + @@SERVERNAME + @crlf +
N'-- Target DB : ' + @TargetDatabase + @crlf +
N'-- Generated : ' + CONVERT(nvarchar(20), GETDATE(), 120) + @crlf +
N'-- ================================================================' + @crlf + @crlf;
-- ── 1. Target database ────────────────────────────────────────────────────────
SET @ddl +=
N'IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = N' + @q + @TargetDatabase + @q + N')' + @crlf +
N' CREATE DATABASE [' + @TargetDatabase + N'];' + @crlf +
N'GO' + @crlf + @crlf;
-- ── 2. Collector schema ───────────────────────────────────────────────────────
SET @ddl +=
N'IF NOT EXISTS (SELECT 1 FROM [' + @TargetDatabase + N'].sys.schemas WHERE name = N' + @q + N'collector' + @q + N')' + @crlf +
N' EXEC [' + @TargetDatabase + N'].sys.sp_executesql N' + @q + N'CREATE SCHEMA collector' + @q + N';' + @crlf +
N'GO' + @crlf + @crlf;
-- ── 3. AgHealthCurrent temporal table ─────────────────────────────────────────
SET @ddl +=
N'IF NOT EXISTS (' + @crlf +
N' SELECT 1 FROM [' + @TargetDatabase + N'].sys.objects o' + @crlf +
N' JOIN [' + @TargetDatabase + N'].sys.schemas s ON s.schema_id = o.schema_id' + @crlf +
N' WHERE o.name = N' + @q + N'AgHealthCurrent' + @q + N' AND s.name = N' + @q + N'collector' + @q + N')' + @crlf +
N'BEGIN' + @crlf +
N'CREATE TABLE [' + @TargetDatabase + N'].[collector].[AgHealthCurrent] (' + @crlf +
N' server_name nvarchar(128) NOT NULL,' + @crlf +
N' ag_name nvarchar(128) NOT NULL,' + @crlf +
N' replica_server_name nvarchar(128) NOT NULL,' + @crlf +
N' database_name nvarchar(128) NOT NULL,' + @crlf +
N' role_desc nvarchar(60) NULL,' + @crlf +
N' operational_state_desc nvarchar(60) NULL,' + @crlf +
N' connected_state_desc nvarchar(60) NULL,' + @crlf +
N' synchronization_health_desc nvarchar(60) NULL,' + @crlf +
N' last_connect_error_description nvarchar(1024) NULL,' + @crlf +
N' db_synchronization_state_desc nvarchar(60) NULL,' + @crlf +
N' db_synchronization_health_desc nvarchar(60) NULL,' + @crlf +
N' log_send_queue_kb bigint NULL,' + @crlf +
N' log_send_rate_kb_s bigint NULL,' + @crlf +
N' redo_queue_kb bigint NULL,' + @crlf +
N' redo_rate_kb_s bigint NULL,' + @crlf +
N' last_sent_time datetime2 NULL,' + @crlf +
N' last_received_time datetime2 NULL,' + @crlf +
N' last_hardened_time datetime2 NULL,' + @crlf +
N' last_redone_time datetime2 NULL,' + @crlf +
N' last_commit_time datetime2 NULL,' + @crlf +
N' SysStartTime datetime2(2) GENERATED ALWAYS AS ROW START NOT NULL,' + @crlf +
N' SysEndTime datetime2(2) GENERATED ALWAYS AS ROW END NOT NULL,' + @crlf +
N' PERIOD FOR SYSTEM_TIME (SysStartTime, SysEndTime),' + @crlf +
N' CONSTRAINT [PK_AgHealthCurrent]' + @crlf +
N' PRIMARY KEY NONCLUSTERED (server_name, ag_name, replica_server_name, database_name)' + @crlf +
N') WITH (SYSTEM_VERSIONING = ON (' + @crlf +
N' HISTORY_TABLE = [collector].[AgHealthHistory],' + @crlf +
N' DATA_CONSISTENCY_CHECK = ON));' + @crlf +
N'END' + @crlf +
N'GO' + @crlf + @crlf;
-- ── 4. Agent category ─────────────────────────────────────────────────────────
SET @ddl +=
N'USE msdb;' + @crlf +
N'GO' + @crlf + @crlf +
N'IF NOT EXISTS (SELECT 1 FROM msdb.dbo.syscategories WHERE name = N' + @q + @CategoryName + @q + N' AND category_class = 1)' + @crlf +
N' EXEC msdb.dbo.sp_add_category' + @crlf +
N' @class = N' + @q + N'JOB' + @q + N', @type = N' + @q + N'LOCAL' + @q + N', @name = N' + @q + @CategoryName + @q + N';' + @crlf +
N'GO' + @crlf + @crlf;
-- ── 5. Job + step + schedule ──────────────────────────────────────────────────
SET @ddl +=
N'-- Job: ' + @jobName + @crlf +
N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + @jobName + @q + N')' + @crlf +
N' EXEC msdb.dbo.sp_delete_job @job_name = N' + @q + @jobName + @q + N', @delete_unused_schedule = 1;' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_add_job' + @crlf +
N' @job_name = N' + @q + @jobName + @q + N',' + @crlf +
N' @enabled = 1,' + @crlf +
N' @owner_login_name = N' + @q + @JobOwner + @q + N',' + @crlf +
N' @category_name = N' + @q + @CategoryName + @q + N';' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_add_jobstep' + @crlf +
N' @job_name = N' + @q + @jobName + @q + N',' + @crlf +
N' @step_id = 1,' + @crlf +
N' @step_name = N' + @q + N'Merge AG replica state' + @q + N',' + @crlf +
N' @subsystem = N' + @q + N'TSQL' + @q + N',' + @crlf +
N' @database_name = N' + @q + N'master' + @q + N',' + @crlf +
N' @command = N' + @q + REPLACE(@stepCmd, @q, @q + @q) + @q + N',' + @crlf +
N' @retry_attempts = 0,' + @crlf +
N' @on_success_action = 1,' + @crlf +
N' @on_fail_action = 2;' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_add_schedule' + @crlf +
N' @schedule_name = N' + @q + @jobName + N' Every ' + CAST(@IntervalMinutes AS nvarchar(5)) + N'min' + @q + N',' + @crlf +
N' @freq_type = 4,' + @crlf +
N' @freq_interval = 1,' + @crlf +
N' @freq_subday_type = 4,' + @crlf +
N' @freq_subday_interval = ' + CAST(@IntervalMinutes AS nvarchar(5)) + N';' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_attach_schedule' + @crlf +
N' @job_name = N' + @q + @jobName + @q + N',' + @crlf +
N' @schedule_name = N' + @q + @jobName + N' Every ' + CAST(@IntervalMinutes AS nvarchar(5)) + N'min' + @q + N';' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + @jobName + @q + N';' + @crlf +
N'GO' + @crlf;
SELECT @ddl AS ddl;On a standalone instance with no Availability Group configured, the table still creates and the job still runs. It writes a single NO_AG sentinel row so the job always succeeds and the table stays queryable. Filter WHERE ag_name <> N'NO_AG' when you query real replica data.
Generate-CollectorJob-IndexFragmentation.sql
Weekly snapshot of index fragmentation across every online user database using SAMPLED mode (reads roughly 1% of pages, accurate enough for detection without DETAILED‘s overhead). Indexes under 100 pages and heaps are excluded. Thresholds: 30%+ suggests REBUILD, 10-29% REORGANIZE, under 10% no action.
/*
Script Name : Generate-CollectorJob-IndexFragmentation
Category : collectors
Purpose : Generates DDL to create the DBA - Collect Index Fragmentation SQL Agent job.
Creates the target database and collector.IndexFragmentation table if absent,
then outputs T-SQL to install a weekly index fragmentation snapshot job.
The job iterates all online user databases using SAMPLED mode
(reads ~1% of pages — accurate for detection, avoids DETAILED overhead).
Indexes smaller than 100 pages and heaps are excluded.
Edit parameters, review output, then run on the target instance.
Author : Peter Whyte (https://sqldba.blog)
Requires : sysadmin (to run generated DDL); VIEW DATABASE STATE per database at job runtime
Notes : Default schedule: weekly, Saturday at 01:00.
Thresholds: >=30% REBUILD, 10-29% REORGANIZE, <10% NONE.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
-- ── Parameters ────────────────────────────────────────────────────────────────
DECLARE @TargetDatabase sysname = N'DBAMonitor'; -- created if absent
DECLARE @JobOwner sysname = N'sa';
DECLARE @CategoryName nvarchar(128) = N'DBA Collectors';
DECLARE @RunHour tinyint = 1; -- hour (0-23) for weekly run
-- ─────────────────────────────────────────────────────────────────────────────
DECLARE @q nchar(1) = NCHAR(39);
DECLARE @crlf nvarchar(2) = CHAR(13) + CHAR(10);
DECLARE @ddl nvarchar(max) = N'';
DECLARE @jobName sysname = N'DBA - Collect Index Fragmentation';
DECLARE @schedTS int = @RunHour * 10000;
DECLARE @stepCmd nvarchar(max);
-- ── Step command (| = single-quote placeholder) ────────────────────────────────
-- Builds @sql via string concatenation so @db is evaluated, not embedded literally.
-- @dbid (integer DB_ID) is computed before @sql is built, avoiding embedded string quotes
-- for the DB name inside the DMV call.
-- N|...| segments become N'...' after REPLACE; || inside them → '' (escaped quote).
SET @stepCmd = REPLACE(
N'SET NOCOUNT ON;
DECLARE @db sysname;
DECLARE @dbid int;
DECLARE @sql nvarchar(max);
DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.databases
WHERE state_desc = |ONLINE| AND database_id > 4 AND is_read_only = 0;
OPEN db_cur;
FETCH NEXT FROM db_cur INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbid = DB_ID(@db);
SET @sql =
N|INSERT INTO [<<DB>>].[collector].[IndexFragmentation] |
+ N|(server_name, collection_time, database_name, schema_name, table_name, |
+ N|index_name, index_type, partition_number, page_count, |
+ N|avg_fragmentation_pct, fragment_count, avg_fragment_size_pages, recommended_action) |
+ N|SELECT @@SERVERNAME, GETDATE(), DB_NAME(| + CAST(@dbid AS nvarchar(10)) + N|), |
+ N|s.name, o.name, ix.name, ix.type_desc, |
+ N|ips.partition_number, ips.page_count, |
+ N|CAST(ips.avg_fragmentation_in_percent AS decimal(5,1)), |
+ N|ips.fragment_count, CAST(ips.avg_fragment_size_in_pages AS decimal(8,1)), |
+ N|CASE |
+ N| WHEN ips.avg_fragmentation_in_percent >= 30 THEN ||REBUILD|| |
+ N| WHEN ips.avg_fragmentation_in_percent >= 10 THEN ||REORGANIZE|| |
+ N| ELSE ||NONE|| |
+ N|END |
+ N|FROM sys.dm_db_index_physical_stats(| + CAST(@dbid AS nvarchar(10)) + N|, NULL, NULL, NULL, ||SAMPLED||) ips |
+ N|JOIN [| + @db + N|].sys.indexes ix ON ix.object_id = ips.object_id AND ix.index_id = ips.index_id |
+ N|JOIN [| + @db + N|].sys.objects o ON o.object_id = ips.object_id |
+ N|JOIN [| + @db + N|].sys.schemas s ON s.schema_id = o.schema_id |
+ N|WHERE ips.page_count >= 100 AND ips.index_id > 0 AND o.is_ms_shipped = 0;|;
BEGIN TRY
EXEC sp_executesql @sql;
END TRY
BEGIN CATCH
-- Skip inaccessible or incompatible databases
END CATCH;
FETCH NEXT FROM db_cur INTO @db;
END;
CLOSE db_cur;
DEALLOCATE db_cur;'
, N'|', NCHAR(39));
SET @stepCmd = REPLACE(@stepCmd, N'<<DB>>', @TargetDatabase);
-- ═══════════════════════════════════════════════════════════════════════════════
-- DDL output
-- ═══════════════════════════════════════════════════════════════════════════════
SET @ddl =
N'-- ================================================================' + @crlf +
N'-- Generated by Generate-CollectorJob-IndexFragmentation.sql' + @crlf +
N'-- Server : ' + @@SERVERNAME + @crlf +
N'-- Target DB : ' + @TargetDatabase + @crlf +
N'-- Generated : ' + CONVERT(nvarchar(20), GETDATE(), 120) + @crlf +
N'-- ================================================================' + @crlf + @crlf;
-- ── 1. Target database ────────────────────────────────────────────────────────
SET @ddl +=
N'IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = N' + @q + @TargetDatabase + @q + N')' + @crlf +
N' CREATE DATABASE [' + @TargetDatabase + N'];' + @crlf +
N'GO' + @crlf + @crlf;
-- ── 2. Collector schema ───────────────────────────────────────────────────────
SET @ddl +=
N'IF NOT EXISTS (SELECT 1 FROM [' + @TargetDatabase + N'].sys.schemas WHERE name = N' + @q + N'collector' + @q + N')' + @crlf +
N' EXEC [' + @TargetDatabase + N'].sys.sp_executesql N' + @q + N'CREATE SCHEMA collector' + @q + N';' + @crlf +
N'GO' + @crlf + @crlf;
-- ── 3. IndexFragmentation table ───────────────────────────────────────────────
SET @ddl +=
N'IF NOT EXISTS (' + @crlf +
N' SELECT 1 FROM [' + @TargetDatabase + N'].sys.objects o' + @crlf +
N' JOIN [' + @TargetDatabase + N'].sys.schemas s ON s.schema_id = o.schema_id' + @crlf +
N' WHERE o.name = N' + @q + N'IndexFragmentation' + @q + N' AND s.name = N' + @q + N'collector' + @q + N')' + @crlf +
N'CREATE TABLE [' + @TargetDatabase + N'].[collector].[IndexFragmentation] (' + @crlf +
N' id bigint IDENTITY(1,1) PRIMARY KEY,' + @crlf +
N' server_name nvarchar(128) NOT NULL,' + @crlf +
N' collection_time datetime2 NOT NULL,' + @crlf +
N' database_name nvarchar(128),' + @crlf +
N' schema_name nvarchar(128),' + @crlf +
N' table_name nvarchar(128),' + @crlf +
N' index_name nvarchar(128),' + @crlf +
N' index_type nvarchar(60),' + @crlf +
N' partition_number int,' + @crlf +
N' page_count bigint,' + @crlf +
N' avg_fragmentation_pct decimal(5,1),' + @crlf +
N' fragment_count bigint,' + @crlf +
N' avg_fragment_size_pages decimal(8,1),' + @crlf +
N' recommended_action nvarchar(20)' + @crlf +
N');' + @crlf +
N'GO' + @crlf + @crlf;
-- ── 4. Agent category ─────────────────────────────────────────────────────────
SET @ddl +=
N'USE msdb;' + @crlf +
N'GO' + @crlf + @crlf +
N'IF NOT EXISTS (SELECT 1 FROM msdb.dbo.syscategories WHERE name = N' + @q + @CategoryName + @q + N' AND category_class = 1)' + @crlf +
N' EXEC msdb.dbo.sp_add_category' + @crlf +
N' @class = N' + @q + N'JOB' + @q + N', @type = N' + @q + N'LOCAL' + @q + N', @name = N' + @q + @CategoryName + @q + N';' + @crlf +
N'GO' + @crlf + @crlf;
-- ── 5. Job + step + schedule (weekly Saturday at @RunHour:00) ─────────────────
SET @ddl +=
N'-- Job: ' + @jobName + @crlf +
N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + @jobName + @q + N')' + @crlf +
N' EXEC msdb.dbo.sp_delete_job @job_name = N' + @q + @jobName + @q + N', @delete_unused_schedule = 1;' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_add_job' + @crlf +
N' @job_name = N' + @q + @jobName + @q + N',' + @crlf +
N' @enabled = 1,' + @crlf +
N' @owner_login_name = N' + @q + @JobOwner + @q + N',' + @crlf +
N' @category_name = N' + @q + @CategoryName + @q + N';' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_add_jobstep' + @crlf +
N' @job_name = N' + @q + @jobName + @q + N',' + @crlf +
N' @step_id = 1,' + @crlf +
N' @step_name = N' + @q + N'Snapshot index fragmentation all databases' + @q + N',' + @crlf +
N' @subsystem = N' + @q + N'TSQL' + @q + N',' + @crlf +
N' @database_name = N' + @q + N'master' + @q + N',' + @crlf +
N' @command = N' + @q + REPLACE(@stepCmd, @q, @q + @q) + @q + N',' + @crlf +
N' @retry_attempts = 0,' + @crlf +
N' @on_success_action = 1,' + @crlf +
N' @on_fail_action = 2;' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_add_schedule' + @crlf +
N' @schedule_name = N' + @q + @jobName + N' Weekly Sat ' + CAST(@RunHour AS nvarchar(2)) + N':00' + @q + N',' + @crlf +
N' @freq_type = 8,' + @crlf + -- weekly
N' @freq_interval = 64,' + @crlf + -- Saturday
N' @freq_recurrence_factor = 1,' + @crlf +
N' @active_start_time = ' + CAST(@schedTS AS nvarchar(10)) + N';' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_attach_schedule' + @crlf +
N' @job_name = N' + @q + @jobName + @q + N',' + @crlf +
N' @schedule_name = N' + @q + @jobName + N' Weekly Sat ' + CAST(@RunHour AS nvarchar(2)) + N':00' + @q + N';' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + @jobName + @q + N';' + @crlf +
N'GO' + @crlf;
SELECT @ddl AS ddl;Generates clean DDL, confirmed by creating the table and job against this instance.
Generate-CollectorJob-QueryStore.sql
Top 50 queries by average CPU from the most recently completed Query Store runtime stats interval, per database, every 30 minutes. Databases without Query Store enabled are silently skipped, not an error.
/*
Script Name : Generate-CollectorJob-QueryStore
Category : collectors
Purpose : Generates DDL to create the DBA - Collect Query Store SQL Agent job.
Creates the target database and collector.QueryStore table if absent,
then outputs T-SQL to install a recurring Query Store collection job.
The job iterates all online user databases with QS enabled and inserts
the top 50 queries by average CPU from the most recently completed
runtime stats interval. Databases without QS are silently skipped.
Edit parameters, review output, then run on the target instance.
Author : Peter Whyte (https://sqldba.blog)
Requires : sysadmin (to run generated DDL); VIEW DATABASE STATE per database at job runtime
Notes : Default interval: every 30 minutes.
Query Store must be enabled on each target database to collect data.
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
-- ── Parameters ────────────────────────────────────────────────────────────────
DECLARE @TargetDatabase sysname = N'DBAMonitor'; -- created if absent
DECLARE @JobOwner sysname = N'sa';
DECLARE @CategoryName nvarchar(128) = N'DBA Collectors';
DECLARE @IntervalMinutes int = 30;
-- ─────────────────────────────────────────────────────────────────────────────
DECLARE @q nchar(1) = NCHAR(39);
DECLARE @crlf nvarchar(2) = CHAR(13) + CHAR(10);
DECLARE @ddl nvarchar(max) = N'';
DECLARE @jobName sysname = N'DBA - Collect Query Store';
DECLARE @stepCmd nvarchar(max);
-- ── Step command (| = single-quote placeholder) ────────────────────────────────
-- Builds @sql via string concatenation so @db is evaluated, not embedded literally.
-- N|...| segments become N'...' after REPLACE; || inside them → '' (escaped quote).
-- || || → '' '' which within a N-string equals the space character literal ' '.
SET @stepCmd = REPLACE(
N'SET NOCOUNT ON;
DECLARE @db sysname;
DECLARE @dbid int;
DECLARE @sql nvarchar(max);
DECLARE db_cur CURSOR LOCAL FAST_FORWARD FOR
SELECT name FROM sys.databases
WHERE state_desc = |ONLINE| AND database_id > 4 AND is_read_only = 0;
OPEN db_cur;
FETCH NEXT FROM db_cur INTO @db;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @dbid = DB_ID(@db);
SET @sql =
N|DECLARE @iid BIGINT; |
+ N|SELECT TOP 1 @iid = runtime_stats_interval_id FROM [| + @db + N|].sys.query_store_runtime_stats_interval |
+ N|WHERE end_time < GETDATE() ORDER BY end_time DESC; |
+ N|IF @iid IS NOT NULL AND EXISTS ( |
+ N| SELECT 1 FROM [| + @db + N|].sys.database_query_store_options |
+ N| WHERE desired_state_desc IN (N||READ_WRITE||, N||READ_ONLY||)) |
+ N|BEGIN |
+ N| INSERT INTO [<<DB>>].[collector].[QueryStore] |
+ N| (server_name, collection_time, database_name, query_id, query_sql_text, |
+ N| plan_id, query_plan_hash, interval_start, interval_end, count_executions, |
+ N| avg_cpu_ms, avg_duration_ms, avg_logical_io_reads, avg_rowcount, |
+ N| is_forced_plan, plan_forcing_type_desc) |
+ N| SELECT TOP 50 @@SERVERNAME, GETDATE(), DB_NAME(| + CAST(@dbid AS nvarchar(10)) + N|), q.query_id, |
+ N| LEFT(REPLACE(REPLACE(qt.query_sql_text, CHAR(13), || ||), CHAR(10), || ||), 500), |
+ N| p.plan_id, CONVERT(char(32), p.query_plan_hash, 2), |
+ N| rsi.start_time, rsi.end_time, rs.count_executions, |
+ N| CAST(rs.avg_cpu_time / 1000.0 AS decimal(12,2)), |
+ N| CAST(rs.avg_duration / 1000.0 AS decimal(12,2)), |
+ N| rs.avg_logical_io_reads, CAST(rs.avg_rowcount AS bigint), |
+ N| p.is_forced_plan, p.plan_forcing_type_desc |
+ N| FROM [| + @db + N|].sys.query_store_query q |
+ N| JOIN [| + @db + N|].sys.query_store_query_text qt ON qt.query_text_id = q.query_text_id |
+ N| JOIN [| + @db + N|].sys.query_store_plan p ON p.query_id = q.query_id |
+ N| JOIN [| + @db + N|].sys.query_store_runtime_stats rs ON rs.plan_id = p.plan_id |
+ N| AND rs.runtime_stats_interval_id = @iid |
+ N| JOIN [| + @db + N|].sys.query_store_runtime_stats_interval rsi |
+ N| ON rsi.runtime_stats_interval_id = rs.runtime_stats_interval_id |
+ N| WHERE q.is_internal_query = 0 |
+ N| ORDER BY rs.avg_cpu_time DESC; |
+ N|END|;
BEGIN TRY
EXEC sp_executesql @sql;
END TRY
BEGIN CATCH
-- Skip inaccessible or incompatible databases
END CATCH;
FETCH NEXT FROM db_cur INTO @db;
END;
CLOSE db_cur;
DEALLOCATE db_cur;'
, N'|', NCHAR(39));
SET @stepCmd = REPLACE(@stepCmd, N'<<DB>>', @TargetDatabase);
-- ═══════════════════════════════════════════════════════════════════════════════
-- DDL output
-- ═══════════════════════════════════════════════════════════════════════════════
SET @ddl =
N'-- ================================================================' + @crlf +
N'-- Generated by Generate-CollectorJob-QueryStore.sql' + @crlf +
N'-- Server : ' + @@SERVERNAME + @crlf +
N'-- Target DB : ' + @TargetDatabase + @crlf +
N'-- Generated : ' + CONVERT(nvarchar(20), GETDATE(), 120) + @crlf +
N'-- ================================================================' + @crlf + @crlf;
-- ── 1. Target database ────────────────────────────────────────────────────────
SET @ddl +=
N'IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = N' + @q + @TargetDatabase + @q + N')' + @crlf +
N' CREATE DATABASE [' + @TargetDatabase + N'];' + @crlf +
N'GO' + @crlf + @crlf;
-- ── 2. Collector schema ───────────────────────────────────────────────────────
SET @ddl +=
N'IF NOT EXISTS (SELECT 1 FROM [' + @TargetDatabase + N'].sys.schemas WHERE name = N' + @q + N'collector' + @q + N')' + @crlf +
N' EXEC [' + @TargetDatabase + N'].sys.sp_executesql N' + @q + N'CREATE SCHEMA collector' + @q + N';' + @crlf +
N'GO' + @crlf + @crlf;
-- ── 3. QueryStore table ───────────────────────────────────────────────────────
SET @ddl +=
N'IF NOT EXISTS (' + @crlf +
N' SELECT 1 FROM [' + @TargetDatabase + N'].sys.objects o' + @crlf +
N' JOIN [' + @TargetDatabase + N'].sys.schemas s ON s.schema_id = o.schema_id' + @crlf +
N' WHERE o.name = N' + @q + N'QueryStore' + @q + N' AND s.name = N' + @q + N'collector' + @q + N')' + @crlf +
N'CREATE TABLE [' + @TargetDatabase + N'].[collector].[QueryStore] (' + @crlf +
N' id bigint IDENTITY(1,1) PRIMARY KEY,' + @crlf +
N' server_name nvarchar(128) NOT NULL,' + @crlf +
N' collection_time datetime2 NOT NULL,' + @crlf +
N' database_name nvarchar(128),' + @crlf +
N' query_id bigint,' + @crlf +
N' query_sql_text nvarchar(500),' + @crlf +
N' plan_id bigint,' + @crlf +
N' query_plan_hash char(32),' + @crlf +
N' interval_start datetime2,' + @crlf +
N' interval_end datetime2,' + @crlf +
N' count_executions bigint,' + @crlf +
N' avg_cpu_ms decimal(12,2),' + @crlf +
N' avg_duration_ms decimal(12,2),' + @crlf +
N' avg_logical_io_reads bigint,' + @crlf +
N' avg_rowcount bigint,' + @crlf +
N' is_forced_plan bit,' + @crlf +
N' plan_forcing_type_desc nvarchar(60)' + @crlf +
N');' + @crlf +
N'GO' + @crlf + @crlf;
-- ── 4. Agent category ─────────────────────────────────────────────────────────
SET @ddl +=
N'USE msdb;' + @crlf +
N'GO' + @crlf + @crlf +
N'IF NOT EXISTS (SELECT 1 FROM msdb.dbo.syscategories WHERE name = N' + @q + @CategoryName + @q + N' AND category_class = 1)' + @crlf +
N' EXEC msdb.dbo.sp_add_category' + @crlf +
N' @class = N' + @q + N'JOB' + @q + N', @type = N' + @q + N'LOCAL' + @q + N', @name = N' + @q + @CategoryName + @q + N';' + @crlf +
N'GO' + @crlf + @crlf;
-- ── 5. Job + step + schedule ──────────────────────────────────────────────────
SET @ddl +=
N'-- Job: ' + @jobName + @crlf +
N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + @jobName + @q + N')' + @crlf +
N' EXEC msdb.dbo.sp_delete_job @job_name = N' + @q + @jobName + @q + N', @delete_unused_schedule = 1;' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_add_job' + @crlf +
N' @job_name = N' + @q + @jobName + @q + N',' + @crlf +
N' @enabled = 1,' + @crlf +
N' @owner_login_name = N' + @q + @JobOwner + @q + N',' + @crlf +
N' @category_name = N' + @q + @CategoryName + @q + N';' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_add_jobstep' + @crlf +
N' @job_name = N' + @q + @jobName + @q + N',' + @crlf +
N' @step_id = 1,' + @crlf +
N' @step_name = N' + @q + N'Collect QS top queries all databases' + @q + N',' + @crlf +
N' @subsystem = N' + @q + N'TSQL' + @q + N',' + @crlf +
N' @database_name = N' + @q + N'master' + @q + N',' + @crlf +
N' @command = N' + @q + REPLACE(@stepCmd, @q, @q + @q) + @q + N',' + @crlf +
N' @retry_attempts = 0,' + @crlf +
N' @on_success_action = 1,' + @crlf +
N' @on_fail_action = 2;' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_add_schedule' + @crlf +
N' @schedule_name = N' + @q + @jobName + N' Every ' + CAST(@IntervalMinutes AS nvarchar(5)) + N'min' + @q + N',' + @crlf +
N' @freq_type = 4,' + @crlf + -- daily recurring
N' @freq_interval = 1,' + @crlf +
N' @freq_subday_type = 4,' + @crlf + -- minutes
N' @freq_subday_interval = ' + CAST(@IntervalMinutes AS nvarchar(5)) + N';' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_attach_schedule' + @crlf +
N' @job_name = N' + @q + @jobName + @q + N',' + @crlf +
N' @schedule_name = N' + @q + @jobName + N' Every ' + CAST(@IntervalMinutes AS nvarchar(5)) + N'min' + @q + N';' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + @jobName + @q + N';' + @crlf +
N'GO' + @crlf;
SELECT @ddl AS ddl;Generates clean DDL, confirmed against this instance.
Generate-CollectorJob-ErrorLog.sql
Recurring error log collection, inserting only entries newer than the latest log_date already stored, every 10 minutes. Login-succeeded and routine backup messages are filtered out deliberately, noisy and low signal.
/*
Script Name : Generate-CollectorJob-ErrorLog
Category : collectors
Purpose : Generates DDL to create the DBA - Collect Error Log SQL Agent job.
Creates the target database and collector.ErrorLog table if absent,
then outputs T-SQL to install a recurring error log collection job.
Each run inserts only entries newer than the latest log_date already
stored for this server, preventing duplicate rows.
Edit parameters, review output, then run on the target instance.
Author : Peter Whyte (https://sqldba.blog)
Requires : sysadmin (to run generated DDL); VIEW SERVER STATE, EXECUTE xp_readerrorlog at job runtime
Notes : Default interval: every 10 minutes.
Login succeeded and backup messages are suppressed (noisy, low signal).
*/
-- SAFE:ReadOnly
-- IMPACT:Low
SET NOCOUNT ON;
-- ── Parameters ────────────────────────────────────────────────────────────────
DECLARE @TargetDatabase sysname = N'DBAMonitor'; -- created if absent
DECLARE @JobOwner sysname = N'sa';
DECLARE @CategoryName nvarchar(128) = N'DBA Collectors';
DECLARE @IntervalMinutes int = 10;
-- ─────────────────────────────────────────────────────────────────────────────
DECLARE @q nchar(1) = NCHAR(39);
DECLARE @crlf nvarchar(2) = CHAR(13) + CHAR(10);
DECLARE @ddl nvarchar(max) = N'';
DECLARE @jobName sysname = N'DBA - Collect Error Log';
DECLARE @stepCmd nvarchar(max);
-- ── Step command (| = single-quote placeholder) ────────────────────────────────
-- @since: latest log_date already stored for this server, or 24h ago as bootstrap.
-- xp_readerrorlog is called with @since as start_time to reduce result set server-side.
-- The WHERE LogDate > @since guard handles edge case where @since matches boundary rows.
SET @stepCmd = REPLACE(
N'SET NOCOUNT ON;
DECLARE @since datetime;
SELECT @since = MAX(log_date)
FROM [<<DB>>].[collector].[ErrorLog]
WHERE server_name = @@SERVERNAME;
IF @since IS NULL
SET @since = DATEADD(HOUR, -24, GETDATE());
CREATE TABLE #errorlog (
LogDate DATETIME,
ProcessInfo NVARCHAR(100),
Text NVARCHAR(4000)
);
INSERT INTO #errorlog (LogDate, ProcessInfo, Text)
EXEC sys.xp_readerrorlog 0, 1, NULL, NULL, @since, NULL, N|asc|;
INSERT INTO [<<DB>>].[collector].[ErrorLog]
(server_name, collection_time, log_date, process_info, severity, message_text)
SELECT
@@SERVERNAME AS server_name,
GETDATE() AS collection_time,
LogDate AS log_date,
ProcessInfo AS process_info,
CASE
WHEN Text LIKE |Error%| THEN |Error|
WHEN Text LIKE |Warning%| THEN |Warning|
WHEN Text LIKE |%severity%1[5-9]%| OR Text LIKE |%severity%2[0-4]%| THEN |Error|
ELSE |Info|
END AS severity,
LEFT(Text, 2000) AS message_text
FROM #errorlog
WHERE LogDate > @since
AND Text NOT LIKE |%Login succeeded%|
AND Text NOT LIKE |%Log was backed up%|
AND Text NOT LIKE |BACKUP DATABASE%|
AND Text NOT LIKE |BACKUP LOG%|
AND LEN(LTRIM(Text)) > 0;
DROP TABLE #errorlog;'
, N'|', NCHAR(39));
SET @stepCmd = REPLACE(@stepCmd, N'<<DB>>', @TargetDatabase);
-- ═══════════════════════════════════════════════════════════════════════════════
-- DDL output
-- ═══════════════════════════════════════════════════════════════════════════════
SET @ddl =
N'-- ================================================================' + @crlf +
N'-- Generated by Generate-CollectorJob-ErrorLog.sql' + @crlf +
N'-- Server : ' + @@SERVERNAME + @crlf +
N'-- Target DB : ' + @TargetDatabase + @crlf +
N'-- Generated : ' + CONVERT(nvarchar(20), GETDATE(), 120) + @crlf +
N'-- ================================================================' + @crlf + @crlf;
-- ── 1. Target database ────────────────────────────────────────────────────────
SET @ddl +=
N'IF NOT EXISTS (SELECT 1 FROM sys.databases WHERE name = N' + @q + @TargetDatabase + @q + N')' + @crlf +
N' CREATE DATABASE [' + @TargetDatabase + N'];' + @crlf +
N'GO' + @crlf + @crlf;
-- ── 2. Collector schema ───────────────────────────────────────────────────────
SET @ddl +=
N'IF NOT EXISTS (SELECT 1 FROM [' + @TargetDatabase + N'].sys.schemas WHERE name = N' + @q + N'collector' + @q + N')' + @crlf +
N' EXEC [' + @TargetDatabase + N'].sys.sp_executesql N' + @q + N'CREATE SCHEMA collector' + @q + N';' + @crlf +
N'GO' + @crlf + @crlf;
-- ── 3. ErrorLog table ─────────────────────────────────────────────────────────
SET @ddl +=
N'IF NOT EXISTS (' + @crlf +
N' SELECT 1 FROM [' + @TargetDatabase + N'].sys.objects o' + @crlf +
N' JOIN [' + @TargetDatabase + N'].sys.schemas s ON s.schema_id = o.schema_id' + @crlf +
N' WHERE o.name = N' + @q + N'ErrorLog' + @q + N' AND s.name = N' + @q + N'collector' + @q + N')' + @crlf +
N'CREATE TABLE [' + @TargetDatabase + N'].[collector].[ErrorLog] (' + @crlf +
N' id bigint IDENTITY(1,1) PRIMARY KEY,' + @crlf +
N' server_name nvarchar(128) NOT NULL,' + @crlf +
N' collection_time datetime2 NOT NULL,' + @crlf +
N' log_date datetime,' + @crlf +
N' process_info nvarchar(100),' + @crlf +
N' severity nvarchar(20),' + @crlf +
N' message_text nvarchar(2000)' + @crlf +
N');' + @crlf +
N'GO' + @crlf + @crlf;
-- ── 4. Agent category ─────────────────────────────────────────────────────────
SET @ddl +=
N'USE msdb;' + @crlf +
N'GO' + @crlf + @crlf +
N'IF NOT EXISTS (SELECT 1 FROM msdb.dbo.syscategories WHERE name = N' + @q + @CategoryName + @q + N' AND category_class = 1)' + @crlf +
N' EXEC msdb.dbo.sp_add_category' + @crlf +
N' @class = N' + @q + N'JOB' + @q + N', @type = N' + @q + N'LOCAL' + @q + N', @name = N' + @q + @CategoryName + @q + N';' + @crlf +
N'GO' + @crlf + @crlf;
-- ── 5. Job + step + schedule ──────────────────────────────────────────────────
SET @ddl +=
N'-- Job: ' + @jobName + @crlf +
N'IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = N' + @q + @jobName + @q + N')' + @crlf +
N' EXEC msdb.dbo.sp_delete_job @job_name = N' + @q + @jobName + @q + N', @delete_unused_schedule = 1;' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_add_job' + @crlf +
N' @job_name = N' + @q + @jobName + @q + N',' + @crlf +
N' @enabled = 1,' + @crlf +
N' @owner_login_name = N' + @q + @JobOwner + @q + N',' + @crlf +
N' @category_name = N' + @q + @CategoryName + @q + N';' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_add_jobstep' + @crlf +
N' @job_name = N' + @q + @jobName + @q + N',' + @crlf +
N' @step_id = 1,' + @crlf +
N' @step_name = N' + @q + N'Collect new error log entries' + @q + N',' + @crlf +
N' @subsystem = N' + @q + N'TSQL' + @q + N',' + @crlf +
N' @database_name = N' + @q + N'master' + @q + N',' + @crlf +
N' @command = N' + @q + REPLACE(@stepCmd, @q, @q + @q) + @q + N',' + @crlf +
N' @retry_attempts = 0,' + @crlf +
N' @on_success_action = 1,' + @crlf +
N' @on_fail_action = 2;' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_add_schedule' + @crlf +
N' @schedule_name = N' + @q + @jobName + N' Every ' + CAST(@IntervalMinutes AS nvarchar(5)) + N'min' + @q + N',' + @crlf +
N' @freq_type = 4,' + @crlf + -- daily recurring
N' @freq_interval = 1,' + @crlf +
N' @freq_subday_type = 4,' + @crlf + -- minutes
N' @freq_subday_interval = ' + CAST(@IntervalMinutes AS nvarchar(5)) + N';' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_attach_schedule' + @crlf +
N' @job_name = N' + @q + @jobName + @q + N',' + @crlf +
N' @schedule_name = N' + @q + @jobName + N' Every ' + CAST(@IntervalMinutes AS nvarchar(5)) + N'min' + @q + N';' + @crlf + @crlf +
N'EXEC msdb.dbo.sp_add_jobserver @job_name = N' + @q + @jobName + @q + N';' + @crlf +
N'GO' + @crlf;
SELECT @ddl AS ddl;Generates clean DDL, confirmed against this instance.
How To Run From The Repo
git clone https://github.com/peterwhyte-lgtm/dba-tools
cd dba-tools
.\Initialize-Environment.ps1
.\run.ps1 Generate-CollectorJob-AgHealth
.\run.ps1 Generate-CollectorJob-IndexFragmentation
.\run.ps1 Generate-CollectorJob-QueryStore
.\run.ps1 Generate-CollectorJob-ErrorLog
# Review the generated DDL, then run it on the target instance
These scripts live in the repo at:
sql/collectors/Generate-CollectorJob-AgHealth.sqlsql/collectors/Generate-CollectorJob-IndexFragmentation.sqlsql/collectors/Generate-CollectorJob-QueryStore.sqlsql/collectors/Generate-CollectorJob-ErrorLog.sql
Understanding the Results
- A “maximum key length” warning at table creation is not something to dismiss because the CREATE TABLE succeeded. It’s a deferred failure waiting for the right (or wrong) data, treat it the same as an error and fix the key design before deploying.
- Zero rows in
AgHealthCurrenton a non-AG instance is the correct, honest result, the collector isn’t broken, there’s genuinely nothing to report. - A database silently missing from Query Store collection almost always means Query Store isn’t enabled there, check Get Query Store Status before assuming the collector skipped it in error.
Best Practices
- Watch for index/key-length warnings on any custom table you add to this collector pattern, especially ones with multi-column natural keys built from
nvarchar(128)-style identifier columns, they add up fast. - Schedule
IndexFragmentationfor a genuinely quiet window,SAMPLEDmode is cheap per index but adds up across every table in a large database. - Enable Query Store on databases you actually want covered before relying on this collector, it can’t collect what isn’t running.
- Review the error log collector’s suppression list periodically, what counts as noise can change as an environment’s normal behavior changes.
Related Scripts
You may also find these scripts useful:
- Collectors and Baseline Infrastructure (hub)
- Generate Collector Alerts
- High Availability (pillar)
- Get Index Fragmentation
- DBA Scripts: The Complete Guide, the map across every script on this site
Frequently Asked Questions
My table creates fine despite the “maximum key length” warning, do I actually need to fix it?
Yes. The warning means the table works today and can fail on a future insert once a specific combination of long values occurs, exactly the kind of bug that surfaces at the worst possible moment. Narrow the key columns or switch to a surrogate key before relying on the table in production.
Why does the AG Health collector return zero rows on my instance?
Either no Availability Group is configured (the honest, correct result on a standalone instance), or the SQL Agent job hasn’t run yet. Check sys.availability_groups directly to confirm which case applies.
Summary
Four collectors covering AG health, index fragmentation, Query Store, and the error log, each on its own natural schedule. Generate the DDL, review it, run it on the target instance, and you have four SQL Agent jobs building history from the moment they are installed. Query the Current tables for the latest state, or the paired history tables for when each value changed.
Leave a Reply