Production SQL Query Performance and Warehouse Cost Optimization Report

Role: Principal Database Reliability Engineer specializing in high-throughput SaaS analytical workloads and relational query optimization.

Context

• Target Database Engine: {{database_engine}}
• Target SQL Query: {{problematic_query}}
• Execution Plan & Telemetry: {{execution_plan_metrics}}
• Schema & Index Topology: {{schema_definitions}}
• Target SLAs & Cost Boundaries: {{sla_and_budget_targets}}
• Workload & Concurrency Profile: {{workload_concurrency}}

Task

Produce a comprehensive SQL query performance remediation report that isolates execution bottlenecks, restructures query anti-patterns, prescribes index or clustering strategy revisions, and estimates concrete operational compute cost reductions.

Method

1. Parse the provided execution plan telemetry against the schema topology to pinpoint execution hotspots such as full table scans, spills to disk, and accidental Cartesian joins.
2. Analyze the syntactic structure of {{problematic_query}} for non-sargable predicates, implicit type conversions, inefficient window functions, and redundant subqueries.
3. Cross-reference index usage with {{workload_concurrency}} to evaluate locking behavior, memory overhead, and cache hit ratios under peak analytical load.
4. Draft an optimized SQL rewrite incorporating engine-specific query constructs native to {{database_engine}}.
5. Formulate precise DDL recommendations for missing composite indexes, partitioning schemes, or clustering keys based on {{schema_definitions}}.
6. Project latency improvements against {{sla_and_budget_targets}} using standard cost-based query modeling principles.
7. Model warehouse compute savings resulting from decreased data scan volumes and reduced vCPU utilization.
8. Define a structured deployment, testing, and rollback protocol to safely validate the rewritten query under production traffic.

Constraints

• MUST provide syntactically valid, production-ready SQL rewrites compatible with {{database_engine}}.
• MUST NOT recommend destructive schema modifications that degrade write performance beyond acceptable tolerances.
• Quantified performance improvement estimates MUST directly correlate with the data provided in {{execution_plan_metrics}}.
• Clearly flag any semantic edge cases where query restructuring alters handling of NULL values or collation orders.
• Exclude speculative hardware upscaling recommendations in favor of code-level and index optimizations.

Output format

1. Executive Summary & Cost Impact Matrix (max 150 words plus a 4-column comparison table: Metric, Baseline, Projected, Delta)
2. Execution Bottleneck Breakdown (bulleted root-cause analysis citing execution plan operators)
3. Optimized SQL Query Rewrite (fully annotated SQL block with inline rationale comments)
4. DDL & Indexing Strategy (runnable DDL statements with operational justification)
5. Deployment, Validation, and Rollback Protocol (ordered checklist with verification queries)

Self-review

1. Verify that the rewritten SQL query preserves exact functional output parity and handles NULL values identically to the original.
2. Confirm that all proposed DDL statements adhere strictly to the dialect and syntax of {{database_engine}}.
3. Ensure latency and cost reduction claims are mathematically defensible based on {{execution_plan_metrics}}.
4. Check that no generic placeholders, pseudo-code, or omitted query fragments exist in the code blocks.