DBA Scripts: Collectors and Baseline Infrastructure

Part of the DBA-Tools Project, in the Performance and Troubleshooting area.

From “Check When Asked” to “Already Watching”

Every other pillar on this site answers a question the moment you ask it: what’s blocking right now, what are the current wait stats, how much free space is on disk today. The Collectors pillar is different. It’s the layer underneath those questions, a set of recurring SQL Agent jobs that quietly write evidence into DBAMonitor tables on a schedule, so that when something does go wrong, the history is already there instead of gone by the time anyone thinks to look.

Twelve collectors, three delta scripts, and one cross-cutting alert job, built and tested against a real local SQL Server 2025 instance. Testing them properly, actually creating the tables, installing the jobs, and running the real step logic rather than just reading the SQL, found six genuine bugs. Every one is documented in its member post with the real error message, the real fix, and real captured output afterward.

Why This Matters

• Transient problems need standing evidence. Blocking chains, deadlocks, and TempDB pressure spikes can resolve in minutes. A DBA asked about “the slowness at 2pm” an hour later has nothing to look at unless something was already collecting.
• Cumulative counters are useless without a second snapshot. Wait stats, perfmon counters, and file I/O stats all accumulate since the instance last restarted. One snapshot answers “how much since startup,” almost never the actual question. The delta scripts in this pillar exist specifically to answer “what changed in this window.”
• An alert only helps if something is watching. Twelve collectors writing to twelve tables is just data until something reads across all of them and says, plainly, this one’s CRITICAL. That’s what Generate-CollectorAlertJob.sql does.

The Five Member Posts

Six Real Bugs, Found by Actually Running Them

Reading SQL and running SQL find different classes of bug. Every one of these passed a read-through. None of them passed a real execution the first time:

This is the actual value of testing infrastructure against a real instance rather than trusting that clean-looking T-SQL runs clean: six defects that would otherwise have shipped, three of them (collation, key width, wrong table name) genuinely common traps worth recognizing in your own scripts even outside this pillar.

How They Fit Together

Collectors (write evidence on a schedule)
├── Blocking, Deadlocks           → collector.Blocking, collector.Deadlocks
├── WaitStats, Perfmon, StorageIO → collector.WaitStats, .Perfmon, .StorageIO
├── DatabaseGrowth, VlfCount      → collector.DatabaseGrowthCurrent, .VlfCountCurrent (system-versioned)
├── Tempdb                        → collector.Tempdb
└── AgHealth, IndexFragmentation, → collector.AgHealthCurrent (system-versioned), .IndexFragmentation,
    QueryStore, ErrorLog            .QueryStore, .ErrorLog

Delta scripts (turn cumulative snapshots into "what changed")
├── Get-WaitStatsDelta.sql
├── Get-PerfmonDelta.sql
└── Get-StorageIODelta.sql

Alert job (reads across everything above, on its own schedule)
└── Generate-CollectorAlertJob.sql → CRITICAL / WARNING findings, one place

Install the collectors first, they’re the foundation. Add the delta scripts once at least two collection intervals have run. Install the alert job last, it depends on the collector tables already existing.

Best Practices Across the Series

• Install collectors on every production instance permanently, not only when chasing a specific incident, the value is having history from before you knew you needed it.
• Watch for the same traps this pillar found: catalog view collation conflicts, composite natural keys that grow past 900 bytes, and system-versioned tables that don’t carry a manual timestamp column, all three recur outside this pillar too.
• Route the alert job’s findings into whatever alerting channel is already in use rather than checking the table manually.
• Treat a CRITICAL finding from the alert job as corroborated evidence, cross-check it against the specific collector’s own post for full context before acting.

See Also

• DBA Scripts: The Complete Guide, the full pillar index
• Run a Full SQL Server Health Check, the workflow that reads across these collector tables (and 36 other checks) to produce a single prioritized assessment
• Installation and Patching (pillar)
• Maintenance Job Framework (pillar)
• SQL Server Wait Statistics (library)

Summary

Twelve collectors, three delta scripts, and one alert job, all covering the gap between “something went wrong” and “here’s the evidence.” Built and tested against a real instance rather than just read through, this pillar surfaced six genuine bugs, three of them recognizable traps (catalog view collation, clustered index key width, system-versioned table assumptions) worth watching for anywhere else in a SQL Server environment. Every fix is documented in its member post with the real error and the real verified output afterward.