Triaging Slow Redshift Queries: SYS Views, Query Priority and Concurrency Scaling

"The warehouse is slow" is never a ticket you can act on. Before you resize a cluster or add RPUs, you need to know which queries are slow, why they are slow, and whether the problem is the query, the data layout, or queueing behind other work. This tutorial is the triage workflow our consultants run on a new Amazon Redshift engagement, using the modern SYS_* monitoring views rather than the older STL_/SVL_ tables.

Everything here works on RA3 provisioned clusters and on Redshift Serverless. Where the two differ, we call it out.

Step 0: use the SYS views, not the legacy tables

The SYS_* monitoring views are the supported surface on both provisioned and Serverless, they are already stitched across the rewrite steps of a single user query, and they retain roughly a day of history. The ones you will actually use:

View	What it answers
SYS_QUERY_HISTORY	One row per user query: elapsed time, queue time, execution time, status, user, text
SYS_QUERY_DETAIL	Per-step detail: rows, bytes, spill, step type
SYS_QUERY_TEXT	Full SQL text in chunks for long statements
SYS_LOAD_HISTORY	COPY / auto-copy loads and their errors
SYS_SERVERLESS_USAGE	RPU-seconds and charged compute per interval (Serverless)
SYS_CONNECTION_LOG	Session churn and auth problems

Step 1: find the expensive work

Start with total time consumed, not the single worst query. A 400 ms query run 900,000 times a day is a bigger problem than a 90-second nightly rebuild.

SELECT
    LEFT(REGEXP_REPLACE(query_text, '\\s+', ' '), 90) AS sql_sample,
    COUNT(*)                                          AS runs,
    ROUND(SUM(elapsed_time) / 1000000.0, 1)           AS total_sec,
    ROUND(AVG(elapsed_time) / 1000000.0, 2)           AS avg_sec,
    ROUND(MAX(elapsed_time) / 1000000.0, 2)           AS max_sec,
    ROUND(AVG(queue_time)   / 1000000.0, 2)           AS avg_queue_sec
FROM SYS_QUERY_HISTORY
WHERE start_time > DATEADD(hour, -24, GETDATE())
  AND status = 'success'
  AND query_type = 'SELECT'
GROUP BY 1
ORDER BY total_sec DESC
LIMIT 25;

Two columns decide where you go next:

• High avg_queue_sec, low execution time → a concurrency problem. Go to step 4.
• Low queue time, high execution time → a query or data-layout problem. Go to steps 2 and 3.

Also look for what never finished:

SELECT user_id, status, COUNT(*)
FROM SYS_QUERY_HISTORY
WHERE start_time > DATEADD(hour, -24, GETDATE())
  AND status IN ('failed', 'canceled')
GROUP BY 1, 2
ORDER BY 3 DESC;

A pile of canceled queries usually means a statement timeout or a query monitoring rule is already firing, and someone is retrying by hand.

Step 2: read the step detail before you read the plan

Take one query_id from step 1 and look at where the time and bytes went:

SELECT step_name,
       SUM(input_rows)  AS in_rows,
       SUM(output_rows) AS out_rows,
       SUM(input_bytes) AS in_bytes,
       MAX(is_disk_based) AS spilled,
       ROUND(SUM(duration) / 1000000.0, 2) AS sec
FROM SYS_QUERY_DETAIL
WHERE query_id = 123456789
GROUP BY step_name
ORDER BY sec DESC;

The three patterns that account for most slow analytic queries:

1. Scan reads far more rows than the result needs. The predicate is not being zone-mapped away. Check the sort key and whether the filter is on a function-wrapped or implicitly cast column (WHERE DATE(event_ts) = ... defeats the zone map; WHERE event_ts >= ... AND event_ts < ... does not).
2. A broadcast or redistribution step (DS_BCAST_INNER, DS_DIST_BOTH) moving hundreds of millions of rows. Either the join keys disagree with the distribution style, or the optimizer has stale statistics.
3. is_disk_based = true on a hash or aggregate step. The query spilled to disk. Usually an over-wide SELECT *, a missing predicate, or genuinely under-sized compute.

Only then run EXPLAIN. The plan tells you what the optimizer intends; SYS_QUERY_DETAIL tells you what actually happened, and the gap between the two is nearly always stale statistics.

SELECT "table", stats_off, tbl_rows, unsorted, vacuum_sort_benefit
FROM SVV_TABLE_INFO
WHERE schema = 'analytics'
ORDER BY stats_off DESC
LIMIT 20;

stats_off above about 10 means ANALYZE the table before you tune anything else. A high unsorted percentage with a meaningful vacuum_sort_benefit means auto-vacuum has not caught up with your load pattern — common right after a large backfill.

Step 3: fix the query or the layout

In rough order of return on effort:

• Run ANALYZE on tables flagged by stats_off, and let auto-analyze handle the rest.
• Let AUTO sort and distribution keys work, but override them when you know the access pattern — a fact table joined on one key every time should be DISTKEY on that column. We covered this in depth in our sort and distribution keys tutorial.
• Replace repeated aggregation with a materialized view. Incremental refresh plus automatic query rewriting means existing dashboards benefit without changing their SQL:

CREATE MATERIALIZED VIEW analytics.mv_daily_revenue
  AUTO REFRESH YES
AS
SELECT order_date, region_id, SUM(net_amount) AS revenue, COUNT(*) AS orders
FROM analytics.fct_orders
GROUP BY 1, 2;

Check what the optimizer is doing with it in SVV_MV_INFO (is_stale) and SYS_MV_REFRESH_HISTORY.

• Trim the projection. SELECT * on a 200-column table in a columnar warehouse is the single most common self-inflicted wound we see.

Step 4: when the problem is queueing, not the query

If avg_queue_sec dominates, tuning SQL will not help. Look at the concurrency picture:

SELECT DATE_TRUNC('hour', start_time) AS hr,
       COUNT(*) AS queries,
       ROUND(AVG(queue_time) / 1000000.0, 2) AS avg_queue_sec,
       SUM(CASE WHEN concurrency_scaling_status = 1 THEN 1 ELSE 0 END) AS ran_on_cs
FROM SYS_QUERY_HISTORY
WHERE start_time > DATEADD(day, -7, GETDATE())
GROUP BY 1
ORDER BY 1;

Use auto WLM. Manual WLM with hand-carved memory percentages is a maintenance burden and almost always worse than auto WLM plus query priorities. Create a small number of query groups and assign priorities:

CREATE ROLE etl_role;
CREATE ROLE bi_role;

-- In the WLM configuration (console, CLI or parameter group):
--   Queue "etl" : user role etl_role, priority HIGHEST
--   Queue "bi"  : user role bi_role,  priority NORMAL
--   Queue "adhoc": everything else,   priority LOW

Priority is relative, not a reservation: a LOW query still runs when the system is idle, it just yields when a HIGHEST query needs slots.

Add query monitoring rules (QMRs) so a runaway ad-hoc query cannot hold the warehouse hostage. Practical starting rules for the ad-hoc queue:

Predicate	Action
query_execution_time > 1800	abort
nested_loop_join_row_count > 100000000	abort
query_temp_blocks_to_disk > 100000	log, then change_query_priority to lowest
return_row_count > 5000000	log

Start every rule in log mode for a week, read the hits in SYS_QUERY_HISTORY, and only then promote it to abort. Rules that abort on day one create incident tickets, not performance wins.

Turn on concurrency scaling for the bursty read queue. On provisioned clusters you accrue one free hour of concurrency scaling credit per active day, which for many BI workloads covers the entire spike. Set max_concurrency_scaling_clusters deliberately rather than leaving it at the default, and watch the spend in SYS_QUERY_HISTORY.concurrency_scaling_status.

On Serverless the lever is different. There is no WLM queue configuration; you control base RPUs, a max RPU ceiling and the AI-driven price-performance target. Triage there means:

SELECT DATE_TRUNC('hour', start_time) AS hr,
       SUM(charged_seconds) AS charged_sec,
       SUM(compute_seconds) AS compute_sec
FROM SYS_SERVERLESS_USAGE
WHERE start_time > DATEADD(day, -7, GETDATE())
GROUP BY 1 ORDER BY 1;

If queue time is high and RPU usage is pinned at the ceiling, raise max RPUs. If queue time is high while RPUs sit low, the bottleneck is a serialized workload — a single long-running transaction, a lock, or one giant query — and more RPUs will change nothing. Separate ETL and BI onto different workgroups against the same namespace when they genuinely need isolation; that is the Serverless equivalent of WLM queues, and it is also how you get clean per-team cost attribution.

Step 5: make the triage repeatable

SYS views retain about a day. If you want trend lines, persist the summary:

CREATE TABLE ops.query_daily_rollup AS
SELECT CURRENT_DATE          AS snapshot_date,
       user_id,
       query_type,
       COUNT(*)              AS runs,
       SUM(elapsed_time)     AS total_us,
       SUM(queue_time)       AS queue_us,
       SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failures
FROM SYS_QUERY_HISTORY
WHERE start_time > DATEADD(day, -1, GETDATE())
GROUP BY 1, 2, 3;

Schedule the insert daily with a Redshift scheduled query or your orchestrator, and put three charts on a dashboard: total execution seconds, total queue seconds, and failures. Regressions show up as a slope change days before anyone files a ticket.

A working checklist

1. Rank by total time consumed, not worst single run.
2. Split queue time from execution time — they have different fixes.
3. Check stats_off and unsorted before touching SQL.
4. Look for spill, broadcast and over-wide scans in SYS_QUERY_DETAIL.
5. Materialize the aggregations that everyone recomputes.
6. Auto WLM plus priorities; QMRs in log mode first.
7. Concurrency scaling for bursty reads; max RPUs only when RPUs are actually pinned.
8. Persist a daily rollup so you can prove the improvement.

Need a second pair of eyes?

Most "we need a bigger cluster" conversations end with a handful of statistics fixes, one materialized view and a sane priority scheme. Our senior Redshift consultants run this triage as a fixed-scope performance review and hand back a prioritised remediation plan you can execute yourself or with us. Get in touch to scope one, or read more about our Redshift Performance Optimization practice.