Collecter des métriques par base pour décider du placement dans les pools élastiques
Quoi mesurer, d'où le lire (DMV, Azure Monitor, Log Analytics), comment l'historiser, noter une base et tenir compte de la saisonnalité.
Déplacer une base d'un pool élastique à un autre est facile. Savoir quelle base déplacer, et où, est la vraie difficulté, et la réponse ne vaut que ce que valent les chiffres. Cet article traite de la mesure : ce que je collecte par base, d'où ça vient, comment je le stocke et comment j'en fais un score. Le déplacement lui-même est traité dans les deux articles suivants.
Flux de données
flowchart LR
A["Métriques Azure Monitor (par base et par pool)"] --> B["Log Analytics (AzureMetrics)"]
C["Vue de gestion du pool dans master"] --> D["Contrôles ponctuels"]
B --> E["Job nocturne (KQL, un jour à la fois)"]
E --> F["Table d'historique"]
F --> G["Procédure de scoring"]
G --> H["Table de scores (par base et par jour)"]
H --> I["Suggestions de déplacement"]
Quoi collecter
Un pool est un budget partagé : une base compte par la part du budget du pool qu'elle consomme. Voici les signaux que je garde :
| Signal | Pourquoi | Métrique source |
|---|---|---|
| CPU | Première limite atteinte sur la plupart des pools | cpu_percent, cpu_used (vCore) |
| IO données | Pression en lecture, charges à gros scans | physical_data_read_percent |
| Écriture du journal | Les traitements lourds en écriture saturent vite | log_write_percent |
| Workers / sessions | Beaucoup de requêtes concurrentes, même à CPU faible | workers_percent, sessions_percent |
| Stockage | Limite dure du pool, pas de burst possible | storage, storage_percent |
| Part du pool | Ramène tout à « ce que cette base coûte à ses voisines » | calculée |
Les pourcentages d'une base sont relatifs à la limite par base, identique pour toutes les bases d'un pool. Ils sont donc comparables dans un pool, mais pas entre pools de tailles différentes. Je convertis donc toujours en part du pool : vCores utilisés divisés par les vCores du pool, ou pourcentage du pool réparti proportionnellement aux pourcentages des bases.
Où le lire
- Vues de gestion dynamiques :
sys.dm_db_resource_stats(dans chaque base, échantillons de 15 secondes, environ la dernière heure) etsys.elastic_pool_resource_stats(dansmaster, niveau pool, rétention courte). Parfait pour un contrôle pendant un incident, inutilisable pour l'historique et impraticable en boucle sur des milliers de bases. - Métriques Azure Monitor envoyées vers Log Analytics : une seule requête couvre tout le parc, avec une longue rétention. C'est ma source principale.
- Resource Graph : l'appartenance actuelle (quelle base dans quel pool), car une ligne de métrique ne porte que l'identifiant de ressource.
Contrôle ponctuel d'un pool depuis master :
SELECT TOP (12) end_time, elastic_pool_name,
avg_cpu_percent, avg_data_io_percent, avg_log_write_percent,
max_worker_percent, avg_storage_percent
FROM sys.elastic_pool_resource_stats
ORDER BY end_time DESC;
Collecte nocturne en KQL
Chaque nuit, un job récupère la veille, heure par heure, et l'écrit dans une table d'historique. Je garde le maximum et la moyenne par heure, car la moyenne seule cache les pics.
let day = startofday(ago(1d));
AzureMetrics
| where TimeGenerated >= day and TimeGenerated < day + 1d
| where ResourceProvider =~ "MICROSOFT.SQL"
| where MetricName in ("cpu_percent", "log_write_percent", "physical_data_read_percent", "workers_percent")
| extend rid = tolower(_ResourceId)
| where rid has "/databases/"
| extend Server = extract(@"servers/([^/]+)", 1, rid), Db = extract(@"databases/([^/]+)$", 1, rid)
| where Db != "master"
| summarize HourAvg = avg(Average), HourMax = max(Maximum) by bin(TimeGenerated, 1h), Server, Db, MetricName
Avec la géo-réplication, le même nom de base apparaît sur deux serveurs. Je résume d'abord par serveur, puis je garde la copie la plus chargée par heure : c'est le primaire, celui qui coûte de la capacité.
La table d'historique
CREATE TABLE dbo.DbMetricHistory (
MetricDate date NOT NULL,
HourUtc tinyint NOT NULL,
DbName sysname NOT NULL,
PoolName sysname NULL,
MetricName varchar(40) NOT NULL,
HourAvg decimal(9,3) NULL,
HourMax decimal(9,3) NULL,
CONSTRAINT PK_DbMetricHistory PRIMARY KEY (MetricDate, HourUtc, DbName, MetricName)
);
Deux choix comptent. Le job est idempotent : il supprime le jour qu'il va écrire, donc un rejeu ne crée pas de doublons. Et il purge les anciennes lignes (environ deux mois suffisent), car la table grossit de milliers de lignes par jour.
Noter une base
Un score ramène plusieurs métriques à un seul nombre comparable. Le mien est une somme pondérée de signaux normalisés, calculés sur le maximum journalier :
SELECT DbName, MetricDate,
0.50 * cpu_p95 + 0.20 * io_p95 + 0.15 * log_p95 + 0.15 * workers_p95 AS Score
FROM (
SELECT DbName, MetricDate,
MAX(CASE WHEN MetricName='cpu_percent' THEN HourMax END) AS cpu_p95,
MAX(CASE WHEN MetricName='physical_data_read_percent' THEN HourMax END) AS io_p95,
MAX(CASE WHEN MetricName='log_write_percent' THEN HourMax END) AS log_p95,
MAX(CASE WHEN MetricName='workers_percent' THEN HourMax END) AS workers_p95
FROM dbo.DbMetricHistory
GROUP BY DbName, MetricDate
) t;
Les poids sont ce que je remets en cause. Nos pools saturaient en CPU, mais la CPU était sous-pondérée : le score composite était surtout de l'IO et classait mal les pools par rapport à la réalité. Je l'ai vérifié en comparant le classement des pools par score à leur classement par CPU réelle (corrélation de Spearman), puis j'ai changé les poids, recalculé tout l'historique et constaté l'amélioration. Une grille de scoring est une hypothèse : on la teste contre la vérité au niveau du pool.
Saisonnalité
Une semaine de données est un piège. La clôture mensuelle, les batchs de nuit et les jobs hebdomadaires créent des pics qu'une fenêtre courte rate. Ce qui a aidé :
- garder des maxima horaires, pour que la fenêtre nocturne récurrente reste visible ;
- agréger sur au moins un cycle complet (une semaine, idéalement une fin de mois) ;
- regarder le profil par heure d'un pool avant de le déclarer inactif.
Pièges
- Les métriques ne sont pas instantanées : l'ingestion Log Analytics peut avoir du retard, donc on collecte la veille, jamais le jour même.
- La rétention est limitée : relancer un jour déjà sorti de l'espace de travail le supprime définitivement. Vérifier avant de retraiter.
- Une base qui a changé de groupe de ressources pendant la période est coupée en deux : interroger tous les groupes.
- Sans critère de départage, les classements se réordonnent à chaque rafraîchissement : toujours trier sur une seconde clé.