Diagnostiquer une requête BigQuery
Une requête BigQuery lente se diagnostique dans le même ordre qu'un pipeline : quelle étape retient la requête, pourquoi elle est lente (volume lu, déséquilibre, jointure qui démultiplie les lignes, brassage), puis ce qui l'empêche d'aller plus vite (slots disponibles, concurrence, quotas). Le graphe d'exécution répond aux deux premières questions, les vues INFORMATION_SCHEMA et les pages de surveillance à la troisième. La conception des tables relève d'Organiser BigQuery et de Modéliser un entrepôt, le choix entre la demande et les éditions d'Optimiser les coûts.
Lire le graphe d'exécution
BigQuery traduit le SQL en graphe d'exécution : des étapes (stages) qui s'exécutent en parallèle sur des slots, chacune composée d'opérations (steps). Les étapes échangent leurs résultats intermédiaires par le brassage (shuffle), une mémoire distribuée. Le plan peut changer en cours d'exécution : BigQuery ajoute des étapes REPARTITION pour rééquilibrer les données entre workers, COALESCE pour regrouper de petits morceaux.
Dans la console, un job ouvert depuis l'historique (Job history, puis View job in editor) montre le plan dans l'onglet Execution graph, pour une requête terminée ou en cours (bouton Sync). Le graphe colore les étapes selon leur temps de slot, du plus clair au rouge foncé ; les nombres sur les arêtes estiment les lignes échangées. Le panneau de gauche surligne le texte SQL selon le temps de slot des étapes qui lui correspondent. Trois options facilitent la lecture : Highlight top stages by duration, Highlight top stages by processing, et Show shuffle redistribution stages, qui affiche les étapes REPARTITION et COALESCE, masquées par défaut. Un dry run ou un résultat servi par le cache n'a pas de plan.
| Mesure d'une étape (champ de l'API) | Ce qu'elle dit | Lecture |
|---|---|---|
Attente (waitMsAvg, waitMsMax) |
Temps passé à attendre d'être ordonnancé | Élevée partout : pas assez de slots. Élevée sur une seule étape : regarder l'étape précédente |
Lecture (readMsAvg, readMsMax) |
Temps passé à lire les données d'entrée | Élevée sur une étape d'entrée : trop de données lues, élagage absent |
Calcul (computeMsAvg, computeMsMax) |
Temps passé à calculer | Maximum très supérieur à la moyenne : déséquilibre, quelques tranches de données portent l'essentiel du travail |
Écriture (writeMsAvg, writeMsMax) |
Temps passé à écrire vers le brassage ou le résultat | Élevée : l'étape produit beaucoup de données intermédiaires |
Lignes lues et écrites (recordsRead, recordsWritten) |
Volume d'entrée et de sortie de l'étape | Beaucoup plus de lignes en sortie qu'en entrée sur une jointure : jointure qui démultiplie |
Octets écrits et déversés (shuffleOutputBytes, shuffleOutputBytesSpilled) |
Volume envoyé au brassage, part déversée sur disque | Un déversement signale un brassage qui dépasse sa capacité en mémoire ; la requête ralentit fortement |
La console présente ces temps en valeurs relatives, rapportées au plus long temps passé par un worker dans l'une des catégories ; l'API (jobs.get) donne aussi les valeurs absolues. Pour une requête longue, les statistiques se mettent à jour pendant l'exécution, au plus souvent toutes les 30 secondes environ.
Les opérations d'une étape se lisent par catégorie : READ, FILTER, COMPUTE, AGGREGATE, JOIN, SORT, ANALYTIC_FUNCTION, USER_DEFINED_FUNCTION, LIMIT, WRITE. Dans une jointure, JOIN EACH WITH ALL désigne une jointure par diffusion : la petite table est copiée sur chaque slot. JOIN EACH WITH EACH désigne une jointure par hachage : les deux tables sont brassées pour réunir les mêmes clés sur le même slot, ce qui coûte plus cher.
La couleur du texte SQL reflète le temps de slot de toute l'étape, pas celui de l'opération surlignée : BigQuery ne mesure pas le temps par opération. Quand une étape combine une jointure et une agrégation, lire ses opérations avant d'accuser l'une ou l'autre.
Insights de performance
Sur le graphe, une étape marquée d'une icône d'information porte un insight de performance, une suggestion calculée par BigQuery au mieux de ses moyens. Les mêmes insights se lisent dans l'onglet Job information quand la requête a régressé, et dans le champ query_info.performance_insights des vues INFORMATION_SCHEMA.JOBS*.
| Insight (console, champ) | Signification | Remède |
|---|---|---|
Contention de slots (slot contention, slot_contention) |
Des tâches sont prêtes, mais BigQuery ne trouve pas de slots libres pour les exécuter | Réduire les données traitées ; côté administration, agrandir la réservation (base ou maximum), créer une réservation séparée, étaler les requêtes lourdes ; passer de la demande à une réservation |
Quota de brassage insuffisant (insufficient shuffle quota, insufficient_shuffle_quota) |
Plus de données à écrire au brassage que la capacité disponible, qui dépend des slots | Réduire les données avant les JOIN et GROUP BY ; ajouter des slots ; isoler les requêtes gourmandes en brassage (period_shuffle_ram_usage_ratio dans JOBS_TIMELINE) |
Jointure à forte cardinalité (high cardinality join, high_cardinality_joins) |
Clés non uniques des deux côtés : la jointure produit beaucoup plus de lignes qu'elle n'en reçoit | Vérifier la condition de jointure ; pré-agréger par GROUP BY ou remplacer par une fonction de fenêtrage |
Déséquilibre de partition (partition skew, partition_skew) |
Une valeur de clé de jointure ou de regroupement très fréquente : un slot traite seul une partition surdimensionnée | Filtrer tôt ; écarter les NULL et chaînes vides ; traiter la clé dominante à part puis UNION ALL ; fonctions approchées (APPROX_COUNT_DISTINCT) |
Changement d'échelle des données d'entrée (data input scale change, input_data_change) |
La requête lit au moins 50 % de données de plus qu'à l'exécution précédente sur une même table | Historique des modifications de la table ; réduire le volume lu |
BI Engine (bi_engine_reasons) |
Raison d'une accélération partielle ou absente | Voir la ligne BI Engine du tableau des symptômes |
Retrouver les requêtes des 30 derniers jours qui portent un insight :
SELECT job_id, user_email, query_info.performance_insights
FROM `region-REGION`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE DATE(creation_time) >= CURRENT_DATE() - 30
AND job_type = 'QUERY' AND state = 'DONE' AND error_result IS NULL
AND statement_type != 'SCRIPT'
AND EXISTS (
SELECT 1
FROM UNNEST(query_info.performance_insights.stage_performance_standalone_insights)
WHERE slot_contention OR insufficient_shuffle_quota
OR high_cardinality_joins IS NOT NULL OR partition_skew IS NOT NULL
);
Surveiller les slots et les jobs
| Outil | Ce qu'on y lit | Question qu'il tranche |
|---|---|---|
| Monitoring, onglet Operational health (console BigQuery) | Slots, brassage, concurrence, erreurs, durée des jobs, octets traités, stockage, par région et par réservation ; repères P95 et P99 du même jour de la semaine précédente ; requêtes actives les plus lourdes ; tableau des insights et des erreurs de quota | L'organisation est-elle dans un état normal ? Quelle réservation déborde ? |
| Monitoring, onglet Resource utilization | Historique jusqu'à 30 jours : usage des slots par réservation (base, emprunts de slots inactifs, autoscaling), concurrence, octets, brassage, groupés et filtrés à la demande | Quand la réservation sature-t-elle, et qui la consomme ? |
| Jobs explorer (menu de la console BigQuery) | Jobs du projet ou de l'organisation filtrés par état, durée, propriétaire, slots, réservation, empreinte de requête ; détail d'un job et comparaison de deux jobs préversion | Quelle requête est en cause ? A-t-elle ralenti par rapport à ses exécutions passées ? |
INFORMATION_SCHEMA.JOBS |
Un enregistrement par job, 180 jours : total_slot_ms, total_bytes_processed, cache_hit, reservation_id, job_stages, query_info.performance_insights, query_info.resource_warning, bi_engine_statistics |
Quels jobs coûtent le plus, lesquels portent un insight ou un avertissement ? |
INFORMATION_SCHEMA.JOBS_TIMELINE |
Une ligne par seconde d'exécution de chaque job : period_slot_ms, state (PENDING, RUNNING), period_estimated_runnable_units, period_shuffle_ram_usage_ratio |
La requête manquait-elle de slots ? Combien de jobs attendaient ? |
INFORMATION_SCHEMA.RESERVATIONS_TIMELINE |
État des réservations minute par minute, détail de l'autoscaling seconde par seconde (per_second_details) |
Combien de slots l'autoscaling a-t-il ajoutés, et quand ? |
| Cloud Monitoring | Métriques bigquery.googleapis.com/slots/allocated_for_reservation, slots/assigned, slots/capacity_committed, query/execution_times, job/num_in_flight |
Alerter sur une réservation pleine ou une durée qui dérive |
Les pages Monitoring interrogent elles-mêmes JOBS, JOBS_TIMELINE et les vues de réservation. L'onglet Operational health demande des réservations et se consulte depuis le projet d'administration ; ses données ont jusqu'à une heure de retard, cinq minutes avec l'option Live data. Voir tous les projets d'une organisation demande roles/bigquery.resourceViewer sur l'organisation.
Une requête a manqué de slots quand ses unités de travail prêtes (period_estimated_runnable_units) restent positives pendant qu'elle s'exécute :
-- Part du temps d'exécution où des slots supplémentaires auraient accéléré le job
SELECT ROUND(COUNTIF(period_estimated_runnable_units > 0) / COUNT(*) * 100, 1) AS pct_manque_de_slots
FROM `region-REGION`.INFORMATION_SCHEMA.JOBS_TIMELINE
WHERE job_id = 'JOB_ID'
AND job_creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY);
-- Jobs en attente et en cours, seconde par seconde, sur une réservation
SELECT period_start,
COUNTIF(state = 'PENDING') AS en_attente,
COUNTIF(state = 'RUNNING') AS en_cours,
SUM(period_slot_ms) / 1000 AS slots
FROM `region-REGION`.INFORMATION_SCHEMA.JOBS_TIMELINE
WHERE job_creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
AND reservation_id = 'ADMIN_PROJECT_ID:REGION.RESERVATION'
AND (statement_type != 'SCRIPT' OR statement_type IS NULL)
GROUP BY period_start
ORDER BY period_start;
Un pourcentage faible écarte la piste des slots. Un pourcentage élevé avec une réservation pleine désigne la capacité : réduire la concurrence, agrandir la réservation, autoriser l'emprunt de slots inactifs, ou quitter la demande. Le nombre moyen de slots d'un job vaut total_slot_ms divisé par sa durée d'exécution ; un job qui fait le même travail avec moins de slots par seconde a manqué de capacité, ou n'en a pas demandé davantage à cause d'un déséquilibre.
Symptômes, causes, remèdes
| Symptôme | Causes à vérifier | Remède |
|---|---|---|
| Attente élevée sur plusieurs étapes, insight de contention | Réservation pleine, autres jobs concurrents ; à la demande, 2 000 slots par projet partagés entre les requêtes, et parfois moins en cas de forte demande dans la région | Étaler les requêtes lourdes, réservation dédiée, base ou maximum relevés ; voir Optimiser les coûts pour dimensionner |
Requêtes en file (PENDING), durée dominée par l'attente de démarrage |
Concurrence calculée dynamiquement par projet ou réservation ; file limitée à 1 000 requêtes interactives et 20 000 batch par projet et région | Réduire la concurrence ; en réservation Enterprise ou Enterprise Plus, fixer une cible de concurrence ; séparer les charges par réservation |
| Calcul max bien supérieur au calcul moyen, insight de déséquilibre | Clé de jointure ou de regroupement dominante, masse de NULL ou de chaînes vides |
Filtrer tôt, écarter les valeurs vides, isoler la clé dominante ; ajouter des slots n'y change rien |
Beaucoup plus de lignes en sortie qu'en entrée sur une étape JOIN |
Clés dupliquées des deux côtés, jointure croisée, auto-jointure | Condition corrigée, pré-agrégation, fonction de fenêtrage plutôt qu'auto-jointure |
| Lecture élevée, octets traités plus élevés que prévu | Filtre qui n'élague pas (valeur non constante, fonction sur la colonne de partition), SELECT *, table non partitionnée, vue modifiée |
Élagage et clustering (Organiser BigQuery), colonnes nommées, require_partition_filter ; le dry run montre le volume |
| Lente avant même de lire, sur une table fragmentée | Tables datées (events_20260101…), joker trop large, milliers de petites partitions (moins de 10 Go chacune), des millions de petits fichiers sous une table externe |
Une table partitionnée au lieu des tables datées ; partition mensuelle et clustering ; fichiers regroupés, cache de métadonnées sur les tables externes et BigLake |
Octets déversés sur disque, insight de brassage, erreur Your project or organization exceeded the maximum disk and memory limit available for shuffle operations |
Jointures et agrégations sur de gros volumes, capacité de brassage partagée avec d'autres jobs | Réduire les données avant JOIN et GROUP BY, plus de slots ; à la demande, passer en capacité |
Étape USER_DEFINED_FUNCTION longue |
Fonction JavaScript : un sous-processus par appel ; fonction distante : latence du service Cloud Run appelé, dix requêtes simultanées avec fonction distante par projet | Fonction SQL quand c'est possible ; pour une fonction distante, filtrer avant l'appel, régler max_batching_rows, laisser le service monter en charge |
DML en file ou en échec : Could not serialize access to table due to concurrent update, Too many DML statements outstanding against <table-name>, limit is 20 |
UPDATE, DELETE, MERGE simultanés sur une même partition ; mises à jour ligne à ligne ; transaction non terminée ; expiration de partition concurrente |
Un MERGE groupé au lieu de nombreux petits DML ; partitionner pour que les DML touchent des partitions distinctes ; limiter la concurrence dans l'orchestrateur |
Échec Resources exceeded during query execution |
ORDER BY avec grand OFFSET trié sur une seule unité, clauses WITH ou vues imbriquées réévaluées, requête trop complexe |
ROW_NUMBER() puis filtre, tables temporaires plutôt que WITH répétés, requête découpée ; query_info.resource_warning prévient avant l'échec |
BI Engine ne sert pas (bi_engine_statistics.acceleration_mode, raison BiEngineReason) |
Pas de réservation BI Engine dans le projet ou mémoire insuffisante ; table joker, table externe, sécurité au niveau des lignes, fonction non SQL ; plus de quatre tables de dimension jointes, ou des dimensions trop grosses (5 millions de lignes, 5 Gio) ; réservation en édition Standard, qui n'a pas BI Engine | Réserver la mémoire, désigner des tables préférées, aplatir les jointures dans une vue matérialisée |
LIMITne réduit pas le volume lu : unSELECT * ... LIMIT 10sur une table non clusterisée lit et facture toute la table. Seuls les colonnes nommées, l'élagage de partitions et le clustering réduisent la lecture.
Comparer une exécution lente à une exécution rapide
Quand une requête qui passait vite ralentit, comparer les deux jobs (Jobs explorer, ou jobs.get sur chacun) avant de toucher au SQL :
- Cache :
cacheHitvrai sur l'exécution rapide signifie qu'elle n'a rien calculé. Comparer avec une exécution rapide hors cache. - Attente de quota :
quotaDefermentsindique un démarrage retardé par un quota. - Relances internes :
finalExecutionDurationMsdonne la durée de la dernière tentative ; un écart important avec la durée totale révèle une relance. - Volume :
totalBytesProcessedetreferencedTableschangent quand une table grossit, qu'une partition plus grosse est lue ou qu'une vue a été redéfinie. - Vues matérialisées et BI Engine : une vue matérialisée choisie la première fois peut être rejetée la seconde (
MaterializedViewStatistics, champrejectedReason) ; l'accélération BI Engine peut disparaître (BiEngineStatistics). - Réservation : un projet passé de la demande à une réservation, ou d'une réservation à une autre, ne dispose plus des mêmes slots.
- Étapes : comparer ensuite l'attente et les octets écrits au brassage, étape par étape.
Pièges
- Accuser la requête quand c'est la file. Une requête qui attend son tour dans la file ou manque de slots paraît lente alors que son plan est sain. Regarder d'abord l'attente et
period_estimated_runnable_units. - Ajouter des slots à une requête déséquilibrée. Une partition se traite sur un seul slot : le temps maximal de l'étape ne baisse pas.
- Confondre slots alloués et slots utilisés. La page Quotas & System Limits compte la base des réservations et les engagements, même inactifs, sans l'autoscaling ; les graphiques Monitoring comptent les slots qui travaillent, autoscaling compris.
- Alerter sur les métriques de requêtes échouées. Les métriques de requêtes Cloud Monitoring n'existent que pour les requêtes réussies, jusqu'à sept minutes après : suivre les échecs par
INFORMATION_SCHEMA.JOBSou les journaux d'audit. - Compter deux fois les scripts. Un job
SCRIPTadditionne les slots de ses requêtes filles : l'exclure (statement_type != 'SCRIPT') dans toute somme. - Compter sur
WITHpour matérialiser. Une expression de table commune peut être évaluée à chaque référence ; une table temporaire ou une variable garantit un calcul unique.
Alternatives en logiciel libre
- Trino (Apache 2.0) : interface web par requête (étapes, tâches, opérateurs) et
EXPLAIN ANALYZE, qui donne le temps CPU, les lignes et la répartition des données par opérateur ; l'équivalent le plus direct du graphe d'exécution. - Apache Spark (Apache 2.0) : l'onglet SQL de l'interface Spark affiche le plan physique avec ses métriques ; l'exécution adaptative redécoupe les partitions déséquilibrées d'une jointure, comme les étapes
REPARTITIONde BigQuery. - ClickHouse (Apache 2.0) : table
system.query_log(durée, lignes et octets lus, mémoire par requête),EXPLAINet profileur par échantillonnage danssystem.trace_log; le pendant deINFORMATION_SCHEMA.JOBS. - StarRocks (Apache 2.0) : profil de requête par opérateur (temps, lignes, mémoire, brassage) consultable après exécution, pour un entrepôt MPP auto-hébergé.
- DuckDB (MIT) :
EXPLAIN ANALYZEet sortie de profilage par opérateur avec cardinalités, pour analyser en local une requête avant de la porter sur un entrepôt.