Part of the DBA-Tools Project.
Four Collectors, One Real Table-Design Bug Found Testing Them
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. Testing the AG health collector against a real instance surfaced a genuine table-design bug, a composite key too wide for SQL Server’s own clustered index limit, fixed and verified.
The Bug: A Composite Key Over SQL Server’s 900-Byte Clustered Index Limit
AgHealthCurrent‘s original primary key was (server_name nvarchar(128), ag_name nvarchar(128), replica_server_name nvarchar(256), database_name nvarchar(128)), 640 characters, 1280 bytes as an nvarchar key. SQL Server’s own hard limit for a clustered index key is 900 bytes:
Warning! The maximum key length for a clustered index is 900 bytes.
The index 'PK_AgHealthCurrent' has maximum length of 1280 bytes.
For some combination of large values, the insert/update operation will fail.
The table creates successfully, this is a warning, not an error, which is exactly what makes it dangerous: it works fine until a specific combination of long AG name, long replica FQDN, and long database name lines up, then an insert or update silently fails at exactly the wrong moment. Fixed by narrowing the four key columns to nvarchar(100), nvarchar(100), nvarchar(128), nvarchar(100), 428 characters, 856 bytes, safely under the limit while still generous for real-world names. Recreated and reverified with zero warnings.
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
Requires : sysadmin (to run generated DDL); VIEW SERVER STATE at job runtime
*/
(The full script is in the repo, link below.) No Availability Group is configured on this lab instance, so the table creates and the job runs cleanly but captures zero rows, an honest “not applicable here” result rather than a manufactured AG scenario, documented plainly rather than hidden.
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
Notes : Default schedule: weekly, Saturday at 01:00.
Thresholds: >=30% REBUILD, 10-29% REORGANIZE, <10% NONE.
*/
(The full script is in the repo, link below.) 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
Notes : Default interval: every 30 minutes.
Query Store must be enabled on each target database to collect data.
*/
(The full script is in the repo, link below.) 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
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).
*/
(The full script is in the repo, link below.) 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
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. Testing the AG health collector against a real instance found a genuine table-design defect, a composite key exceeding SQL Server’s own 900-byte clustered index limit, the kind of bug that passes table creation silently and only bites on a specific future insert. Fixed, narrowed, and reverified with zero warnings.
Leave a Reply