A reference library of SQL and KQL queries
Why keep a query library, how to organise it (catalog, naming, metadata), search it and keep it reviewed.
Every DBA owns dozens of scattered queries: a .sql file on the desktop, a KQL query in the portal history, a blog script modified three times. On incident day you cannot find them, or worse, you rewrite the same thing, only worse. A reference library that is organised and reviewed changes that.
Why keep one
- Save time during an incident: "who is blocking whom?", "which Agent job failed?", "where does the ingestion cost spike come from?" already have a written answer.
- Share the team's knowledge instead of leaving it in one person's head.
- Know what a query costs before running it: permissions, impact on a busy server, risk of changing something.
- Serve as the index for an AI assistant: it searches and reuses before writing.
A reference library is not an execution framework: nothing runs by itself, no pipeline. Queries that an application runs stay in that application's repository; the library keeps the ones a DBA runs by hand.
Organise: the catalog
I classify by scope, then by category.
query-library/
├── Catalog/queries.json generated, never edited by hand
├── Tools/Build-Catalog.ps1 builds or checks the catalog
├── Tests/ Pester tests
├── SQL/<Category>/*.sql generic T-SQL, any database
├── KQL/<Category>/*.kql generic KQL, any workspace
└── Projects/<Project>/... queries tied to one application or database
Categories: Security, Performance, SQLAgent, Replication, Availability, Maintenance, Storage, BackupRecovery, Configuration, Diagnostics, Development. A category folder exists only when it holds at least one query.
The classification rule: a query that reads only system objects and works anywhere goes to generic; one that reads an application's tables goes to the project, once for the whole family of databases, never one copy per database.
Name and describe: the metadata header
Every file starts with a header. What the path already says (language, category) is not repeated.
/*
ID: SQL-AGENT-005
Name: SQL Agent job execution statistics
Description: Runs, failure rate, avg/max duration per job over 7 and 30 days.
Keywords: agent, job, history, duration, failure rate
Platforms: SQLServer, SQLMI
Requires: msdb: SQLAgentReaderRole or sysadmin
Access: ReadOnly
Impact: Low
Source: Internal
Status: Imported
*/
KQL has no block comment: its header is a run of // lines.
| Field | Role |
|---|---|
ID |
SQL-<CODE>-NNN, never reused, even after deprecation |
Description |
one line: the question the query answers |
Platforms |
only what is verified (SQLServer, AzureSQL, SQLMI; LogAnalytics, ResourceGraph) |
Requires |
permissions, tables, diagnostic categories |
Access |
ReadOnly, Generator (prints DROP/KILL to review), DDL, DML... |
Impact |
Low, Medium, High: cost and locking on a busy server |
Status |
Imported, Validated, Deprecated |
Parameters are DECLARE (T-SQL) or let (KQL) statements at the top, with defaults that never name a production object.
Search
The JSON catalog is generated from the headers, so it always matches the files. Searching takes a few lines of PowerShell:
$catalog = (Get-Content ./Catalog/queries.json -Raw | ConvertFrom-Json).queries
$catalog | Where-Object { "$($_.name) $($_.description) $($_.keywords)" -match 'blocking' } |
Format-Table id, name, platforms, access, status, path
$catalog | Where-Object { $_.platforms -contains 'SQLMI' -and $_.category -eq 'Replication' }
Search order: the target database, then its project, then the generic queries.
Keep it reviewed
Importedby default. A query becomesValidatedonly after a read-only run, with the evidence recorded (date, platform). Never claim it without that run.Deprecatedrather than delete: the file stays, withReplacedBy.- An automatic check (
Build-Catalog -Checkplus Pester) fails on: missing or unknown field, duplicate ID, ID inconsistent with its folder,Validatedwithout evidence, stale catalog. A T-SQL parser (ScriptDom) checks syntax, a scan looks for secrets. - Review before merge: every change goes through a pull request, with the catalog regenerated in the same commit.
Lessons learned
- Scripts inherited from an older collection are kept as they were and flagged (
one-off,broken); improvements go into a new query that references them: you keep history without polluting the library. - Mind the licences: a script from a blog or forum is not free to copy. I write from the Microsoft documentation and reference the original.
- Never run a library query on production without reading its header first, especially
Generatorones: they print SQL, they must never execute it. - No secret, token or personal data in the library: a test enforces it.