How to Monitor Memory Usage in Amazon Redshift

How do I check memory usage for queries in Amazon Redshift?

Track how much WLM slot and cluster memory each query or session consumes in Amazon Redshift.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why should I monitor Redshift memory usage?

Memory pressure is the top cause of query slow-downs and out-of-memory (OOM) errors. Watching memory tells you when to tune queries, resize the cluster, or adjust WLM queues.

Which system tables expose memory metrics?

Use STL_WLM_QUERY for per-query peaks, SVL_QUERY_METRICS for slot-level usage, and STV_BLOCKLIST to catch live OOM waits.

How do I pull current memory by session?

Join STV_RECENTS to SVL_QUERY_METRICS filtering on pid or query.The sample query below shows the pattern.

What is the exact syntax to query memory tables?

See the Syntax section for the canonical SELECT with optional filters for user, queue, and time window.

Best practices for interpreting memory data?

Compare max_block_usage to slot_count; values >99% flag risk.
Sort by service_class to find noisy queues.
Correlate memory peaks with concurrency scaling usage.

How do I reduce high memory usage?

Add DISTKEYs to large tables, push predicates into subqueries, and switch to late-binding views so unused columns never materialize.

Can I alert when memory crosses a threshold?

Create a scheduled query in CloudWatch, SNS, or Galaxy’s upcoming workflow runner that checks max_block_usage > 95 and sends a notification.

Why How to Monitor Memory Usage in Amazon Redshift is important

How to Monitor Memory Usage in Amazon Redshift Example Usage


-- Find ecommerce queries reading the Products table that used \u226590% memory
SELECT q.query, q.max_block_usage, s.text
FROM   SVL_QUERY_METRICS_SUMMARY q
JOIN   STL_QUERYTEXT s USING (query)
WHERE  q.max_block_usage >= 90
  AND  s.text ILIKE '%FROM products%'
ORDER  BY q.max_block_usage DESC
LIMIT 10;

How to Monitor Memory Usage in Amazon Redshift Syntax


-- Track memory usage for recent queries in the last 15 minutes
SELECT q.query,
       q.service_class AS wlm_queue,
       q.slot_count,
       m.max_block_usage AS pct_memory,
       q.user_name,
       q.start_time,
       q.end_time
FROM   STL_WLM_QUERY q
JOIN   SVL_QUERY_METRICS_SUMMARY m USING (query)
WHERE  q.start_time >= dateadd(minute,-15, current_timestamp)
  AND  m.max_block_usage IS NOT NULL
ORDER  BY pct_memory DESC;

-- Optional filters
--   AND q.user_name = 'analytics_user'
--   AND q.service_class = 6

Common Mistakes

Relying only on CloudWatch. CloudWatch shows cluster-level memory but hides per-query spikes. Query STL_WLM_QUERY for granular insight.
Filtering by runtime instead of start_time. <code>end_time</code> is NULL while a query runs, so use <code>start_time</code> for live monitoring.