How to Check Query Memory Usage in BigQuery

Galaxy Glossary

How do I check memory usage of a BigQuery query?

Shows how to inspect memory consumption of BigQuery jobs using INFORMATION_SCHEMA views and job statistics.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why monitor BigQuery memory usage?

High memory consumption can increase slot contention, slow queries, and raise costs. Monitoring usage helps tune SQL, set proper limits, and avoid job failures.

Which system views expose memory metrics?

Use INFORMATION_SCHEMA.JOBS* views. The total_slot_ms and reservation_id columns reveal how many slot-milliseconds a job consumed, a close proxy for memory.

How to retrieve memory for recent jobs?

Query region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT filtering by creation_time and state = 'DONE'.Join to your job IDs or user names for context.

Can I see memory per stage?

Yes. Call EXPLAIN ANALYZE on your SQL. The returned JSON shard shows per-stage slotMs values to pinpoint heavy operators.

What parameters limit memory?

Set maximum_bytes_billed in the query job configuration or use #legacySQL & max_bytes_billed hint. Although it caps bytes scanned, it indirectly controls memory.

When should I use reservations?

If workloads regularly exceed memory, create a slot reservation and assign projects through reservation_id. This isolates resources and stabilizes performance.

Why How to Check Query Memory Usage in BigQuery is important

How to Check Query Memory Usage in BigQuery Example Usage


-- Identify top 5 memory-heavy jobs by current user in last 6 hours
SELECT
  job_id,
  total_slot_ms / 1000 AS slot_seconds,
  (total_slot_ms / 1000) * 0.000004 AS approx_dollar_cost
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_USER
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 6 HOUR)
  AND state = 'DONE'
ORDER BY total_slot_ms DESC
LIMIT 5;

How to Check Query Memory Usage in BigQuery Syntax


-- Recent job memory usage
SELECT
  job_id,
  user_email,
  total_slot_ms AS memory_slot_ms,
  statement_type,
  creation_time
FROM
  `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)
  AND state = 'DONE'
ORDER BY memory_slot_ms DESC;

-- EXPLAIN memory per stage for an ecommerce join
EXPLAIN ANALYZE
SELECT c.id, c.name, SUM(oi.quantity * p.price) AS lifetime_value
FROM `ecom.Customers` c
JOIN `ecom.Orders` o ON o.customer_id = c.id
JOIN `ecom.OrderItems` oi ON oi.order_id = o.id
JOIN `ecom.Products` p ON p.id = oi.product_id
GROUP BY c.id, c.name;

Common Mistakes

Ignoring <code>total_slot_ms</code> and focusing only on bytes processed. Slot-milliseconds directly affect performance; monitor both metrics for a full picture.
Using outdated JOBS view names. Prefer <code>JOBS_BY_PROJECT</code> or <code>JOBS_BY_USER</code>; older views miss reservation details and may be deprecated.