Find the query behind the metric: troubleshooting Amazon Redshift with the AWS DevOps Agent and an MCP server

Content level: Intermediate | ~7 minute read

Overview

Most Amazon Redshift performance investigations begin with a familiar picture: an alarm fires, you open Amazon CloudWatch, and you can see the cluster is under pressure — CPUUtilization and CommitQueueLength are both elevated. That confirms the symptom, but it stops short of the questions that actually resolve the incident: which queries caused it, why they are expensive, and how to fix them. Answering those has traditionally required a Redshift specialist — someone who knows which system tables (STL_*, STV_*, SVV_*, SYS_*) to open, how to write the diagnostic SQL, and how to interpret the results. If you don't have that expertise on hand at 2 a.m., the investigation stalls.

That expertise gate is exactly what this pattern removes. You describe the problem in plain language and the agent does the specialist's work for you — it decides which system tables to read, runs the queries, and explains the findings in words. You do not need to know Redshift SQL to get to root cause; and if you are a specialist, the same setup shows its work so you can verify or extend it.

The AWS DevOps Agent already investigates incidents by correlating CloudWatch, CloudTrail, AWS Health, and discovered topology, and moves quickly from an alarm to a well-formed diagnosis. Those native signals describe the cluster from the outside; the evidence for a query-level root cause lives inside the database. You can extend the agent's reach to that layer by connecting it to the Amazon Redshift MCP Server — an AWS Labs Model Context Protocol server that lets the agent run read-only queries against the cluster's own system tables. (Under Auto WLM, per-queue detail isn't emitted as a CloudWatch metric, and CloudTrail records that statements ran without their SQL text — so the in-database view is what closes the loop.)

This article is a practical guide to setting that up and using it: how to wire the server to your Agent Space, what to ask and how to read the agent's answers, a worked commit-pressure example with the fixes, the security model, and the setup gotchas to expect. The exact SQL the agent runs is included too — optional, for readers who want to verify a finding or run it by hand. If you'd rather see it end to end first, there's a hands-on workshop linked at the bottom.

Here's the gap and the solution at a glance — native signals take the agent to the symptom; the Redshift MCP server extends it to the root cause and the fix:

Setup (once)

You wire the MCP server to your Agent Space once; after that it's available to every investigation. Four steps:

1. Host the MCP server. The awslabs.redshift-mcp-server runs SQL through the Amazon Redshift Data API (no persistent connection). A common production pattern is an AWS Lambda function behind a function URL with IAM (SigV4) auth.
2. Scope its execution role to read-only: redshift:DescribeClusters, redshift:GetClusterCredentialsWithIAM, and redshift-data:ExecuteStatement / DescribeStatement / GetStatementResult (plus serverless equivalents). Full policy is in the server's Permissions section.
3. Grant sys:monitor (as a superuser) to the read-only database identity the server authenticates as — the review_cluster tool reads system views such as SVV_TABLE_INFO that require it.
4. Register the server with your DevOps Agent Space; the agent connects to private or remote MCP servers and its tools then join the agent's toolkit automatically.

The server exposes seven read-only tools: list_clusters, list_databases, list_schemas, list_tables, list_columns, execute_query, and review_cluster.

The diagnostic playbook

You drive the whole investigation in plain language — no SQL required. Ask the agent one question and let it decide which system tables to read, run the queries, and explain what it found. A good opening prompt:

"Investigate CPU and commit activity on prod-cluster over the last 30 minutes and find the root cause."

Step 1 — Confirm where the pressure is. The agent checks per-node commit time and queue depth and replies in words, for example: "Commits are serializing on both nodes — the commit queue is backing up while commit work time climbs." You now know the pressure is commit-related without having written a line of SQL.

Step 2 — Find what actually ran. This is the step CloudWatch can't do: the agent reads the executed statements ranked by cost and names them — "Three statements dominate the window: a 38.7-second self-join, a 4.5-second self-join, and a swarm of small ETL inserts."

Step 3 — Ask for the fix. A follow-up like "What are the expensive queries and how should I rewrite them?" returns the offending SQL, why each is costly, and revised versions — explained in plain terms, with the rewrites ready to hand to whoever owns the query.

That entire flow happens as a conversation. The agent is doing the specialist's work — choosing the right STL_*/SVV_* tables, running the SQL, and translating the output — so a first responder who has never written Redshift SQL can still reach a named root cause and a concrete fix.

Under the hood: the SQL the agent runs

You never have to write these — the agent does — but they're useful if you want to verify a finding or reproduce it in the query editor.

Per-node commit time and queue depth — a high queuelen with rising work_time_ms confirms commits are serializing (STL_COMMIT_STATS):

SELECT node, datediff(ms, startwork, endtime) AS work_time_ms, queuelen
FROM stl_commit_stats
WHERE startwork > DATEADD(minute, -30, GETDATE())
ORDER BY work_time_ms DESC
LIMIT 20;

What ran in the window, ranked by cost — the actual SQL text the metric layer can't show you (STL_QUERY):

SELECT query, TRIM(querytxt) AS sql_text,
       DATEDIFF(second, starttime, endtime) AS elapsed_s
FROM stl_query
WHERE starttime > DATEADD(minute, -30, GETDATE())
ORDER BY elapsed_s DESC
LIMIT 20;

Disk vs. CPU/concurrency — if CloudWatch ReadThroughput / ReadIOPS / ReadLatency are near zero while scans are high, the bottleneck is CPU and concurrency (data served from cache), not disk — so remediation belongs in query design, not storage. The agent makes this same cross-check automatically.

Worked example: commit-queue pressure

On a two-node ra3.large cluster running ETL TRUNCATE/INSERT cycles plus analyst self-joins, the agent identified three contributors and quantified each:

Contributor	Cost	Why
Product-peer self-join	~38.7 s	Joined only on region_id/bill_date with account_id <> account_id — an N×(N-1) fan-out
Region-peer self-join	~4.5 s	Unbounded scan + per-row arithmetic (also a latent numeric-overflow risk)
ETL "insert swarm"	~99.8% of reads	~9,800 tiny overlapping INSERT … SELECTs re-scanning the same source table

The agent didn't just name these — it showed the offending statement and a rewrite for each. The worst offender was the product-peer self-join. Here is what the agent surfaced and proposed:

Offending query — product-peer self-join (~38.7 s). Every account row is joined to every other account row sharing a region and day, so the row count explodes before any aggregation:

SELECT a.region_id, a.product_id, b.product_id AS peer_product,
       SUM(a.gross_amount + b.gross_amount) AS combined_gross
FROM   xbitbls.fct_billing_daily a
JOIN   xbitbls.fct_billing_daily b
       ON a.region_id = b.region_id
      AND a.bill_date = b.bill_date
      AND a.account_id <> b.account_id      -- N x (N-1) fan-out
GROUP  BY a.region_id, a.product_id, b.product_id;

Rewrite — pre-aggregate first, then join the small result (≈39 s → sub-second). Collapse each region/day/product to one row before joining, and bound the scan to a recent window so the sort key prunes blocks:

WITH daily AS (
    SELECT region_id, bill_date, product_id,
           SUM(gross_amount) AS gross_amount,
           COUNT(*)          AS rows_n
    FROM   xbitbls.fct_billing_daily
    WHERE  bill_date >= DATEADD(day, -8, CURRENT_DATE)   -- sort-key pruning
    GROUP  BY region_id, bill_date, product_id
)
SELECT a.region_id, a.product_id, b.product_id AS peer_product,
       SUM(a.gross_amount * b.rows_n + b.gross_amount * a.rows_n) AS combined_gross
FROM   daily a
JOIN   daily b
       ON a.region_id = b.region_id
      AND a.bill_date = b.bill_date
      AND a.product_id < b.product_id        -- each pair once, no self-pair
GROUP  BY a.region_id, a.product_id, b.product_id;

The other two contributors follow the same before/after shape:

Region-peer self-join (~4.5 s) and the ETL insert swarm

Region-peer self-join — an unbounded scan with per-row arithmetic (SQRT/MOD cross-terms) that also risks a bigint/numeric overflow. Fix: bound the scan (WHERE bill_date >= DATEADD(day, -8, CURRENT_DATE)), cast the arithmetic inputs to float8, and replace a.account_id * b.account_id with a modular identity so operands never overflow.

ETL "insert swarm" — the pattern was thousands of tiny overlapping inserts, each re-scanning the whole source table:

-- Before: ~9,800 of these, in a tight loop
INSERT INTO xbiwrk.ld_billing_stage_001_s1
SELECT * FROM xbitbls.fct_billing_daily;

-- After: one bounded bulk load per stage, explicit column list
INSERT INTO xbiwrk.ld_billing_stage_001_s1 (account_id, region_id, bill_date, product_id, gross_amount)
SELECT account_id, region_id, bill_date, product_id, gross_amount
FROM   xbitbls.fct_billing_daily
WHERE  bill_date >= DATEADD(day, -1, CURRENT_DATE);

All three fixes are standard Redshift tuning — pre-aggregate before a fan-out join, prune with the sort key, and bulk-load instead of micro-insert. The point of the example is not the SQL itself but that the agent produced it from a plain-language question, ready to hand to whoever owns the query.

One-shot health check

For a periodic review or a new cluster, the review_cluster tool runs a battery of diagnostics in a single call and returns findings plus recommendations ordered by mitigation effort, each with doc links — similar in spirit to Amazon Redshift Advisor, but via conversation. Prompt: "Run a comprehensive health review of prod-cluster." Expect findings across node configuration, table design (sort/dist keys), and WLM setup — including issues you didn't know to ask about.

Security

Control	Why it matters
Read-only mode	The server accepts a single statement and rejects writes — the agent reads evidence and proposes changes; it never mutates data or schema.
Least-privilege, read-only role	Read-only is enforced in the MCP layer, so back it with a read-only IAM + database identity — the credentials can't write even in principle (defense in depth).
IAM-based DB auth	GetClusterCredentialsWithIAM issues temporary, IAM-scoped credentials — no stored password.
sys:monitor, not superuser-for-everything	Grant the narrowest role that covers the system views review_cluster needs; don't attach it to a write-capable user.
IAM-auth function URL	Only the specific principal the DevOps Agent assumes can invoke the server, and every request is SigV4-signed.
Private cluster	Because it uses the Data API (a regional service endpoint), the cluster stays private — no public accessibility required.

Troubleshooting the setup

Symptom	Cause & fix
Agent sees metrics but no query text	Expected — CloudWatch/CloudTrail don't expose SQL. Register the MCP server and re-run; the agent then reads STL_QUERY/STL_COMMIT_STATS directly.
execute_query returns a permissions error on system views	The DB identity lacks sys:monitor. Grant it as a superuser to the read-only user.
403 Forbidden invoking the function URL	The caller isn't the authorized principal, or the request isn't SigV4-signed. Confirm the DevOps Agent's role is the function URL's authorized invoker.
review_cluster returns fewer findings than expected	Signals that find nothing produce no finding — that's normal. Don't sum affected_row_count across findings; the counts overlap and use different units.
Writes rejected	By design — the server is read-only and single-statement. Use it for diagnosis; apply changes through your normal change-management path.

Why it helps

• No Redshift SQL expertise required — the biggest barrier to database troubleshooting is knowing which system tables to read and how to query them. The agent removes that gate: describe the problem in plain language and it does the specialist's work, so a first responder, an on-call generalist, or a new team member can reach root cause without ever writing SQL.
• Root cause, not just symptoms — turns "CPU is high" into named queries, quantified cost, and concrete rewrites.
• Faster resolution — the agent correlates signals autonomously and starts immediately, compressing investigation time and reducing the wait for a scarce specialist.
• Shows its work for those who want it — specialists still get the exact SQL and system tables, so the plain-language answer is verifiable, not a black box.
• Reusable pattern — the same "native metrics + in-database evidence via MCP" approach applies to any data source you put behind an MCP server.

Want to try it end to end in a disposable environment? This public workshop builds the whole loop — workload, investigation with and without the MCP server, and review_cluster: AWS DevOps Agent: Intelligent Redshift Troubleshooting with MCP.

Related resources

• About AWS DevOps Agent
• Amazon Redshift MCP Server (AWS Labs)
• Model Context Protocol — Introduction
• Amazon Redshift Data API
• STL_COMMIT_STATS · STL_QUERY · Amazon Redshift Advisor
• AWS Lambda function URLs and IAM auth

---

Topics: Management & Governance, Analytics, Artificial Intelligence Tags: AWS DevOps Agent, Amazon Redshift, Model Context Protocol (MCP), Amazon CloudWatch, AWS Lambda, Enterprise Support