LLI Wiki — My Digital Knowledge Base

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

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.

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

Lessons learned

Mastodon LLIMastodon SocialFosstodon