BigQuery Top Queries by Complexity

Why This Matters

High-slot queries affect everyone in your project. Under on-demand pricing they compete for your fair-share slot allocation (typically 2,000 slots). Under Editions pricing they directly consume your reserved capacity. Identifying and optimizing complex queries improves performance for all users and reduces costs under both billing models.

How It Works

Approximate slot count is calculated by dividing total_slot_ms by execution time in milliseconds. A query using 1,000 slot-milliseconds over 1 second used approximately 1 slot on average. This metric captures the parallelism and computational weight of each query.

SQL Query

-- Queries ranked by average slot consumption (most compute-heavy first)

DECLARE lookback_days INT64 DEFAULT 14;

WITH jobs AS (
  SELECT
    user_email,
    query,
    project_id,
    start_time,
    end_time,
    total_slot_ms,
    COALESCE(total_bytes_billed, 0) AS bytes_billed,
    TIMESTAMP_DIFF(end_time, start_time, MILLISECOND) AS duration_ms,
    ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY end_time DESC) AS rn
  FROM `your-project`.`region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
  WHERE creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL lookback_days DAY)
    AND job_type = 'QUERY' AND state = 'DONE' AND total_slot_ms IS NOT NULL
)
SELECT
  user_email,
  query,
  project_id,
  start_time,
  end_time,
  ROUND(SAFE_DIVIDE(total_slot_ms, duration_ms), 0) AS avg_slots,
  ROUND(duration_ms / 1000, 1) AS duration_sec,
  ROUND(bytes_billed / POW(1024, 4) * 6.25, 2) AS on_demand_cost_usd
FROM jobs
WHERE rn = 1
...

Query Explanation

The core formula is SAFE_DIVIDE(total_slot_ms, duration_ms). If a query used 10,000 slot-milliseconds over 100 milliseconds, its approximate slot count is 100 — meaning it used roughly 100 slots concurrently. Higher numbers indicate more parallelism and computational demand.

Key Insights

• lightbulb

  Queries with >500 slots are considered highly complex and may benefit from query rewriting.

• lightbulb

  High slot count with low bytes billed suggests compute-heavy operations: complex JOINs, window functions, or DISTINCT on large datasets.

• lightbulb

  High slot count with high bytes billed indicates both data volume and compute issues — the biggest optimization targets.

• lightbulb

  Compare slot count to execution time: if slots are high but execution is fast, the query is efficiently parallel but resource-hungry.

Best Practices

1. 1

   Simplify multi-way JOINs by pre-aggregating or using intermediate tables.

2. 2

   Replace exact COUNT(DISTINCT ...) with APPROX_COUNT_DISTINCT() when precision is not critical.

3. 3

   Avoid correlated subqueries — rewrite them as JOINs or window functions.

4. 4

   Use clustering on JOIN keys to reduce shuffle operations.

Related Guides