DBS-C01 Monitoring and Troubleshooting Practice Test 28 — 15 Questions

Question 1

A database specialist is troubleshooting an Amazon RDS for SQL Server DB instance that is experiencing slow query performance. The specialist suspects that a specific query is causing high CPU usage. Which AWS tool should be used to identify the top SQL statements by CPU consumption?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Performance Insights. Performance Insights is a database performance tuning and monitoring feature that helps you quickly assess the load on your database and determine when and where to take action. It provides a top SQL view that shows the SQL statements contributing most to database load, including CPU usage. For SQL Server, it captures this data without requiring additional setup. The other tools lack this query-level detail.

Answer

Amazon CloudWatch Logs

Answer

Amazon RDS Enhanced Monitoring

Answer

AWS CloudTrail

Question 2

A company uses Amazon ElastiCache for Redis to cache session data for a high-traffic web application. The operations team notices that the cache hit rate has dropped from 95% to 70% over the past week, leading to increased latency. They suspect that many keys are expiring prematurely or being evicted. The Redis cluster is running in cluster mode with 3 shards. Which action should the team take to determine the cause of the reduced hit rate?

Accepted Answer

Analyze the KeyspaceHits and KeyspaceMisses metrics in CloudWatch, and compare with Evictions and ExpiredKeys.. The most effective way to diagnose a drop in cache hit rate is to examine CloudWatch metrics that directly measure hits and misses (KeyspaceHits, KeyspaceMisses) alongside metrics that explain key removal (Evictions, ExpiredKeys). This combination reveals whether the issue is due to memory pressure causing evictions or TTLs causing expirations, enabling targeted remediation.

Answer

Review the ElastiCache event log for messages about failed key lookups.

Answer

Use the Redis MONITOR command to capture all commands in real time and look for patterns of key access.

Answer

Check the Evictions and CurrConnections metrics in Amazon CloudWatch for each node.

Question 3

A database specialist is managing an Amazon DocumentDB cluster. The application team reports that queries are slower than usual, and the specialist suspects that the cluster's cache hit ratio has dropped. Which Amazon CloudWatch metric should the specialist monitor to evaluate the buffer cache hit ratio for the DocumentDB cluster?

Accepted Answer

BufferCacheHitRatio. BufferCacheHitRatio is the specific CloudWatch metric for Amazon DocumentDB that reports the percentage of requests served from the buffer cache. Monitoring this metric allows the specialist to confirm whether the cache hit ratio has dropped, which would explain slower queries due to increased disk reads. Other metrics like DiskQueueDepth or FreeableMemory provide related but indirect information and do not directly measure cache effectiveness.

Answer

DatabaseCursorsTimedOut

Answer

DiskQueueDepth

Answer

FreeableMemory

Question 4

A database specialist is troubleshooting an Amazon RDS for SQL Server DB instance that is experiencing slow query performance. The specialist suspects that a specific query is causing high CPU usage. Which AWS tool should be used to identify the top SQL statements by CPU consumption?

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Query the sys.dm_exec_query_stats dynamic management view to retrieve execution statistics for cached query plans.. Performance Insights offers a top SQL view with wait events, and SQL Server DMVs like sys.dm_exec_query_stats provide execution statistics for cached plans. Together they identify the most resource-intensive queries and the waits they experience. The other options either generate excessive logs, track the wrong activity type, or disable the needed analysis.

Answer

Enable and review the general query log to capture every SQL statement executed on the instance.

Answer

Use AWS CloudTrail to analyze the API calls made to the RDS instance and identify slow queries.

Answer

Configure RDS Performance Insights to capture only OS-level metrics and disable database load analysis.

Question 5

A company runs an Amazon RDS for SQL Server DB instance with Multi-AZ. During a failover test, the application experiences a brief outage, but after the failover, the database specialist notices that the secondary AZ's DB instance is not receiving updates from the primary. The RDS console shows the DB instance status as 'available' and the Multi-AZ status as 'pending'. Which action should the specialist take to resolve this issue?

Accepted Answer

Wait for the Multi-AZ deployment to complete synchronization and monitor the RDS event log for status changes.. The 'pending' Multi-AZ status in Amazon RDS indicates that a change to the Multi-AZ configuration is in progress, such as creating or synchronizing a standby. After a failover, the old primary becomes the new standby, and RDS must synchronize it with the new primary. This process can take time. The specialist should wait for it to complete and monitor the RDS event log for status updates, as no manual intervention is required.

Answer

Reboot the DB instance with a failover to force the secondary to become the primary.

Answer

Restore the DB instance from the latest automated backup to a new Multi-AZ deployment.

Answer

Modify the DB instance to disable Multi-AZ, wait for the change to apply, and then re-enable Multi-AZ.

Question 6

A database specialist is monitoring an Amazon Aurora MySQL cluster with a writer and one reader. The application uses the reader endpoint for read-only queries. The specialist observes that the reader instance's ReplicaLag metric is consistently increasing and is currently at 300 seconds. The writer instance shows high write throughput. Which action should the specialist take to reduce the replica lag?

Accepted Answer

Modify the reader instance to a larger instance class to increase its capacity to apply changes.. The reader instance is likely unable to keep up with the write volume due to insufficient resources. Scaling up the reader instance provides more compute and memory to apply the replication stream faster, directly reducing replica lag. Other options either do not impact replication performance or are counterproductive.

Answer

Enable Multi-AZ on the writer instance to improve replication performance.

Answer

Increase the size of the Aurora cluster volume to provide more IOPS for replication.

Answer

Change the reader endpoint to route queries to the writer instance.

Question 7

A startup runs an Amazon Aurora MySQL cluster and wants to receive an alert when the average number of database connections exceeds a threshold for five consecutive minutes. The team already uses Amazon CloudWatch. Which combination of CloudWatch features should the database specialist configure to meet this requirement with the least effort?

Accepted Answer

A CloudWatch alarm on the `DatabaseConnections` metric with a period of 300 seconds and a threshold evaluated over one datapoint.. Aurora MySQL emits the `DatabaseConnections` metric to CloudWatch, and a CloudWatch alarm can evaluate that metric over a 300-second period to trigger when the average exceeds the threshold for five minutes. This requires only an alarm configuration and an SNS action, making it the least-effort native solution. Metric filters, EventBridge rules, and dashboard widgets do not evaluate this metric for alerting.

Answer

An Amazon EventBridge rule that matches RDS events for connection count and invokes an Amazon SNS topic.

Answer

A CloudWatch metric filter on the Aurora error log and an AWS Lambda function to evaluate the filter.

Answer

A CloudWatch dashboard widget with a custom threshold line and an SNS subscription for visual notifications.

Question 8

A database specialist is responsible for an Amazon RDS for MySQL DB instance. The specialist needs to receive an alert when the average number of database connections exceeds 80% of the maximum allowed for the instance's parameter group over a 5-minute period. Which combination of CloudWatch alarm configuration steps should the specialist use?

Accepted Answer

Create a CloudWatch alarm on the DatabaseConnections metric with a threshold based on the max_connections parameter value, and set the period to 300 seconds with the statistic set to Average.. The DatabaseConnections metric is the correct measure for connection count. To alert at 80% of max_connections, the threshold must be calculated from that parameter, and a 300-second period with Average statistic provides the 5-minute average. The other metrics and static thresholds do not align with the stated requirement.

Answer

Create a CloudWatch alarm on the CPUUtilization metric with a threshold of 80%, and set the period to 300 seconds.

Answer

Create a CloudWatch alarm on the FreeableMemory metric with a threshold of 20% of total memory, and set the period to 300 seconds.

Answer

Create a CloudWatch alarm on the DatabaseConnections metric with a static threshold of 80 connections, regardless of the instance's max_connections parameter.

Question 9

A company uses Amazon ElastiCache for Redis to cache session data for a web application. The operations team wants to monitor the cache hit rate to ensure the cache is effective. They need to set up a CloudWatch alarm that triggers when the cache hit rate falls below 80% over a 5-minute period. Which metric should they use to calculate the cache hit rate?

Accepted Answer

CacheHits and CacheMisses. To calculate the cache hit rate, the team needs the number of successful cache lookups (CacheHits) and the number of unsuccessful lookups (CacheMisses). The hit rate is CacheHits / (CacheHits + CacheMisses). ElastiCache for Redis provides these metrics in CloudWatch, and metric math can be used to create a custom metric for the hit rate. This allows setting an alarm when the hit rate drops below the threshold.

Answer

CurrConnections and NewConnections

Answer

Evictions and Reclaimed

Answer

CPUUtilization and EngineCPUUtilization

Question 10

A company runs an Amazon Aurora PostgreSQL DB cluster. A database specialist notices that the writer instance's CPU utilization is consistently high, and Performance Insights shows a large number of active sessions waiting on the LWLock:BufferContent event. Which action should the specialist take to reduce the impact of this wait event?

Accepted Answer

Identify and optimize queries that repeatedly access the same hot pages, such as by adding appropriate indexes or partitioning.. The LWLock:BufferContent wait event signals contention on shared buffer pages, typically from concurrent queries hitting the same hot data. The most effective remediation is to identify those queries and reduce contention by adding indexes, partitioning, or rewriting queries. Increasing resources or restarting does not resolve the underlying access pattern causing the lock waits.

Answer

Increase the instance size to a larger instance class with more vCPUs.

Answer

Increase the value of the shared_buffers parameter to reduce buffer contention.

Answer

Enable the pg_stat_statements extension and restart the DB instance to reset statistics.

Question 11

A database administrator is monitoring an Amazon RDS for MySQL DB instance. The administrator notices that the 'FreeableMemory' metric is consistently low, and the 'SwapUsage' metric is increasing. The DB instance is running a memory-intensive workload. What is the most likely impact on database performance, and what should the administrator do to resolve the issue?

Accepted Answer

Performance will degrade due to swapping; the administrator should scale up to a larger DB instance class with more memory.. Consistently low FreeableMemory and increasing SwapUsage indicate that the RDS for MySQL instance is under memory pressure and swapping to disk. Swapping causes severe performance degradation because disk access is orders of magnitude slower than memory. The correct action is to scale up to a larger DB instance class with more memory to eliminate swapping and improve performance.

Answer

Performance will improve because swapping frees up memory for the database; the administrator should take no action.

Answer

Performance will degrade due to increased disk I/O; the administrator should enable Enhanced Monitoring to diagnose further.

Answer

Performance will be unaffected because the database uses the buffer pool; the administrator should increase the buffer pool size.

Question 12

A database specialist is responsible for an Amazon DocumentDB cluster. The specialist needs to monitor the cluster's CPU utilization and set up an alarm if it exceeds 80% for 10 minutes. Which AWS service should be used to create this alarm?

Accepted Answer

Amazon CloudWatch. Amazon CloudWatch is the AWS service designed for monitoring resources and applications. It collects metrics from DocumentDB, such as CPUUtilization, and allows you to create alarms that trigger when a metric crosses a threshold for a specified duration. The other services either log API calls, route events, or provide best-practice checks, but none provide metric-based monitoring and alarms.

Answer

Amazon EventBridge

Answer

AWS Trusted Advisor

Answer

AWS CloudTrail

Question 13

A company uses Amazon ElastiCache for Redis (cluster mode disabled) with a single shard and two replicas. The application experiences occasional latency spikes when reading from the primary node. CloudWatch metrics show high CPU utilization on the primary node and increased replication lag on the replicas. The application uses the primary endpoint for both reads and writes. What is the most likely cause of the latency spikes?

Accepted Answer

The primary node is overwhelmed by read requests, causing CPU contention and increased replication lag.. The primary node is handling both reads and writes because the application uses the primary endpoint. This increases CPU utilization, leading to latency spikes. Replication lag increases because the primary is busy. Distributing read traffic to replicas would alleviate the load on the primary and improve overall performance.

Answer

The replicas are not properly configured to handle read traffic, causing the primary to handle all reads.

Answer

The replication lag is causing the primary to wait for replicas to acknowledge writes, increasing latency.

Answer

The ElastiCache cluster's parameter group has 'maxmemory-policy' set to 'noeviction', causing memory pressure and latency.

Question 14

A database specialist is monitoring an Amazon Aurora PostgreSQL cluster. The cluster has a writer and one reader. The application connects to the writer endpoint for writes and the reader endpoint for reads. During peak hours, the specialist notices that the reader instance's CPU is at 95% while the writer's CPU is at 40%. The application reports increased read latency. CloudWatch metrics show that the reader's 'SelectThroughput' is high and 'BufferCacheHitRatio' is low. What is the most likely cause of the high reader CPU?

Accepted Answer

The reader instance is under-provisioned and cannot handle the read workload, causing high CPU and low buffer cache hit ratio.. The reader instance is likely under-provisioned for the read workload. High SelectThroughput with a low BufferCacheHitRatio means many queries are reading from disk rather than memory, increasing CPU usage. Upgrading the reader instance to a larger class provides more memory for caching and more CPU to handle concurrent queries, reducing latency.

Answer

The reader endpoint is misconfigured and routing read traffic to the writer, causing the writer to handle reads and the reader to be idle.

Answer

The Aurora cluster's parameter group has 'max_connections' set too low, causing connection queuing and high CPU on the reader.

Answer

The writer instance is not sending updates to the reader, causing the reader to have stale data and perform full table scans.

Question 15

A database specialist is monitoring an Amazon RDS for SQL Server DB instance using Enhanced Monitoring. The specialist notices that the 'Disk Read Latency' metric is consistently high, while 'Disk Write Latency' is normal. The DB instance uses General Purpose SSD (gp2) storage. The application performs a high volume of read operations. What is the most likely cause of the high read latency?

Accepted Answer

The DB instance's memory is insufficient, causing frequent physical reads from disk instead of reads from the buffer pool.. High Disk Read Latency with normal Disk Write Latency on an RDS for SQL Server instance often indicates that the buffer pool is not large enough to cache frequently accessed data. This forces the database engine to perform physical reads from disk, which are slower than memory reads. Increasing the instance's memory or optimizing queries to reduce the working set can alleviate the issue. gp2 burst depletion would affect both reads and writes, and insufficient provisioned IOPS is not applicable to gp2.

Answer

The storage volume is not configured with enough provisioned IOPS, causing read operations to be throttled.

Answer

The gp2 storage volume's burst balance has been depleted, causing reduced IOPS for both reads and writes.

Answer

The read latency is caused by network latency between the application and the DB instance, not by the storage subsystem.