DVA-C02 Troubleshooting and Optimization Practice Question
A developer is troubleshooting a slow-running query on an Amazon RDS for MySQL database. The query is used by a reporting application and takes over 30 seconds to complete. The database is a db.r5.large instance with 200 GB of gp2 storage. Which TWO actions should the developer take to improve query performance?
⚠ Common exam trap
DVA-C02 often tests the misconception that scaling storage or enabling Multi-AZ improves query performance, when the real fix is query-level tuning (indexes, execution plan review).
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Review the slow query log to identify the query and its execution plan.
Option B is correct because the MySQL slow query log captures queries exceeding the long_query_time threshold (default 10 seconds), and reviewing it along with EXPLAIN output reveals the query's execution plan, helping pinpoint full table scans, missing indexes, or inefficient joins. Option D is correct because adding appropriate indexes on the columns used in WHERE, JOIN, and ORDER BY clauses lets MySQL satisfy the query with index lookups instead of full table scans, which is the most direct fix for a slow reporting query. Option A is not appropriate because idle connections consume minimal resources and terminating them does not address query execution inefficiency. Option C is not appropriate because increasing gp2 storage size only raises the baseline IOPS (3 IOPS/GB) and burst balance; it does not fix a poorly optimized query and is a costly workaround. Option E is not appropriate because Multi-AZ is a high-availability feature that maintains a standby replica for failover, not a read-scaling mechanism, so it does not improve query performance.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Terminate idle connections to free up resources.
Why it's wrong here
Terminating idle connections does not address the root cause of a slow query because idle sessions consume negligible CPU and memory resources. In RDS, each connection uses some memory, but the sudden kill of sessions can cause application errors. The performance bottleneck is likely the query's execution plan, not connection count, so freeing trivial resources won't speed up the query.
- ✓
Review the slow query log to identify the query and its execution plan.
Why this is correct
Reviewing the slow query log is the first diagnostic step because it captures queries that exceed a specified duration, along with their execution time and connection metadata. Once identified, use EXPLAIN to analyze the execution plan, exposing table scans, missing indexes, or poor join ordering. This evidence-based approach tells you exactly which query to optimize and whether to add indexes or rewrite the query.
- ✗
Increase the allocated storage to 500 GB to improve I/O performance.
Why it's wrong here
Increasing the storage size to 500 GB may allow higher baseline IOPS on gp2 volumes, but it will not fix a query that is CPU-bound or badly optimized. The slow query likely suffers from a full table scan or inefficient join, not from disk throughput limits. Additionally, modifying storage can require downtime and increases cost, whereas the query itself remains unoptimized.
- ✓
Add appropriate indexes to the tables involved in the query.
Why this is correct
Adding appropriate indexes is a valid corrective action because an index can transform a full table scan into a narrow index seek, dramatically reducing the number of rows evaluated. After reviewing the slow query log and execution plan, create composite indexes tailored to the WHERE, JOIN, and ORDER BY clauses of that query. Be careful not to over-index, since each index adds overhead to write operations and consumes storage.
- ✗
Enable Multi-AZ deployment for better read performance.
Why it's wrong here
Enabling Multi-AZ deployment creates a synchronous standby replica for failover but does not serve read traffic, so it has no impact on query performance. Applications still connect to the primary instance, leaving the query execution unchanged. For scaling reads you would need to add one or more Read Replicas, and for query optimization you need to fix the query plan itself.
Go deeper
Related to this question
About these practice questions
This DVA-C02 question is part of Courseiva's 1,135-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Amazon Web Services exam blueprint
This DVA-C02 practice question is part of Courseiva's free Amazon Web Services certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DVA-C02 exam.