PCDE Monitor and optimize database performance Practice Test 9 — 15 Questions

Question 1

A data engineering team wants to monitor BigQuery query performance and slot utilization. Which TWO tools or features should they use? (Choose two.)

Accepted Answer

INFORMATION_SCHEMA tables (e.g., JOBS_BY_PROJECT). Option A, INFORMATION_SCHEMA tables such as JOBS_BY_PROJECT, is correct because these views expose per-job metadata including query text, elapsed/execution time, bytes processed, slot-milliseconds consumed, and errors, which is exactly what is needed to analyze BigQuery query performance and slot usage historically. Option B, Cloud Monitoring metrics like 'bigquery.googleapis.com/scheduler/slot_allocation', is correct because Cloud Monitoring surfaces BigQuery slot allocation, slot utilization, and query execution time as time-series metrics, enabling dashboards and alerts on slot consumption and performance trends. Option C, Cloud Logging audit logs, is not the right fit because audit logs record administrative and data-access events (who did what) rather than detailed query performance and slot-utilization metrics. Option D, the bq command-line tool with '--format=prettyjson', only changes output formatting of bq results and does not provide performance or slot monitoring. Option E, Cloud Profiler, is a continuous CPU/heap profiler for application code and has no visibility into BigQuery query execution or slot usage.

Answer

Cloud Logging (audit logs)

Answer

bq command-line tool with the '--format=prettyjson' flag

Answer

Cloud Profiler

Question 2

You are responsible for a Cloud SQL for MySQL instance that supports a content management system (CMS). The application frequently performs SELECT queries with ORDER BY and LIMIT. Recently, the response time for these queries has increased. The database has 4 vCPUs and 15 GB memory. You check the slow query log and find many queries that are taking over 1 second. The 'rows_examined' is much higher than 'rows_sent'. The EXPLAIN plan shows 'Using filesort' and 'Using temporary'. There is currently an index on the column used in the WHERE clause but not on the ORDER BY columns. The table has 5 million rows. What should you do to improve query performance?

Accepted Answer

Add a composite index on the columns used in the WHERE clause and the ORDER BY clause.. Adding a composite index on the columns used in the WHERE clause and the ORDER BY clause allows MySQL to avoid the expensive 'Using filesort' and 'Using temporary' operations. With a covering index, the database can retrieve rows in the required order directly from the index, eliminating the need to sort the result set after filtering. This dramatically reduces 'rows_examined' and improves query response time for SELECT queries with ORDER BY and LIMIT.

Answer

Increase the buffer pool size to 80% of memory.

Answer

Disable the query cache to reduce overhead.

Answer

Remove the ORDER BY clause and sort the results in application code.

Question 3

Your company runs a business intelligence (BI) dashboard on BigQuery that refreshes every hour. The dashboard queries are complex with multiple JOINs and aggregations. Recently, the queries started taking longer than 30 minutes, causing timeouts. You check the BigQuery monitoring and see that the slot utilization consistently reaches 100% during the dashboard refresh. The project uses a flat-rate pricing model with 1000 slots. Other team members run ad-hoc queries during the same period. What is the most effective action to improve the dashboard performance?

Accepted Answer

Create a separate reservation for the dashboard queries with a baseline of 500 slots and use a low priority job queue for ad-hoc queries.. Creating a separate reservation for the dashboard queries with a baseline of 500 slots ensures that the critical BI dashboard always has guaranteed compute capacity, preventing starvation by ad-hoc queries. Using a low-priority job queue for ad-hoc queries allows them to use any remaining idle slots without interfering with the dashboard's reserved slots. This directly addresses the 100% slot utilization and timeout issue without requiring query rewrites or schedule changes.

Answer

Rewrite the dashboard queries to use fewer joins and aggregations.

Answer

Increase the total number of slots to 2000 to provide more capacity for all queries.

Answer

Schedule the dashboard refresh to run at a different time when ad-hoc usage is low.

Question 4

A social media application uses Memorystore for Redis to cache user profiles and session data. Recently, the application experienced intermittent errors and high latency. You observe that the Redis CPU utilization is consistently above 90% and the cache hit ratio is 85%. The instance type is a Standard tier M2 (30 GB) with a maxmemory setting of 25 GB. The eviction policy is allkeys-lru. The number of keys is 10 million with an average value size of 2 KB. You suspect memory pressure is causing CPU spikes. What should you do to reduce CPU utilization and improve performance?

Accepted Answer

Upgrade the Memorystore instance to a larger size (e.g., M5 with 60 GB) to provide more CPU and memory resources.. Upgrading to a larger instance (e.g., M5 with 60 GB) directly addresses both the high CPU utilization and memory pressure. The M2 instance is CPU-bound with sustained >90% usage, and the 85% cache hit ratio indicates that evictions are already occurring under the allkeys-lru policy, causing CPU spikes from eviction overhead and key lookups. A larger instance provides more CPU cores and memory, reducing eviction frequency and allowing the cache to serve more requests without thrashing.

Answer

Reduce the maxmemory setting to force more aggressive eviction and free up memory.

Answer

Change the eviction policy to volatile-lru to prefer evicting keys with TTL.

Answer

Implement Redis monitoring with Cloud Monitoring and set alerts to notify when CPU exceeds 80%.

Question 5

You are managing a Cloud SQL for MySQL instance that supports a web application. Recently, users have reported that the application is responding slowly during peak hours. You examine the Query Insights dashboard and see that a specific query is running frequently and has a high execution time. The query involves JOINs on three tables, each with tens of thousands of rows. The query plan shows a full table scan on two tables. What should you do first to improve performance?

Accepted Answer

Add indexes on the columns used in JOIN conditions.. Adding indexes on the columns used in JOIN conditions will improve query performance by allowing the database to quickly locate matching rows instead of scanning entire tables. The query plan shows full table scans on two tables, indicating missing indexes. Indexes reduce the amount of data read and speed up JOIN operations.

Answer

Enable the query cache flag in Cloud SQL database flags.

Answer

Increase the instance size to provide more memory and CPU.

Answer

Rewrite the query to use subqueries instead of JOINs.

Question 6

You administer a Cloud SQL for PostgreSQL instance running with 4 vCPUs and 15 GB of memory. The application frequently runs complex read-only reporting queries during business hours. Recently, the database started throwing 'out of memory' errors, and the instance's memory usage is consistently above 90%. You have enabled the pgtune recommendations and applied them. The workload is read-heavy and does not require a high write throughput. Which change would most effectively reduce memory pressure while maintaining query performance?

Accepted Answer

Scale up the instance to a higher memory tier, such as 8 vCPUs and 30 GB of memory.. Scaling up to a higher memory tier directly addresses the root cause: the instance has insufficient RAM for the read-heavy reporting workload, as evidenced by memory usage consistently above 90% and OOM errors even after pgtune tuning. With 30 GB, PostgreSQL can allocate larger shared_buffers and work_mem, allowing complex queries to complete without spilling to disk or exhausting memory. This preserves query performance because the additional memory is used for caching and sort/hash operations, which are critical for read-only reporting queries.

Answer

Enable the pgzstd extension to compress data in memory.

Answer

Set up connection pooling using PgBouncer to limit the number of concurrent connections.

Answer

Decrease the shared_buffers configuration parameter to free memory for other uses.

Question 7

Your team runs a Cloud SQL for PostgreSQL 15 instance that powers an internal analytics dashboard. Users report the dashboard is slow every morning between 08:00 and 09:00, when many concurrent reports run. You enable Query Insights and see that several queries show a high 'DB load' contribution but low CPU utilization on the instance. You want to determine whether the bottleneck is lock waits, I/O waits, or network waits before changing any instance settings. What should you do?

Accepted Answer

In the Query Insights dashboard, open the query details for the top queries and examine the wait event breakdown (for example, Lock, IO, LWLock) to see which wait type dominates.. Because DB load is elevated while CPU stays low, the workload is dominated by waiting rather than computation, so the decisive evidence is the wait event breakdown that Query Insights records per query. Examining those wait categories distinguishes lock contention from storage and network delays and points to the right remediation, whereas timing-only tools and brute-force scaling leave the actual blocker unknown.

Answer

Raise the instance's vCPU count during the morning window and observe whether dashboard latency improves.

Answer

Set log_min_duration_statement to 0 to capture every statement, then parse the PostgreSQL logs for slow queries.

Answer

Enable the pg_stat_statements extension and sort by total_exec_time, then add indexes to every query that appears in the top ten.

Question 8

A Cloud Spanner instance runs an OLTP workload with a read-heavy API. Latency for point reads is acceptable, but a nightly analytics job that issues large range scans with strong reads is causing the p99 latency of the production API to spike. You cannot move the analytics job to a separate instance this quarter. You want to reduce the impact of the analytics scans on the production traffic without changing the API's consistency guarantees. What should you do?

Accepted Answer

Reconfigure the analytics job to use stale reads with an exact staleness bound of 15 seconds, and keep strong reads only for the production API.. A bounded stale read gives the analytics scans a consistent snapshot that avoids competing for the newest data versions, which is exactly the mechanism driving contention with strongly consistent point reads. Because the API keeps strong reads, its guarantees are preserved, and the batch job still produces coherent results. This reduces interference without moving the job or weakening production consistency.

Answer

Increase the instance's node count so the analytics scans have more CPU to run against, leaving read types unchanged.

Answer

Convert the production API's point reads to stale reads so both workloads use the same read mode and contention evens out.

Answer

Change the analytics job to use a read-write transaction with a higher priority so it completes faster and stops interfering.

Question 9

A Cloud SQL for PostgreSQL instance supports a customer-facing web application. During peak hours, the database experiences high CPU utilization and slow query response times. You have enabled the pg_stat_statements extension and collected data. You want to identify the top five queries consuming the most total time. Which query should you use?

Accepted Answer

SELECT query, total_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;. To find the top five queries by total time consumed, you need to aggregate the total execution time, which is available in the total_time column of pg_stat_statements. Ordering by total_time descending and limiting to five gives the desired result. Other columns like calls, mean_time, or rows do not directly represent total time consumption and could mislead optimization efforts.

Answer

SELECT query, rows FROM pg_stat_statements ORDER BY rows DESC LIMIT 5;

Answer

SELECT query, mean_time FROM pg_stat_statements ORDER BY mean_time DESC LIMIT 5;

Answer

SELECT query, calls FROM pg_stat_statements ORDER BY calls DESC LIMIT 5;

Question 10

A Cloud Spanner instance is used for a global gaming application. The database has a table named 'PlayerScores' with a monotonically increasing primary key 'ScoreID'. The application performs many inserts and updates. During peak hours, you observe high write latency and increased CPU utilization on the Spanner instance. You suspect that hotspotting is occurring. Which action should you take to mitigate the hotspotting?

Accepted Answer

Use a UUID or hash the primary key to distribute writes across the key space.. Hotspotting in Cloud Spanner occurs when writes are concentrated on a narrow key range, often due to monotonically increasing keys. Distributing the key space by using UUIDs or hash values spreads writes across multiple splits, allowing Spanner to scale horizontally and reduce latency. Increasing nodes, adding indexes, or interleaving do not fix the underlying key distribution problem.

Answer

Increase the number of nodes in the Spanner instance.

Answer

Use interleaved tables to store scores for each player separately.

Answer

Enable a secondary index on the 'ScoreID' column.

Question 11

You are the database engineer for a Cloud Spanner instance that serves a global retail application. The instance uses regional configuration us-central1 with 3 nodes. During a flash sale, you observe that CPU utilization on the instance stays above 90% for 15 minutes and P99 read latency climbs from 8 ms to 250 ms. You want to reduce latency quickly while keeping the same schema and query patterns. What should you do?

Accepted Answer

Add more nodes to the instance to increase compute capacity.. Cloud Spanner scales horizontally by adding nodes, and each node provides a fixed amount of CPU and serving capacity. When CPU utilization stays very high and latency rises, the instance is compute-bound, so adding nodes is the direct fix. Changing region topology or adding indexes does not increase the serving capacity that the saturated instance needs.

Answer

Change the instance to a multi-region configuration such as nam3.

Answer

Enable the Spanner query optimizer's latest version and rerun the queries.

Answer

Create a secondary index on the columns used by the hottest read queries.

Question 12

You are using Cloud Bigtable to store time-series data. The table has a single column family and uses a row key that is a combination of a sensor ID and a timestamp, with the sensor ID as a prefix. You notice that read latency for queries that fetch the latest data for a specific sensor is high. The queries use a row key range that starts with the sensor ID and ends with the sensor ID plus a high timestamp. What is the most effective way to improve read latency for these queries?

Accepted Answer

Change the row key to use a reversed timestamp as a prefix followed by the sensor ID.. The row key design with sensor ID as prefix and timestamp as suffix means that the latest data for a sensor is at the end of the row range. To fetch the latest data, the query must scan through all historical data for that sensor, causing high latency. Reversing the timestamp so that the latest timestamp has the smallest value (e.g., using Long.MAX_VALUE - timestamp) places the latest data at the beginning of the range, allowing the query to read only the first few rows. This dramatically reduces the number of rows scanned and improves latency.

Answer

Increase the number of nodes in the Bigtable cluster to improve throughput.

Answer

Enable a replication cluster in a different zone to distribute read load.

Answer

Use a filter to limit the number of versions returned per row.

Question 13

A Cloud Spanner instance with regional configuration us-central1 is serving a read-heavy workload. The application issues many small, single-row reads via the Spanner API. You observe high CPU utilization and increased read latency. Query plans show that these reads are using a secondary index. What should you do to improve performance?

Accepted Answer

Create a covering index that includes all columns needed by the query.. A covering index allows the query to be satisfied entirely from the index, eliminating the extra base-table lookup that occurs when the index does not include all selected columns. This reduces both latency and CPU for single-row reads. Increasing nodes, forcing a table scan, or using Data Boost do not fix the inefficient index access pattern.

Answer

Enable Data Boost for the Spanner instance.

Answer

Increase the number of nodes in the Spanner instance.

Answer

Change the query to use a full table scan instead of the index.

Question 14

A Cloud SQL for PostgreSQL instance is running a reporting workload. The database engineer notices that the instance's memory is nearly exhausted and that the operating system is swapping heavily, causing query latency to spike. The instance has 8 GB of RAM, and pg_stat_activity shows many concurrent connections running large sorts and hash joins. Which action should the engineer take first to relieve the memory pressure?

Accepted Answer

Reduce the work_mem flag and add a connection pooler in front of the instance.. On a memory-constrained Cloud SQL for PostgreSQL instance, per-session work_mem allocations for sorts and hash joins accumulate across concurrent connections. Lowering work_mem forces larger operations to spill to disk, and a connection pooler bounds the number of concurrent backends, together reducing total memory demand. These changes address the root cause without resizing the instance.

Answer

Enable the pg_stat_statements extension and analyze the slowest queries.

Answer

Increase the max_connections flag so more sessions can run concurrently.

Answer

Increase the shared_buffers flag to 6 GB so more data is cached in memory.

Question 15

You manage a Cloud SQL for PostgreSQL instance that supports an online order-processing application. During peak hours, users report slow response times. You observe that the instance's CPU utilization is consistently around 95%, and the number of active connections is at the maximum. You want to quickly identify the top SQL statements contributing to CPU load. Which Cloud SQL feature should you use?

Accepted Answer

Enable and review Query Insights for Cloud SQL.. Query Insights for Cloud SQL is the native tool for monitoring query performance on Cloud SQL instances. It automatically collects and aggregates query metrics, highlighting the most resource-intensive statements. By using it, you can rapidly identify the queries causing high CPU utilization and take corrective action, such as adding indexes or rewriting queries, without manual overhead.

Answer

Enable pg_stat_statements and query the view manually.

Answer

Enable the slow query log and analyze the log entries.

Answer

Use Cloud Monitoring to create a custom dashboard for CPU utilization.