DVA-C02 Troubleshooting and Optimization Practice Question

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