How do you prove that slow Amazon Redshift dashboards are queueing rather than executing slowly?

## Split elapsed time before you do anything else "The dashboard is slow at 9am" is not yet a diagnosis. A query's elapsed time on Amazon Redshift is `queue time + execution time`, and the two have completely disjoint remedies. Adding queues, priorities or concurrency scaling does nothing for a query that is executing slowly; rebuilding sort keys does nothing for a query that spent four minutes waiting in line. So the first measurement is always the split. ## The views that give you the split **Historical, per query.** `STL_WLM_QUERY` has one row per query that passed through WLM, with `total_queue_time` and `total_exec_time` in microseconds, `service_class` identifying the queue, `slot_count`, and timestamps including `queue_start_time`. Aggregating it by queue over the complaint window is the standard first query: ```sql SELECT service_class, count(*) AS queries, avg(total_queue_time) / 1000000.0 AS avg_queue_s, avg(total_exec_time) / 1000000.0 AS avg_exec_s, max(total_queue_time) / 1000000.0 AS max_queue_s FROM stl_wlm_query WHERE queue_start_time > dateadd(hour, -4, getdate()) GROUP BY service_class ORDER BY avg_queue_s DESC; ``` On current clusters, `SYS_QUERY_HISTORY` exposes queue time and execution time per query directly and is the view AWS now points at; it also spans queries that ran on concurrency scaling clusters, which the older STL tables on the main cluster do not fully cover. **Live.** `STV_WLM_QUERY_STATE` shows every query WLM currently knows about and whether it is running or queued, with how long it has been waiting. `STV_WLM_SERVICE_CLASS_STATE` shows per-queue counts of running and queued queries, and `STV_WLM_SERVICE_CLASS_CONFIG` shows the configuration those numbers are being measured against — useful for catching the case where the parameter group you edited was never applied. **Time series.** CloudWatch publishes `WLMQueueLength`, `WLMQueueWaitTime`, `WLMRunningQueries` and `WLMQueryDuration` per queue. These are what you overlay with the business hours to prove the backlog is a 9am phenomenon rather than a constant. ## Reading the result **Wait dominates.** The queue is admitting fewer queries than are arriving. Check first whether the traffic is even landing in the queue you think it is — a common finding is that BI users match no queue at all and are piling into the default queue behind ETL. Then the remedies in order: route the workload to its own queue, give it a higher priority under automatic WLM, enable concurrency scaling on that queue so bursts spill onto transient clusters, and enable short query acceleration so quick queries are not stuck behind long reports. If the cluster is simply undersized for the concurrency, no WLM configuration fixes that. **Execution dominates.** WLM is not your problem. Go to the query: check `SVL_QUERY_SUMMARY` for steps with `is_diskbased = 't'` (memory spill), `STL_ALERT_EVENT_LOG` for planner alerts such as missing statistics or very selective filters, the distribution style for a large redistribution or broadcast, and whether the sort key supports the filter. Run `ANALYZE` if statistics are stale. **Both are moderate but the count exploded.** Sometimes nothing is slow and the dashboard simply issues 400 queries per refresh. Count queries per user and per query group before tuning anything. ## Traps worth naming - **Times are in microseconds** in the STL views. Reporting `total_queue_time` as seconds off by six orders of magnitude is an easy way to misdiagnose. - **STL tables are retained for a limited period** and rotate; if the incident was days ago the rows may be gone. Persist a nightly snapshot if you need history. - **Queue time excludes client-side and connection time.** If the user's elapsed time is much larger than queue plus execution, look at the leader node's compile time for a first-run query, at result set transfer, or at the BI tool itself. - **Compile time on first execution** of a new query shape can be seconds and is not queue time. A dashboard deployed this morning may be paying compilation once per new statement. ## How to present it The answer an interviewer wants is a decision procedure, not a view name: measure the split, act on whichever side dominates, and confirm the fix by re-measuring the same split. Naming `STL_WLM_QUERY`, `SYS_QUERY_HISTORY` and `STV_WLM_QUERY_STATE` proves you have actually done it.