PCDE Practice Test 18 — 15 Questions

Question 1

A Cloud SQL for SQL Server instance has been running for months. Recently, the database size grew significantly and now query performance has degraded. The DBA checks the query execution plan and sees index scans. The current storage is 500GB SSD. What is the most likely cause and solution?

Accepted Answer

Index fragmentation; rebuild or reorganize indexes. The correct answer is B. Index fragmentation occurs over time as data is inserted, updated, or deleted, causing indexes to become inefficient. The query execution plan showing index scans (instead of seeks) is a classic symptom of fragmented indexes. Rebuilding or reorganizing the indexes will defragment them, restoring query performance without requiring additional storage or infrastructure changes.

Answer

Increase storage capacity to 1TB SSD

Answer

Enable query insights to analyze performance

Answer

Enable read replicas to offload queries

Question 2

Which tool is best for identifying hot spots in a Cloud Spanner database?

Accepted Answer

Key Visualizer. Key Visualizer is the correct tool because it is specifically designed to visualize access patterns in Cloud Spanner and identify hot spots—keys or ranges that receive a disproportionate share of reads or writes. Unlike generic monitoring tools, Key Visualizer provides a heatmap of key-space activity, enabling you to pinpoint and mitigate performance bottlenecks caused by uneven distribution of workload across splits.

Answer

Query Insights

Answer

Cloud Trace

Answer

Cloud Monitoring

Question 3

Refer to the exhibit. A company has a Cloud Spanner instance with the backup configuration shown. They need to improve disaster recovery. Which THREE strategies should they implement?

Accepted Answer

Enable point-in-time recovery (PITR). Option B is correct because enabling point-in-time recovery (PITR) in Cloud Spanner allows you to recover data to any point within the retention period (default 7 days), which is essential for granular disaster recovery against logical errors or accidental data changes. This complements backup strategies by providing fine-grained restore capabilities beyond full backups.

Answer

Schedule regular exports to Cloud Storage

Answer

Use read replicas in another region

Question 4

A Cloud Spanner database is experiencing high CPU utilization and latency. The workload is read-heavy with occasional writes. Which TWO actions would most effectively improve performance?

Accepted Answer

Create a secondary index on frequently queried columns.. Creating a secondary index on frequently queried columns allows Cloud Spanner to serve read queries directly from the index without scanning the full base table, reducing CPU usage and latency. This is especially effective in read-heavy workloads because it minimizes the number of rows that must be processed per query.

Answer

Use a smaller instance configuration to reduce cost.

Answer

Use a split point to distribute hot rows.

Answer

Enable interleaved tables to reduce joins.

Question 5

A Cloud SQL for PostgreSQL instance is experiencing high CPU usage during peak hours. Query Insights shows that a complex reporting query is causing full table scans on a large table. The query filters on a column used in JOINs. Which optimization should be applied first?

Accepted Answer

Add a composite index on the filtered column and join columns.. Option D is correct because adding a composite index on the filtered column and the join columns directly addresses the root cause of the full table scans. Query Insights indicates the query filters on a column used in JOINs; a composite index covering both the filter and join columns allows PostgreSQL to perform an Index Scan instead of a sequential scan, reducing CPU usage without requiring additional infrastructure or data duplication.

Answer

Increase the instance size.

Answer

Create a read replica for reporting.

Answer

Use query rewriting with materialized views.

Question 6

Which TWO metrics should you monitor in Cloud Monitoring to evaluate the performance of a Cloud Spanner instance? (Choose two.)

Accepted Answer

Commit latency. Commit latency is a critical metric for Cloud Spanner because it directly measures the time taken to commit a transaction, which reflects the database's ability to handle write operations efficiently. High commit latency can indicate contention, node overload, or suboptimal schema design, making it essential for performance evaluation.

Answer

Row reads per second

Answer

Connection count

Answer

Disk IOPS

Question 7

A Bigtable instance is running out of storage and performance is degraded. The schema design is known to be efficient. Which THREE actions can help?

Accepted Answer

Delete unused column families.. Deleting unused column families is correct because each column family in Bigtable stores data in separate SSTable files, and unused families consume storage and memory resources without providing value. Removing them frees up space and reduces the amount of data that must be scanned during reads and compactions, directly improving performance.

Answer

Reduce the number of tablets.

Answer

Add SSDs.

Question 8

Your application uses Firestore for real-time updates. You notice increasing read latency during peak hours. The database is in Native mode with a single-location (us-central1). After reviewing metrics, you see that the number of document reads has not changed significantly, but the database size has grown. What is the most likely cause and solution?

Accepted Answer

Review and create composite indexes for common query patterns.. The correct answer is D because as the database size grows, Firestore's query performance can degrade if queries rely on automatic index scanning without composite indexes. Composite indexes allow Firestore to serve queries without scanning all documents, reducing read latency. The unchanged read count but increased latency indicates that queries are scanning more data due to missing indexes.

Answer

Enable multi-region replication to distribute read traffic.

Answer

The database needs to be defragmented periodically; run a compaction command.

Answer

Migrate the database to Datastore mode for better performance.

Question 9

A Cloud Spanner database has a table with a primary key (UserId, Timestamp). Queries that filter by Timestamp range for a specific UserId are fast, but queries that filter only by Timestamp range across all users are slow. Which TWO improvements would help?

Accepted Answer

Use a leading column of the primary key that supports range scans.. Option A is correct because in Cloud Spanner, the primary key order determines how data is physically sorted and stored. By making Timestamp the leading column of the primary key (e.g., (Timestamp, UserId)), range scans on Timestamp become efficient as Spanner can perform a contiguous scan of the sorted data. This directly addresses the slow queries that filter only by Timestamp range across all users.

Answer

Use a hash prefix on UserId.

Answer

Create an interleaved table structure.

Answer

Partition the table by Timestamp.

Question 10

A Cloud SQL instance is using InnoDB and has a large buffer pool. The query performance is slower after a failover. What is the most likely cause?

Accepted Answer

Buffer pool warm-up time. After a failover in Cloud SQL, the new primary instance starts with a cold buffer pool. InnoDB relies on the buffer pool to cache data and index pages in memory for fast queries. Since the buffer pool is empty after the failover, queries must read from disk until the cache warms up, causing significantly slower performance.

Answer

Read replica lag

Answer

Binary log not enabled

Answer

Data corruption

Question 11

A team notices that queries on a Cloud Spanner database are slow. They want to identify which queries are consuming the most resources. What should they use?

Accepted Answer

Query Insights. Query Insights is the correct tool because it is specifically designed for Cloud Spanner to analyze query performance, providing detailed metrics such as execution latency, CPU usage, and rows scanned per query. It helps identify the most resource-intensive queries by breaking down performance by query fingerprint, allowing the team to pinpoint and optimize slow queries directly.

Answer

Cloud Logging

Answer

Performance Dashboard

Answer

Cloud Monitoring metrics

Question 12

You are designing a Cloud Spanner schema for a global social media application. The application reads the most recent 100 posts for a user's timeline. The Posts table has a primary key of (UserId, PostTimestamp DESC). You observe that queries for the timeline are hitting high read latency and the transaction abort rate is increasing. What is the most likely cause?

Accepted Answer

The descending timestamp in the primary key is causing hotspotting on the tablet leader for the most recent data.. The descending timestamp in the primary key causes all writes for the most recent posts to be concentrated on the same tablet leader, because Cloud Spanner uses the first primary key column for splitting and distributing data. This hotspotting leads to high read latency and increased transaction abort rates as the single leader becomes overloaded with both writes and reads for the latest data.

Answer

The instance has too few nodes to handle the write volume.

Answer

The lack of a secondary index on UserId is forcing full table scans.

Answer

The table should be interleaved under a Users table.

Question 13

Refer to the exhibit. What is the most effective optimization for this query?

Accepted Answer

Create a composite index on (status, order_date). The query filters on `status` and `order_date`, so a composite index on `(status, order_date)` allows the database to perform an index seek on the equality predicate (`status`) and then a range scan on the ordered column (`order_date`), avoiding a full table scan. This is the most effective optimization because it directly supports the WHERE clause with minimal I/O and no sorting overhead.

Answer

Increase the instance memory to 30 GB

Answer

Remove the WHERE clause and fetch all rows in application

Answer

Partition the orders table by month

Question 14

You are managing a Memorystore for Redis cluster with standard tier (persistence disabled). The application experiences occasional latency spikes while performing SET operations. You observe that the 'evicted_keys' metric spikes during the spikes. What is the most effective solution?

Accepted Answer

Increase the maximum memory size of the instance. The evicted_keys metric spikes during SET operations indicate that the Redis instance has reached its maxmemory limit and is evicting keys to accommodate new writes. Increasing the maximum memory size directly addresses the root cause by providing more headroom for data, reducing the need for eviction and the associated latency spikes.

Answer

Enable AOF persistence with fsync every second

Answer

Change the maxmemory-policy to 'volatile-lru'

Answer

Configure a read replica to offload read traffic

Question 15

You are a cloud database engineer for a financial services firm. The firm uses Cloud SQL for PostgreSQL to support a BI reporting tool. The main table 'transactions' has 500 million rows and is growing daily. Reports often run aggregations over date ranges and group by account_id. The 'transactions' table has indexes on date and account_id separately. Despite these indexes, the reporting queries are slow, often taking over 30 minutes. The database is deployed on a high-memory machine with 32 vCPUs and 256 GB RAM. You notice that the queries perform sequential scans instead of using indexes. What is the most likely reason, and what single change would you make to improve performance?

Accepted Answer

Create a composite index on (date, account_id). The correct answer is B because the reporting queries filter by date ranges and group by account_id, but the existing separate indexes on date and account_id cannot be combined efficiently for both conditions. PostgreSQL's query planner often chooses a sequential scan over using two separate indexes because it estimates that reading the entire table is cheaper than the bitmap scan overhead of combining them. A composite index on (date, account_id) allows the database to directly locate rows matching the date range and then access them in account_id order, eliminating the need for a separate sort or join step.

Answer

Partition the table by date using PostgreSQL declarative partitioning

Answer

Increase the shared_buffers setting to 128 GB

Answer

Disable sequential scans by setting enable_seqscan = off