Une bibliothèque de référence de requêtes SQL et KQL
Pourquoi garder une bibliothèque de requêtes, comment l'organiser (catalogue, nommage, métadonnées), la parcourir et la garder à jour.
Tout DBA possède des dizaines de requêtes éparpillées : un fichier .sql sur le bureau, une requête KQL dans l'historique du portail, un script récupéré sur un blog et modifié trois fois. Le jour de l'incident, on ne les retrouve pas, ou pire, on réécrit la même chose en moins bien. Une bibliothèque de référence, organisée et revue, change ça.
Pourquoi en garder une
- Gagner du temps en incident : « qui bloque qui ? », « quel job Agent a échoué ? », « d'où vient le pic de coûts d'ingestion ? » ont déjà une réponse écrite.
- Partager le savoir de l'équipe au lieu de le laisser dans la tête d'une personne.
- Savoir ce qu'une requête coûte avant de la lancer : permissions, impact sur un serveur occupé, risque de modification.
- Servir d'index à un assistant IA : il cherche et réutilise avant d'écrire.
Une bibliothèque de référence n'est pas un framework d'exécution : rien ne s'y lance tout seul, pas de pipeline. Les requêtes qu'une application exécute restent dans le dépôt de cette application ; la bibliothèque garde celles qu'un DBA lance à la main.
Organiser : le catalogue
Je classe par portée, puis par catégorie.
query-library/
├── Catalog/queries.json généré, jamais édité à la main
├── Tools/Build-Catalog.ps1 construit ou vérifie le catalogue
├── Tests/ tests Pester
├── SQL/<Catégorie>/*.sql T-SQL générique, toute base
├── KQL/<Catégorie>/*.kql KQL générique, tout workspace
└── Projects/<Projet>/... requêtes liées à une application ou une base
Catégories : Security, Performance, SQLAgent, Replication, Availability, Maintenance, Storage, BackupRecovery, Configuration, Diagnostics, Development. Un dossier de catégorie n'existe que s'il contient au moins une requête.
La règle de classement : une requête qui ne lit que des objets système et marche partout va en générique ; celle qui lit les tables d'une application va dans le projet, une seule fois pour toute la famille de bases, jamais une copie par base.
Nommer et décrire : l'en-tête de métadonnées
Chaque fichier commence par un en-tête. Ce que le chemin dit déjà (langage, catégorie) n'est pas répété.
/*
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 n'a pas de commentaire de bloc : l'en-tête est une suite de lignes //.
| Champ | Rôle |
|---|---|
ID |
SQL-<CODE>-NNN, jamais réutilisé, même après dépréciation |
Description |
une ligne : la question à laquelle la requête répond |
Platforms |
uniquement ce qui est vérifié (SQLServer, AzureSQL, SQLMI ; LogAnalytics, ResourceGraph) |
Requires |
permissions, tables, catégories de diagnostic |
Access |
ReadOnly, Generator (imprime des DROP/KILL à relire), DDL, DML... |
Impact |
Low, Medium, High : coût et verrouillage sur un serveur occupé |
Status |
Imported, Validated, Deprecated |
Les paramètres sont des DECLARE (T-SQL) ou des let (KQL) en haut du fichier, avec des valeurs par défaut qui ne nomment jamais un objet de production.
Chercher
Le catalogue JSON est généré à partir des en-têtes, donc toujours cohérent avec les fichiers. La recherche tient en quelques lignes de 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' }
Ordre de recherche : la base visée, puis son projet, puis les requêtes génériques.
Garder la bibliothèque revue
Importedpar défaut. Une requête ne devientValidatedqu'après une exécution en lecture seule, avec la preuve notée (date, plateforme). Ne jamais le prétendre sans cette exécution.Deprecatedplutôt que supprimer : le fichier reste, avecReplacedBy.- Un contrôle automatique (
Build-Catalog -Checket Pester) échoue sur : champ manquant ou inconnu, ID en double, ID incohérent avec le dossier,Validatedsans preuve, catalogue périmé. Un parseur T-SQL (ScriptDom) vérifie la syntaxe, un scan cherche les secrets. - Revue avant fusion : toute modification passe par une demande de fusion, avec le catalogue régénéré dans le même commit.
Retour d'expérience
- Les scripts hérités d'une ancienne collection sont gardés tels quels et marqués (
one-off,broken), et les améliorations vont dans une requête neuve qui les référence : on garde l'historique sans le polluer. - Attention aux licences : un script de blog ou de forum n'est pas libre de recopie. J'écris à partir de la documentation Microsoft et je référence l'original.
- Aucune requête de la bibliothèque ne doit être lancée en production sans lecture préalable de son en-tête, surtout les
Generator: ils impriment du SQL, ils ne doivent jamais l'exécuter. - Pas de secret, jeton ou donnée personnelle dans la bibliothèque : un test le vérifie.