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.sqldoes.
Start Here
The Five Member Posts
💥 Collect Blocking and Deadlock Events
Two idempotent collectors, blocking every 2 minutes, deadlocks every 5, writing nothing when the server is quiet. Full deadlock XML preserved for real post-incident investigation.
⚡ Collect Performance Baselines
Wait stats, perfmon counters, and storage I/O, all cumulative, all paired with a matching Get-*Delta.sql script that compares the two most recent snapshots with restart-aware detection. Proven end to end with two real snapshots taken minutes apart.
💾 Collect Capacity and Temp DB Baselines
Database growth, VLF count, and TempDB space and session consumers. Three real bugs found and fixed here alone: a UNION/ORDER BY scoping error, and the same sys.databases collation-conflict trap hitting two separate scripts.
🧞 Collect Health and Configuration Baselines
AG health, index fragmentation, Query Store top queries, and error log entries. Testing the AG health collector surfaced a composite clustered index key exceeding SQL Server’s own 900-byte limit, a defect that passes table creation silently.
🚨 Generate Collector Alerts
The cross-cutting job that reads every collector table above and raises CRITICAL/WARNING findings in one place. Two bugs fixed, then a genuine end-to-end run surfacing 8 real CRITICAL TempDB findings that corroborated the capacity collector’s own results independently.
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.
Frequently Asked Questions
Why collect a baseline when the DMVs already have the data?
Because most of it is cumulative since the last restart and none of it survives one. Without collection you can say what the totals are, but not what normal looks like or when it changed.
How long before a baseline is useful?
Enough to cover one full business cycle, including a month-end. A week of data will mislead you about anything that only happens monthly, and month-end is usually the interesting case.
Is collecting this going to slow the server down?
Not meaningfully, if it is sampled rather than continuous. The cost is disk for retention, which is why a purge policy is part of the collector rather than an afterthought.
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.
Leave a Reply