Post Syndicated from Sidhanth Muralidhar original https://aws.amazon.com/blogs/big-data/run-an-automated-operational-review-with-the-amazon-redshift-mcp-server/

It’s the end of a strong quarter, and your Amazon Redshift workloads have grown with the business. Data volumes are up, new pipelines have shipped, and more teams are querying than when you first sized the cluster. Nothing is broken, but this is exactly when a periodic operational review pays off. It confirms the cluster is still tuned for how it’s used today, and surfaces ways to optimize cost and performance as you scale.

Amazon Redshift already automates significant operational complexity. Autonomics features such as automatic table optimization, automatic workload management, and automatic vacuum handle much of the complex work, so you can focus on writing queries rather than managing infrastructure. Amazon Redshift Serverless goes further, using AI-driven scaling that adapts compute to workload demand.

Even so, some decisions still benefit from human judgment. For example, table design choices may not have accounted for common join patterns, or query patterns may have shifted since the tables were built. Both are worth revisiting. Likewise, as workloads increase, it’s worth deciding whether the current deployment model is still correctly sized.

Many organizations have turned this into a recurring business process: monitoring dashboards and key performance indicators (KPIs), narrowing down long-running queries, collaborating across teams to resolve them, and coaching users on efficient query patterns. This isn’t unique to Amazon Redshift. It’s a best practice for any production data system.

AWS Enterprise Support helps customers with these reviews. But even with specialist assistance, the process typically consumes 4–8 hours of focused effort per cluster: assembling diagnostic queries, interpreting results, cross-referencing documentation, and compiling findings into a prioritized report. Most teams recognize the value, but consistently deprioritize it in favor of feature delivery and day-to-day operations.

In a previous post, we demonstrated how to query Amazon Redshift using natural language with Kiro and the Amazon Redshift Model Context Protocol (MCP) server. That approach replaced manual schema navigation and hand-written SQL with conversational analytics. This post takes the next step: from asking questions about your data to asking questions about your cluster’s operational health.

The review_cluster tool in the Amazon Redshift MCP server makes the entire diagnostic process available from a single natural-language request. It evaluates 12 diagnostic areas, identifies potential issues, and returns prioritized recommendations linked directly to AWS documentation, covering both provisioned clusters and serverless workgroups in one invocation. What previously required hours of specialist effort now completes in a few minutes. This makes it practical to review your cluster weekly, after significant schema changes, or before peak traffic events.

In this post, you learn how to:

1. Run an automated operational review of your Amazon Redshift cluster using natural language.
2. Interpret the structured findings and recommendations.
3. Act on specific findings conversationally, turning diagnostics into remediation without leaving Kiro, Claude, or any MCP-compatible client.
4. Incorporate periodic reviews into your operational workflow.

What is the review_cluster tool?

A single natural-language request triggers a comprehensive diagnostic assessment. It complements the server’s discovery and query tools: those give you conversational access to your data, while review_cluster gives you the same for your cluster’s health. The tool executes a curated set of queries against Amazon Redshift system views, evaluates the results against known best-practice thresholds, and returns structured findings with prioritized recommendations. Every recommendation includes direct links to the relevant AWS documentation, so you can move from identification to remediation without searching. The entire process is read-only. No data is modified, no configuration is changed, and no resources are created. The tool observes and reports. Remediation decisions remain with you.

The tool returns a structured result containing:

• Signals evaluated: The total number of diagnostic checks executed.
• Findings: A list of triggered conditions, each with a name, the number of affected objects (tables, queries, nodes), and linked recommendation IDs.
• Recommendations: A deduplicated list of corrective actions, ordered by effort, with documentation links.

What it inspects

The review currently evaluates your cluster across 12 diagnostic areas:

1. Automatic Table Optimization (ATO): Whether the automated tuning actions of Amazon Redshift (encoding, sort keys, distribution styles) are completing successfully or not.
2. Table design recommendations: Amazon Redshift Advisor recommendations for encoding, sort keys, and distribution that have not yet been applied.
3. COPY and data ingestion performance: File sizing, parallelism relative to slice count, and ingestion throughput patterns.
4. External query (Spectrum) performance: Partition pruning effectiveness, file sizes, and scan efficiency for queries against external tables.
5. Materialized view health: Staleness, auto-refresh status, and maintenance overhead of materialized views.
6. Node and storage utilization: Disk usage, node type currency, and whether the cluster would benefit from migration to the latest instance generation.
7. Table-level statistics: Vacuum status, stale statistics, sort key effectiveness, distribution skew, and compression encoding coverage.
8. Query performance: The longest-running queries, nested loop joins, and disk spill patterns.
9. Workload usage patterns: How intensively the cluster is used throughout the day and whether the workload suits the current deployment model.
10. Workload Management (WLM) configuration: Queue setup, concurrency scaling, short query acceleration, priority settings, and query monitoring rules.
11. Workload evaluation: Whether the cluster’s utilization pattern suggests it could benefit from a different deployment model such as serverless.
12. Serverless scaling: Whether a serverless workgroup’s observed compute range falls within the AI-driven scaling window.

Not every area applies to every deployment, because some checks target configuration that only exists in one model. Four checks are provisioned-only: COPY parallelism relative to slice count, node and storage utilization, WLM configuration, and workload evaluation. These tune constructs that Amazon Redshift Serverless manages for you. Serverless has no nodes or slices to size, and it always uses automatic WLM rather than user-configured queues, so there is nothing for those checks to act on. The serverless-scaling check is the reverse: it evaluates AI-driven Redshift Processing Unit (RPU) scaling, which is specific to serverless and has no equivalent on a provisioned cluster. As a result, a provisioned cluster evaluates 11 of the areas and a serverless workgroup evaluates 8, and the tool reports the number actually run as signals evaluated.

Running your first review

The following steps cover what you need in place and how to start a review.

Prerequisites

Before running a review, make sure that you have:

• An MCP-compatible client configured with the Amazon Redshift MCP server (for example, Kiro). See the Amazon Redshift MCP server README for installation and configuration steps.
• Valid AWS credentials with the AWS Identity and Access Management (IAM) permissions the server requires.
• The sys:monitor role granted to the connecting database user, the one additional database permission review_cluster needs.

To grant this access, a database administrator with superuser privileges runs:

GRANT ROLE sys:monitor TO "IAMR:YourRoleName";

The database user name for IAM identities follows the format IAMR:RoleName or IAM:UserName. Confirm yours with SELECT current_user in the Amazon Redshift Query Editor. The double quotes in the GRANT statement are required for IAM identity names.

With prerequisites in place, the review is a single prompt:

Run a review of analytics-cluster

The agent identifies the target cluster, connects to the database, and executes the diagnostic assessment.

The following example shows the output from a review of a serverless workgroup. Because four provisioned-only areas don’t apply, the tool evaluated 8 of the 12 diagnostic areas, identified 8 findings, and mapped them to 6 distinct recommendations:

Cluster Review: analytics-cluster (Serverless, eu-west-2 / London)
Ran the diagnostic --- result: 8 signals evaluated, 8 findings, 6 recommendations.
Findings
- Missing statistics --- 16 queries hit tables that have never been analyzed
- Dist/Broadcast alerts --- 10 queries reshuffle data across nodes
- Nested loop joins / cross-joins --- 5 long-running queries
- Small tables without ALL distribution --- 4 tables
- Small tables with a manual sort key --- 4 tables
- Large table with data skew --- 1 table (slice imbalance ≥4x)
- Large table distributed by date/datetime --- 1 table
- Variable serverless workload in the AI-scaling range --- 1 workgroup
Recommendations
1. REC_003 --- Analyze tables (fix missing stats). Effort: Low. Run or schedule ANALYZE on the 16 affected tables. Lowest-effort, highest-impact item; also improves the poor distribution/plan decisions the optimizer is making. Docs: https://docs.aws.amazon.com/redshift/latest/dg/t_Analyzing_tables.html
2. REC_007 --- Right-size sort keys. Effort: Low. The 4 small tables carry manual sort keys that add overhead for little benefit --- switch them to ALTER TABLE [table] ALTER SORTKEY AUTO. Docs: https://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-sort-key.html
3. REC_008 --- Choose the right distribution style. Effort: Low--Medium. Small tables → DISTSTYLE AUTO/ALL; the skewed large table → pick a better DISTKEY (skew often from NULLs in the key); the date-distributed large table → reconsider the key or move to DISTSTYLE AUTO. This also resolves the 10 Dist/Broadcast query alerts. Docs: https://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-best-dist-key.html
4. REC_030 --- Tune serverless AI-driven scaling. Effort: Low. Review your price-performance target (Optimizes for cost / Balanced / Optimizes for performance) and pair it with Max capacity + Max RPU-hours caps to keep cost predictable. Adapts over 1--3 days. Docs: https://docs.aws.amazon.com/redshift/latest/mgmt/serverless-capacity.html
5. REC_009 --- Remove nested loop joins (cross-joins). Effort: Medium. Add join conditions to the 5 cross-join queries. Docs: https://docs.aws.amazon.com/redshift/latest/dg/query-performance-improvement-opportunities.html#nested-loop
6. REC_019 --- Add QMR rules. Effort: Medium. Add query monitoring rules to catch nested loops and long runtimes going forward (log/hop/abort actions). Docs: https://docs.aws.amazon.com/redshift/latest/dg/cm-c-wlm-query-monitoring-rules.html
Suggested starting point
Start with REC_003 (ANALYZE) --- cheapest, highest-impact, and likely a root cause behind several slow-query findings. Then apply the AUTO table-optimization changes in REC_007 and REC_008, which are mostly one-line ALTER TABLE statements.
Want me to pull the specific table names behind each finding and draft the exact ANALYZE / ALTER TABLE remediation statements?

Each finding represents an independent diagnostic condition that was triggered. The affected_row_count indicates how many objects match that specific condition. These counts describe different dimensions of the cluster and are not additive across findings.

When the tool returns zero findings, the cluster is operating within best-practice thresholds across the evaluated areas.

From findings to fixes

The review output isn’t a static report. It’s a starting point for an interactive conversation. Each finding identifies a specific condition, and each recommendation provides a clear remediation path with documentation links. From here, you can continue working within the same Kiro session to plan and prepare your next steps.

Recommendations fall into four broad categories:

• Quick configuration changes: Actions such as enabling concurrency scaling or short query acceleration require a single parameter change in the Amazon Redshift console or a brief API call. These are low-risk, high-impact adjustments that can often be applied immediately.
• Batch table operations: Findings related to table design (distribution style, sort keys, compression encoding) typically affect multiple tables. You can ask Kiro to list the specific tables involved and generate the corresponding ALTER TABLE statements. Review the generated SQL, then apply it through the Amazon Redshift Query Editor or your preferred SQL client.
• Data ingestion optimization: Findings related to COPY performance identify inefficiencies in how data is loaded. For example, source files might be too small, or file counts might not align with the cluster’s slice count. Addressing these requires changes upstream in your extract, transform, and load (ETL) pipeline or Amazon Simple Storage Service (Amazon S3) staging process rather than within Amazon Redshift itself.
• Architectural decisions: Recommendations such as migrating to Amazon Redshift Serverless or resizing to RG instances require broader evaluation. These are not single-command fixes. They involve capacity planning, workload testing, and potentially migration steps. The recommendation text and linked documentation provide the context needed to begin that planning.

After presenting findings, Kiro proposes the next best step to act upon, typically starting with the lowest-effort, highest-impact recommendation. Alternatively, you can direct the conversation yourself. For example:

• “Which tables are affected by the distribution style finding?”
• “What would the ALTER TABLE statements look like for those tables?”
• “Explain what concurrency scaling does and how to enable it.”
• “What are the trade-offs of migrating this workload to serverless?”

Kiro retrieves the relevant details, generates SQL where applicable, and references the documentation. The tool diagnoses and recommends, but does not modify your cluster. The decision to apply changes remains with you.

Best practices

Tips for getting the most out of review_cluster.

1. Embed reviews in your DataOps practice. Regular table and cluster maintenance is a foundational best practice for any production data warehouse, and its value comes from consistency. Operational reviews are one component of a broader DataOps discipline: the practice of maintaining data systems that are clean, reliable, governed, and always available. Define KPIs for your cluster, such as query latency percentiles, disk spill frequency, and WLM queue wait times. Then use periodic review_cluster runs to track your query and workload optimization progress against them. Over time, this creates a feedback loop: findings inform remediation, KPIs measure impact, and the next review validates improvement.
2. Run reviews on a regular cadence. Treat operational reviews like testing: the more routinely you run them, the sooner you catch drift before it affects users. Consider running a review weekly, after significant schema changes, after major data loads, or before anticipated peak traffic events.
3. Follow the documentation links. Every recommendation includes direct links to the relevant AWS documentation. These pages provide detailed guidance, edge cases, and configuration examples that go beyond what the recommendation text can cover. Use them as your primary reference when planning remediation.
4. Start with low-effort wins. The recommendations are ordered by effort. Begin with quick configuration changes (concurrency scaling, short query acceleration, query monitoring rules) before moving to structural changes that require broader planning. Early wins build confidence and often improve cluster performance enough to create headroom for larger changes.
5. Use the review as a baseline. Run a review before and after significant changes to measure their effect. For example, after applying table design recommendations, a follow-up review should show fewer table-related findings. This before-and-after pattern helps validate that your changes had the intended impact.

Conclusion

In this post, you learned how to run an automated operational review of your Amazon Redshift cluster using natural language with Kiro. The review_cluster tool evaluates 12 diagnostic areas, from table design and workload management to node utilization and data ingestion performance. It returns prioritized, actionable recommendations linked directly to AWS documentation.

What previously required a specialist to assemble diagnostic scripts, interpret system view outputs, and compile findings over several hours now completes in a single request. This shift makes it practical to incorporate operational reviews into your regular workflow rather than treating them as an infrequent, resource-intensive exercise.

To get started:

1. Make sure the Amazon Redshift MCP server is configured in Kiro (see the setup post).
2. Grant the sys:monitor role to your database user.
3. Ask Kiro to review your cluster.

To go further:

• Visit the Amazon Redshift MCP server documentation for the full tool reference.
• Read the MCP protocol to understand how AI agents integrate with external tools.
• Explore Kiro for additional capabilities including steering files, hooks, and agent automation.

About the authors