PCDE Practice Test 8 — 10 Questions

Question 1

You are managing a Cloud SQL for PostgreSQL instance that is experiencing high CPU usage and slow query performance. You notice that the database has a high number of idle-in-transaction connections. Which immediate action should you take to reduce CPU load without disrupting active transactions?

Accepted Answer

Set the cloudsql.enable_idle_in_transaction_session_timeout flag to true and configure idle_in_transaction_session_timeout.. Option C is correct because setting the `cloudsql.enable_idle_in_transaction_session_timeout` flag to true and configuring `idle_in_transaction_session_timeout` allows Cloud SQL to automatically terminate idle-in-transaction connections after a specified timeout, reducing CPU load without manually killing connections or disrupting active transactions. This is a built-in, non-disruptive mechanism that targets only connections that are holding resources while idle, freeing up CPU and memory for active queries.

Answer

Use VPC firewall rules to block new connections until the issue resolves.

Answer

Kill all idle-in-transaction connections using pg_terminate_backend.

Answer

Set a statement_timeout at the session level for new connections.

Question 2

You are running a production workload on Cloud Bigtable and notice that read latency has increased. Upon reviewing the monitoring dashboard, you see that CPU utilization is below 50% but the number of active tablets is high. What is the most likely cause of the increased read latency?

Accepted Answer

There are too many tablets, causing increased metadata operations and slower reads.. In Cloud Bigtable, each tablet is a contiguous range of rows managed by a tablet server. When the number of active tablets is high, the tablet server must perform more metadata operations (e.g., splitting, merging, and serving multiple tablets) which increases per-request overhead and can degrade read latency. This is true even when CPU utilization is below 50%, because the overhead is not purely CPU-bound but involves increased I/O and coordination.

Answer

Read requests are being throttled due to exceeding IOPS limits.

Answer

A hot node is throttling read requests.

Answer

The cluster is underprovisioned, causing resource contention.

Question 3

You are monitoring a Cloud Spanner instance and see that the average commit latency is high. The application performs many single-row inserts. Which metric would you check first to understand the root cause?

Accepted Answer

Lock conflicts (e.g., Spanner/API/Lock_wait).. High commit latency for single-row inserts in Cloud Spanner is often caused by lock conflicts, as concurrent transactions may contend for the same row or index. The metric Spanner/API/Lock_wait directly measures time spent waiting for locks, making it the first metric to check. High commit latency without high lock wait suggests other issues, but lock conflicts are the most common root cause for write-heavy single-row workloads.

Answer

Read latency.

Answer

Storage utilization.

Answer

CPU utilization per node.

Question 4

A company runs a BigQuery data warehouse. They notice that query performance has degraded over time. The data is loaded daily from Cloud Storage using batch loads. Which action is most likely to improve query performance?

Accepted Answer

Partition and cluster tables based on common query filters.. Partitioning and clustering tables based on common query filters directly reduces the amount of data scanned per query by organizing data into physical segments. In BigQuery, this allows the query engine to prune entire partitions and clusters, significantly lowering I/O and improving performance without additional cost or complexity.

Answer

Increase the number of slots in the reservation.

Answer

Create materialized views for all frequent queries.

Answer

Migrate the data to Cloud SQL for better performance.

Question 5

You are troubleshooting a slow-performing query on Cloud Spanner. The query scans a large table with a secondary index. Which TWO metrics from the Query Insights dashboard would most directly indicate the source of the performance issue?

Accepted Answer

CPU time. CPU time (A) is correct because high CPU usage indicates that the query is performing expensive operations like sorting, joining, or complex filtering, which can slow performance even if the index is used. Rows scanned (B) is correct because scanning a large number of rows, even with a secondary index, suggests the index is not selective enough or the query is retrieving many rows, leading to excessive I/O and latency. Both metrics directly point to query execution inefficiency.

Answer

Storage utilization

Answer

Commit latency

Answer

Lock wait time

Question 6

Your Cloud SQL for PostgreSQL instance is experiencing high CPU utilization during peak hours. You notice that the query `SELECT * FROM orders WHERE order_date >= '2024-01-01'` is frequently run against a table with 10 million rows. The table has a B-tree index on `order_date`. What is the most likely cause of the high CPU usage, and how should you address it?

Accepted Answer

The query retrieves all columns, causing significant heap lookup overhead; rewrite the query to select only required columns.. Option C is correct because the query uses `SELECT *`, which forces PostgreSQL to fetch all columns from the heap (the main table storage) even though the index on `order_date` can efficiently locate the matching rows. This results in significant heap lookup (also known as bitmap heap scan or index scan with tuple retrieval) overhead, consuming CPU cycles for each row fetched. Reducing the selected columns to only those needed minimizes I/O and CPU usage by avoiding unnecessary data retrieval from the heap.

Answer

The index is fragmented; rebuild the index to improve performance.

Answer

Increase the instance machine type to provide more CPU capacity.

Answer

The index on `order_date` is not being used; add a hint to force index usage.

Question 7

You are managing a Cloud SQL for MySQL instance that is experiencing high replication lag. The instance uses semi-synchronous replication. Which THREE actions could reduce the replication lag?

Accepted Answer

Change replication mode from semi-synchronous to asynchronous.. Option A is correct because switching from semi-synchronous to asynchronous replication removes the requirement for the primary to wait for at least one replica to acknowledge receipt of each transaction. In semi-synchronous mode, the primary waits for acknowledgment, which can introduce latency and contribute to replication lag under high write loads. Asynchronous replication allows the primary to commit transactions immediately without waiting, reducing the time the primary spends waiting for replica acknowledgment and thus lowering replication lag.

Answer

Reduce the binlog retention period on the primary.

Answer

Enable binary log compression on the primary.

Question 8

A company is migrating its on-premises PostgreSQL database to Cloud SQL for PostgreSQL. They want to minimize downtime during the migration. Which TWO actions should they take?

Accepted Answer

Set up a Cloud SQL read replica and promote it to the primary after migration.. Option C is correct because setting up a Cloud SQL read replica from the source PostgreSQL database and then promoting it to primary allows for a controlled cutover with minimal downtime. The replica stays in sync with the source using PostgreSQL's native streaming replication, and promotion is a fast metadata operation that typically takes seconds, not minutes or hours.

Answer

Increase the disk size of the Cloud SQL instance before migration to improve performance.

Answer

Use pg_dump to export the database and pg_restore to import into Cloud SQL.

Answer

Decrease max_connections to reduce load during migration.

Question 9

A retail company uses BigQuery to analyze sales data. They need to create a weekly report showing total sales per product category for the last 4 weeks, but the query is taking too long and exceeding slot resources. The sales table has over 2 billion rows and is partitioned by date. Which design change would most improve query performance and reduce slot consumption?

Accepted Answer

Cluster the table by product_category within the existing date partitions.. Option B is correct because clustering the table by product_category within the existing date partitions organizes the data physically so that queries filtering or grouping by product_category can skip irrelevant blocks. This reduces the amount of data scanned and the slot consumption, directly addressing the performance issue without requiring additional resources.

Answer

Increase the number of available slots in the reservation.

Answer

Create a materialized view that pre-aggregates sales by category and date.

Answer

Partition the table by product_category instead of date.

Question 10

Your company is migrating a 2 TB SQL Server database to Cloud SQL. You need to choose a migration approach that minimizes downtime and supports ongoing changes. Which TWO options meet these requirements?

Accepted Answer

Use Database Migration Service with a source of SQL Server.. Database Migration Service (DMS) supports continuous change data capture (CDC) for SQL Server, enabling near-zero-downtime migrations by replicating ongoing changes from the source to Cloud SQL until cutover. This approach minimizes downtime and keeps the database synchronized during the migration window, meeting both requirements.

Answer

Export the database as a .bak file and restore to Cloud SQL.

Answer

Use BCP to export data and import via gcloud sql import.

Answer

Migrate to Azure SQL Managed Instance first, then to Cloud SQL.