Courseiva

CCNA Db Performance Questions

75 of 102 questions · Page 1/2 · Db Performance topic · Answers revealed

Practice these questionsExam hubAll questions
1
Multi-Selecthard

You manage a Cloud SQL for PostgreSQL instance that is experiencing high read latency. You have already tuned the buffer cache and queries. Which THREE actions can further reduce read latency? (Choose three.)

Select 3 answers
A.Enable the PostgreSQL slow query log and analyze it.
B.Use connection pooling to reduce the number of open connections.
C.Increase the number of vCPUs on the primary instance.
D.Create read replicas in the same region to distribute read traffic.
E.Add a Memorystore for Redis cache in front of the database for frequently accessed data.
AnswersC, D, E

More vCPUs can process more queries concurrently, reducing queue time.

Why this answer

Increasing the number of vCPUs on the primary instance (Option C) can reduce read latency by providing more CPU resources to process queries, especially if the current bottleneck is CPU-bound operations like query parsing, sorting, or aggregation. In Cloud SQL for PostgreSQL, scaling vCPUs also increases available memory and I/O bandwidth proportionally, which can alleviate contention and improve throughput for read-heavy workloads.

Exam trap

Candidates often mistakenly select diagnostic actions (like enabling logs) as a direct solution for latency reduction, but the question asks for actions that can directly reduce read latency.

Practice this question →
2
Multi-Selectmedium

Which TWO actions can help reduce the number of read replicas needed for a Cloud SQL for PostgreSQL instance that serves a read-heavy workload?

Select 2 answers
A.Implement connection pooling to reuse database connections.
B.Enable synchronous replication on all read replicas.
C.Use smaller machine types for read replicas.
D.Use application-level caching (e.g., Redis) to cache frequent read results.
E.Increase the max_connections parameter on the primary instance.
AnswersA, D

Reduces connection overhead and improves replica efficiency.

Why this answer

Connection pooling reduces the overhead of establishing new database connections, which can consume significant CPU and memory resources on the primary instance. By reusing existing connections, the primary instance can handle more read requests without needing additional read replicas to offload the connection management load. This directly reduces the number of replicas required for a read-heavy workload.

Exam trap

Google Cloud often tests the misconception that increasing database parameters like max_connections or using synchronous replication directly reduces read replica requirements, when in fact these actions either increase resource consumption or do not address read offloading.

Practice this question →
3
Multi-Selectmedium

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

Select 2 answers
A.Row reads per second
B.Commit latency
C.Connection count
D.Disk IOPS
E.CPU utilization per node
AnswersB, E

Indicates write performance.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that throughput metrics like row reads per second or disk-level metrics like IOPS are meaningful for evaluating performance in a fully managed, distributed database like Cloud Spanner, where internal optimizations and abstractions make such metrics irrelevant.

Practice this question →
4
MCQeasy

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?

A.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.
B.Rewrite the dashboard queries to use fewer joins and aggregations.
C.Increase the total number of slots to 2000 to provide more capacity for all queries.
D.Schedule the dashboard refresh to run at a different time when ad-hoc usage is low.
AnswerA

Dedicated slots guarantee resources for the dashboard regardless of other jobs.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that simply adding more resources (slots) or rewriting queries is the best solution, when in fact proper resource governance through reservations and priority queues is the most effective and scalable approach for mixed workloads.

How to eliminate wrong answers

Option B is wrong because rewriting queries to use fewer joins and aggregations might reduce complexity but does not guarantee performance improvements if the underlying slot contention is the root cause; it also requires significant development effort and may not fully resolve timeouts under high concurrency. Option C is wrong because simply increasing total slots to 2000 does not prioritize the dashboard queries; ad-hoc queries could still consume all slots, leading to the same contention and timeout issue. Option D is wrong because scheduling the dashboard refresh at a different time only avoids the conflict temporarily and does not solve the fundamental slot contention problem; it also may not be feasible if the dashboard requires hourly updates.

Practice this question →
5
MCQeasy

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

A.Query Insights
B.Key Visualizer
C.Cloud Trace
D.Cloud Monitoring
AnswerB

Key Visualizer provides heatmaps of key access patterns, helping identify hot spots.

Why this answer

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.

Exam trap

Google Cloud often tests the distinction between performance monitoring tools by presenting Cloud Monitoring or Query Insights as plausible answers, but the trap is that candidates overlook Key Visualizer's unique purpose of visualizing key-space access patterns specifically for Cloud Spanner hot spot detection.

How to eliminate wrong answers

Option A is wrong because Query Insights focuses on analyzing query performance, such as latency and execution plans, not on visualizing key-level access patterns to detect hot spots. Option C is wrong because Cloud Trace is a distributed tracing tool for latency analysis of requests across services, not for identifying hot keys in a database. Option D is wrong because Cloud Monitoring provides metrics and alerting for overall system health and performance, but lacks the key-space heatmap visualization required to pinpoint hot spots in Cloud Spanner.

Practice this question →
6
MCQeasy

You are using Cloud Memorystore for Redis as a caching layer. You notice that cache hit ratio is below 50%. What is the best action to improve it?

A.Flush the cache periodically to remove stale data.
B.Increase the TTL (time-to-live) for cached data.
C.Enable persistence to avoid data loss.
D.Increase the instance memory size.
AnswerB

Longer TTL keeps data in cache for more reads.

Why this answer

A low cache hit ratio indicates that a large proportion of requests are not finding their data in the cache, forcing the application to fetch from the primary database. Increasing the TTL (time-to-live) for cached data keeps valid entries in Redis longer, reducing the frequency of evictions and cache misses. This directly improves the hit ratio by ensuring that more requests can be served from the cache before the data expires.

Exam trap

Google Cloud often tests the misconception that a low cache hit ratio is always a memory capacity problem, leading candidates to choose 'increase memory size' when the real issue is data expiring too quickly due to short TTLs.

How to eliminate wrong answers

Option A is wrong because flushing the cache periodically removes all data, which would drastically reduce the hit ratio and increase load on the database, the opposite of the desired effect. Option C is wrong because enabling persistence (e.g., RDB snapshots or AOF logs) protects against data loss on restart but does not influence how long data remains in the cache or the hit ratio. Option D is wrong because increasing instance memory size only delays evictions under the maxmemory-policy; if the TTL is too short, data still expires quickly and the hit ratio remains low regardless of memory size.

Practice this question →
7
Multi-Selecteasy

Which TWO are best practices for optimizing write performance in Cloud Bigtable?

Select 2 answers
A.Use short row keys to reduce storage size
B.Group multiple mutations into a single request
C.Design row keys to distribute writes across tablets
D.Use the Dataflow Bulk Import API for real-time writes
E.Increase replication lag to allow more time for writes
AnswersB, C

Batching reduces overhead.

Why this answer

Bigtable batches mutations into a single RPC request, reducing network round trips and improving throughput. Sending individual mutations incurs per-request overhead, so grouping them into a single atomic or non-atomic batch (via `MutateRows` or client-side batching) significantly increases write throughput.

Exam trap

Google Cloud often tests the misconception that short row keys are a primary optimization for write performance, when in reality row key distribution to avoid hotspots is far more critical for throughput.

Practice this question →
8
MCQhard

Your Cloud SQL for MySQL instance is experiencing intermittent performance degradation. You suspect that the issue is due to a sudden spike in connections from a specific application. Which metric and monitoring approach would best help you correlate the connection spike with performance degradation?

A.Monitor 'cloudsql.googleapis.com/network/received_bytes_count' and compare with connection count.
B.Monitor 'cloudsql.googleapis.com/database/mysql/replication/seconds_behind_master' and compare with query latency.
C.Monitor 'cloudsql.googleapis.com/instance/uptime' and check for instance restarts during degradation.
D.Monitor 'cloudsql.googleapis.com/database/mysql/threads/threads_connected' and correlate with CPU utilization and query latency.
AnswerD

Threads connected directly indicates active connections, and correlating with CPU and latency helps identify the impact.

Why this answer

The 'threads_connected' metric directly measures the number of active connections to the MySQL instance. Correlating this with CPU utilization and query latency allows you to pinpoint whether a sudden spike in connections is causing resource contention and degraded query performance, which is the exact scenario described.

Exam trap

The trap here is that candidates may confuse network metrics (like cloudsql.googleapis.com/network/received_bytes_count) or replication metrics with direct indicators of connection-related performance issues, rather than focusing on the thread count (threads_connected) and its impact on CPU and query latency.

How to eliminate wrong answers

Option A is wrong because 'received_bytes_count' measures network throughput, not connection count; a spike in bytes could be due to large queries or data transfers, not necessarily a connection spike. Option B is wrong because 'seconds_behind_master' is a replication lag metric relevant only for read replicas, not for correlating connection spikes with performance degradation on the primary instance. Option C is wrong because 'instance/uptime' only indicates restarts, which are not directly caused by connection spikes; performance degradation can occur without any instance restart.

Practice this question →
9
MCQeasy

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?

A.Query Insights
B.Cloud Logging
C.Performance Dashboard
D.Cloud Monitoring metrics
AnswerA

Query Insights is the dedicated tool for analyzing Cloud Spanner query performance.

Why this answer

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.

Exam trap

Google Cloud often tests the distinction between high-level monitoring tools (Cloud Monitoring, Performance Dashboard) and query-specific diagnostic tools (Query Insights), trapping candidates who confuse general performance metrics with per-query resource analysis.

How to eliminate wrong answers

Option B is wrong because Cloud Logging captures raw log entries and events but does not provide aggregated query-level performance metrics or resource consumption analysis for Cloud Spanner. Option C is wrong because the Performance Dashboard in Google Cloud Console offers a high-level overview of database metrics like latency and throughput, but it lacks the per-query breakdown and resource attribution needed to identify specific resource-heavy queries. Option D is wrong because Cloud Monitoring metrics provide system-level metrics (e.g., CPU utilization, storage) but do not offer query-level insights or the ability to sort and analyze individual query performance.

Practice this question →
10
MCQhard

An e-commerce platform uses Cloud Bigtable for real-time user sessions. Write latency is high. On investigation, they find that rows are being written with monotonically increasing row keys (e.g., user_id + timestamp). What is the likely cause and solution?

A.Too many column families; merge them
B.Inefficient reads; use reverse scan
C.Hotspotting on a single node; use salting or field promotion
D.Tablet splits are misconfigured; pre-split the table
AnswerC

Salting or field promotion spreads writes across tablets.

Why this answer

Monotonically increasing row keys (e.g., user_id + timestamp) cause all writes to target a single tablet server, creating a hotspot. Cloud Bigtable distributes writes across nodes by row key range; sequential keys concentrate load on one node, degrading write latency. Salting (prepending a hash or random prefix) or field promotion (using a high-cardinality field as the first part of the key) spreads writes evenly across the cluster.

Exam trap

Google Cloud often tests the misconception that pre-splitting alone solves hotspotting, but the trap here is that monotonically increasing keys will still cause writes to concentrate on the last tablet regardless of pre-splitting, requiring key design changes like salting.

How to eliminate wrong answers

Option A is wrong because too many column families do not cause write hotspotting; they affect storage and read performance, not write distribution. Option B is wrong because inefficient reads (e.g., full scans) are a read-side issue, not a cause of high write latency; reverse scan is a read optimization, not a write fix. Option D is wrong because misconfigured tablet splits or pre-splitting addresses initial distribution, but the root cause here is the key design pattern, not split configuration; even with pre-splits, monotonically increasing keys will still hotspot writes to the last tablet.

Practice this question →
11
Multi-Selectmedium

You are a Cloud Database Engineer managing a Cloud Spanner instance. You notice that some queries are taking longer than expected. You suspect that the queries are not using secondary indexes efficiently. Which TWO metrics should you monitor in Cloud Monitoring to validate your suspicion? (Choose two.)

Select 2 answers
A.CPU utilization
B.Statement scan rows returned
C.Lock conflicts
D.Row count returned by index
E.Query scan latency (mean)
AnswersB, D

High scan rows vs. rows returned indicates inefficient index usage.

Why this answer

'Statement scan rows returned' measures the number of rows scanned by a query, which directly indicates whether a full table scan is occurring instead of an efficient index seek. If this metric is high relative to the rows returned, it confirms that secondary indexes are not being used effectively, leading to longer query times.

Exam trap

The trap here is that candidates often confuse general performance metrics like CPU utilization or latency with index-specific metrics, failing to realize that only row-level scan and return metrics directly reveal index usage efficiency.

Practice this question →
12
Multi-Selecthard

Which THREE are best practices for designing a Cloud Spanner schema for high performance? (Choose three.)

Select 3 answers
A.Spread data across multiple regions to reduce latency.
B.Avoid using a monotonically increasing primary key as the first part of the key.
C.Denormalize frequently joined tables into a single table.
D.Use interleaved tables for tables that are always accessed together by the parent key.
E.Use secondary indexes when querying by non-key columns.
AnswersB, D, E

This prevents write hotspotting.

Why this answer

A monotonically increasing primary key (e.g., an auto-increment integer or timestamp) creates a hot spot on the last tablet server, causing all writes to be serialized on a single split. Cloud Spanner distributes splits based on the primary key range; a sequential key forces all new inserts into the same split, leading to write contention and poor throughput. Using a hash prefix or a UUID-like key spreads writes evenly across splits, maximizing parallelism.

Option A is wrong because spreading data across multiple regions does not inherently reduce latency; it may increase latency due to geographic distance. Cloud Spanner automatically replicates data across regions for high availability, and best practices recommend placing instances close to users for lower latency.

Option C is wrong because denormalization is generally not recommended in Cloud Spanner. Instead, use interleaved tables to efficiently join parent-child tables without data redundancy. Denormalization can lead to increased storage and update anomalies.

Option D is correct because interleaved tables allow parent and child rows to be stored together, providing efficient access when querying by the parent key. This reduces the need for joins and improves performance.

Option E is correct because secondary indexes are necessary for efficient queries on non-key columns. Without an index, Cloud Spanner would need to perform a full table scan, which is slow for large tables.

Exam trap

Google Cloud often tests the misconception that denormalization is always beneficial for performance, but in Cloud Spanner, interleaved tables provide efficient parent-child joins without the downsides of denormalization.

Practice this question →
13
MCQmedium

A Cloud Spanner database contains the Orders table as defined above. The query `SELECT * FROM Orders WHERE CustomerID=123` takes a long time. What is the most likely reason?

A.The ORDER BY clause is missing.
B.Interleaving causes extra I/O.
C.The primary key is not optimized for this query.
D.The table needs a secondary index on CustomerID.
AnswerD

A secondary index on CustomerID enables direct lookup without scanning the entire table.

Why this answer

The query `SELECT * FROM Orders WHERE CustomerID=123` filters on the `CustomerID` column, but the primary key of the Orders table is likely defined on a different column (e.g., `OrderID`). Without a secondary index on `CustomerID`, Cloud Spanner must perform a full table scan to find matching rows, which is slow for large tables. Creating a secondary index on `CustomerID` allows Cloud Spanner to directly locate the relevant splits and rows, dramatically reducing latency.

Exam trap

The trap here is that candidates often assume a primary key is always the best way to query any column, but Cloud Spanner requires the query predicate to match the primary key order for efficient access; otherwise, a secondary index is necessary.

How to eliminate wrong answers

Option A is wrong because the absence of an ORDER BY clause does not cause a query to take a long time; it merely affects the order of results, not the scan method. Option B is wrong because interleaving (table interleaving in Cloud Spanner) is a design pattern that can improve performance by co-locating parent and child rows; it does not inherently cause extra I/O and is not relevant to a simple filter on a non-key column. Option C is wrong because the primary key is already defined; the issue is that the query predicate does not match the primary key order, so the primary key cannot be used efficiently for this filter.

Practice this question →
14
Multi-Selecteasy

Which TWO actions would help optimize a Cloud SQL for PostgreSQL database experiencing high read latency?

Select 2 answers
A.Increase the number of read replicas
B.Add indexes on frequently queried columns
C.Increase database tier machine type
D.Configure automatic storage increase
E.Use pgBouncer connection pooling
AnswersB, C

Adding indexes reduces full table scans and speeds up query execution, directly addressing high read latency.

Why this answer

Adding indexes on frequently queried columns (B) reduces full table scans, and increasing the database tier machine type (C) provides more CPU/memory for query processing. Read replicas (A) distribute load but do not reduce individual query latency; connection pooling (E) helps connection management, not read latency; automatic storage increase (D) is irrelevant.

Practice this question →
15
Multi-Selectmedium

A company is experiencing slow query performance in Cloud SQL for PostgreSQL. Which TWO tools can help identify the root cause?

Select 2 answers
A.Cloud Monitoring
B.Query Insights
C.Cloud Logging with error reporting
D.Cloud Profiler
E.Cloud Trace
AnswersA, B

Cloud Monitoring shows instance-level resource metrics that can indicate bottlenecks.

Why this answer

Cloud Monitoring provides metrics and dashboards to track database performance indicators like CPU utilization, memory usage, disk I/O, and query latency, helping identify resource bottlenecks. Query Insights offers detailed query-level diagnostics, including execution plans, lock contention, and slow query analysis, directly pinpointing problematic SQL statements in Cloud SQL for PostgreSQL.

Exam trap

The trap here is that candidates often confuse Cloud Logging’s error reporting with performance diagnostics, or assume Cloud Profiler and Cloud Trace can analyze database internals, when in fact they are application-layer tools not designed for PostgreSQL query tuning.

Practice this question →
16
MCQmedium

You are managing a Spanner instance for a global financial application. The database has a table `transactions` with columns `transaction_id` (INT64), `user_id` (INT64), `amount` (FLOAT64), `timestamp` (TIMESTAMP), and `region` (STRING). The table is interleaved with a parent table `users`. Recently, you observed that point-read queries by `transaction_id` are taking over 100ms on average, whereas they used to take under 10ms. The instance CPU utilization is below 40%, and there are no contention issues. The `transactions` table has a primary key `(user_id, transaction_id)`. Queries filter on `transaction_id` only, without specifying `user_id`. Which optimization should you implement to improve point-read latency?

A.Add a secondary index on `user_id` to help narrow down the search.
B.Create a secondary index on `transaction_id` to enable efficient key-based lookups.
C.Use a Spanner query hint to force a specific index scan.
D.Change the primary key to `(transaction_id, user_id)` to enable direct access by transaction_id.
AnswerB

A secondary index on `transaction_id` provides a direct lookup path, reducing latency.

Why this answer

Point-read queries by `transaction_id` are slow because the primary key is `(user_id, transaction_id)`, so without `user_id`, Spanner cannot directly locate the split (tablet) and must perform a full table scan or a less efficient lookup. Creating a secondary index on `transaction_id` allows Spanner to use that index for key-based lookups, reducing latency to under 10ms by enabling direct access to the specific split via the index's key.

Exam trap

Google Cloud often tests the misconception that changing the primary key is the only way to optimize queries that don't use the full primary key, but in Spanner, secondary indexes are the correct and efficient solution without disrupting existing interleaved table relationships.

How to eliminate wrong answers

Option A is wrong because adding a secondary index on `user_id` does not help queries that filter only on `transaction_id`; it would only be useful if queries filtered on `user_id` alone or in combination with `transaction_id`. Option C is wrong because a query hint to force a specific index scan is unnecessary and ineffective if no suitable index exists; the hint cannot create an index that doesn't exist, and without an index on `transaction_id`, Spanner would still perform a full scan. Option D is wrong because changing the primary key to `(transaction_id, user_id)` would require a costly schema change and data migration, and it would break the interleaved table structure with the parent `users` table, which expects `user_id` as the first part of the primary key for interleaving.

Practice this question →
17
Multi-Selectmedium

You are monitoring a Cloud Spanner instance that is experiencing high CPU utilization (consistently above 70%). You want to identify the root cause. Which TWO metrics should you examine first? (Choose two.)

Select 2 answers
A.Average commit latency
B.Read and write throughput (operations/second)
C.Lock wait time
D.Stale read rate
E.Number of nodes
AnswersA, B

High commit latency can indicate contention, increasing CPU.

Why this answer

Examining read and write throughput helps identify if the workload is pushing the instance. Analyzing commit latency and lock wait time reveals contention. Stale reads show replica lag but are not primary indicators of high CPU.

Node count is configuration, not utilization.

Practice this question →
18
MCQmedium

Your Cloud Spanner instance has several tables with interleaved parent-child relationships. You notice that queries that join parent and child tables are slow. What is the best practice to optimize these joins?

A.Ensure the tables are defined as interleaved with the parent key as the first part of the child primary key
B.Create secondary indexes on the join columns
C.Use batch update operations to reduce round trips
D.Remove interleaving and use a separate JOIN statement
AnswerA

Interleaving enables efficient distributed joins without cross-node communication.

Why this answer

Cloud Spanner optimizes interleaved table joins by physically co-locating parent and child rows on the same split, based on the parent key as the prefix of the child's primary key. This eliminates the need for distributed cross-split joins, dramatically reducing latency. Queries that join on the interleaved key benefit from local data access, making them fast and efficient.

Exam trap

The trap here is that candidates often assume secondary indexes are the universal solution for join performance, but in Cloud Spanner, physical data co-location via interleaving is the critical optimization for parent-child joins, not indexing alone.

How to eliminate wrong answers

Option B is wrong because secondary indexes on join columns do not change the physical co-location of parent and child rows; they only provide an alternative access path, and queries may still require distributed joins across splits, which is the root cause of slowness. Option C is wrong because batch update operations reduce round trips for writes, not for read-heavy join queries; they do not address the physical data layout needed for efficient joins. Option D is wrong because removing interleaving would break the physical co-location guarantee, forcing Spanner to perform distributed cross-split joins, which would make queries even slower, not faster.

Practice this question →
19
MCQeasy

A bigquery job is running slower than expected. Checking the job information, you see that the slot usage is at 100% for the entire duration of the query. You are using on-demand pricing. What is the most effective way to improve query performance?

A.Create materialized views for common aggregations.
B.Purchase a slot reservation and assign the project to it.
C.Cluster the tables on frequently filtered columns.
D.Partition the tables by date.
AnswerB

Reservations provide dedicated slots, allowing queries to use more resources and run faster.

Why this answer

With on-demand pricing, your query is limited to the default per-project slot capacity (typically 2,000 slots in BigQuery). If slot usage is at 100% for the entire duration, the query is resource-constrained and cannot be sped up without additional slots. Purchasing a slot reservation and assigning the project to it provides dedicated slots, eliminating the contention and allowing the query to run faster.

Exam trap

Google Cloud often tests the misconception that performance issues are always solved by data organization techniques (partitioning/clustering) or precomputation (materialized views), when in fact the bottleneck is compute capacity (slots) under on-demand pricing.

How to eliminate wrong answers

Option A is wrong because materialized views reduce the amount of data scanned and recomputation for repeated aggregations, but they do not increase the available slot capacity; if the query is already hitting 100% slot usage, the bottleneck is compute resources, not data volume. Option C is wrong because clustering improves data pruning and scan efficiency for filtered queries, but it does not add more slots; the query will still be throttled by the fixed slot pool. Option D is wrong because partitioning reduces the amount of data read by date range filters, but like clustering, it does not address the root cause of slot exhaustion; the query will still run at the same slot limit.

Practice this question →
20
MCQmedium

What is the most likely cause of the high execution time?

A.The WHERE clause uses a string comparison
B.Missing index on status column
C.The query selects two columns causing inefficiency
D.The instance has insufficient nodes
E.The query does not filter on primary key
AnswerB

An index on status would allow efficient row retrieval instead of a full table scan.

Why this answer

The execution plan shows a full table scan. Since the query filters on the status column, an index on that column would allow direct lookup instead of scanning all rows. The high execution time is most likely due to the missing index on the status column.

Therefore, option B is correct. Options A, C, D, and E are not the primary cause: string comparison (A) does not inherently cause high execution time; selecting two columns (C) is not inefficient; insufficient instance nodes (D) would affect overall performance but not specifically this query; not filtering on primary key (E) is not a problem if other columns are indexed appropriately.

Practice this question →
21
MCQmedium

Your Cloud SQL for PostgreSQL instance is experiencing intermittent slowdowns during peak hours. You notice that the CPU utilization spikes to 80% and the number of connections increases. The application team confirms they are not running any new queries. What should you do first to diagnose the issue?

A.Increase the machine type of the Cloud SQL instance to add more CPU.
B.Enable connection pooling to reduce the number of connections.
C.Use Cloud SQL Insights to analyze query performance and wait statistics.
D.Set a maximum connection limit and reduce the connection lifetime.
AnswerC

Query Insights helps identify high CPU queries, wait events, and performance trends.

Why this answer

Cloud SQL Insights provides built-in query performance monitoring and wait statistics that can pinpoint the root cause of intermittent slowdowns without making changes. Since CPU spikes and increased connections are symptoms, not the cause, analyzing wait events (e.g., CPU, IO, lock contention) directly reveals which queries or resources are bottlenecked. This is the first diagnostic step before any scaling or configuration changes.

Exam trap

Google Cloud often tests the principle that you must diagnose before scaling; the trap here is that candidates jump to scaling or connection limits (A, B, D) because they seem like immediate fixes, but the correct first step is always to use monitoring tools like Cloud SQL Insights to identify the actual bottleneck.

How to eliminate wrong answers

Option A is wrong because increasing the machine type adds cost and masks the underlying issue without diagnosing why CPU spikes occur; it treats a symptom, not the cause. Option B is wrong because enabling connection pooling reduces connection overhead but does not address CPU spikes or query performance; it may even hide connection-related issues without solving the root cause. Option D is wrong because setting a maximum connection limit or reducing connection lifetime can cause application errors or dropped connections without identifying why connections are increasing or why CPU is spiking.

Practice this question →
22
MCQhard

Your Cloud SQL for SQL Server instance has a query that uses a non-clustered index to filter rows but then performs key lookups to retrieve additional columns. The query is slow. Which database tuning option would most likely reduce I/O?

A.Increase the buffer pool size
B.Rebuild the non-clustered index with FILLFACTOR=80
C.Create a covering index that includes all columns referenced in the query
D.Use a FORCESEEK query hint
AnswerC

Covering index avoids the need for lookups, reducing I/O.

Why this answer

The query is slow because key lookups require random I/O to retrieve additional columns not included in the non-clustered index. Creating a covering index that includes all columns referenced in the query eliminates the need for key lookups entirely, converting the operation into a single index seek or scan with minimal I/O. This directly reduces the number of page reads and improves query performance.

Exam trap

Google Cloud often tests the misconception that any index tuning or query hint can fix performance, but the trap here is that candidates may choose FORCESEEK or FILLFACTOR without realizing that only a covering index directly addresses the root cause of key lookup I/O.

How to eliminate wrong answers

Option A is wrong because increasing the buffer pool size only caches more data in memory, which may reduce physical I/O but does not eliminate the logical I/O caused by key lookups; the query still performs the same number of page accesses. Option B is wrong because rebuilding the index with FILLFACTOR=80 reduces page splits and fragmentation but does not change the index structure to include additional columns, so key lookups still occur. Option D is wrong because using a FORCESEEK query hint forces the optimizer to use an index seek, but it does not prevent key lookups if the index does not cover all required columns; it may even degrade performance by forcing a suboptimal plan.

Practice this question →
23
MCQeasy

A developer has deployed a new version of an application that uses Cloud SQL. After the deployment, you notice a sharp increase in the number of slow queries. What should you do first to identify the problematic queries?

A.Check the slow query log in Cloud Logging and look for queries with high rows_examined.
B.Use Cloud SQL Query Insights to identify the queries with the highest latency and examine their execution plans.
C.Increase the instance tier to reduce the impact of slow queries.
D.Enable the general query log and parse the log file to find slow queries.
AnswerB

Query Insights provides detailed query performance data without additional overhead.

Why this answer

Cloud SQL Query Insights is the recommended first step for diagnosing slow queries because it provides built-in query monitoring, latency breakdowns, and execution plans without additional configuration. It directly surfaces the queries with the highest latency, allowing you to examine their execution plans to identify root causes such as missing indexes or inefficient joins.

Exam trap

Google Cloud often tests the distinction between reactive scaling (Option C) and proactive diagnostics (Option B), trapping candidates who think adding resources is the first troubleshooting step instead of identifying the root cause.

How to eliminate wrong answers

Option A is wrong because the slow query log in Cloud Logging requires manual filtering and may not be enabled by default, whereas Query Insights provides immediate, structured visibility into high-latency queries. Option C is wrong because increasing the instance tier only masks the symptom by adding more resources, without identifying or fixing the underlying problematic queries. Option D is wrong because enabling the general query log generates excessive volume and performance overhead, and parsing it manually is inefficient compared to using Query Insights' built-in analysis.

Practice this question →
24
MCQeasy

A Cloud Spanner database is experiencing high latency for point reads. The table has a primary key of (CustomerID, OrderDate). Most reads are by CustomerID only. What should the engineer do?

A.Add a secondary index on CustomerID.
B.Use interleaved tables.
C.Reorder primary key to (OrderDate, CustomerID).
D.Increase the number of nodes.
AnswerA

A secondary index allows direct lookup by CustomerID, significantly reducing read latency.

Why this answer

Point reads by CustomerID only are inefficient on the primary key (CustomerID, OrderDate) because Cloud Spanner requires the full primary key for direct lookup. Adding a secondary index on CustomerID allows Spanner to perform an index scan followed by a point read, drastically reducing latency for these queries.

Exam trap

The trap here is that candidates assume reordering the primary key (Option C) is a valid optimization, but Cloud Spanner's design requires the full primary key for efficient point reads, and changing the key order does not eliminate the need for a secondary index when filtering on a prefix alone.

How to eliminate wrong answers

Option B is wrong because interleaved tables optimize joins and hierarchical data access, not point reads by a non-primary-key column. Option C is wrong because reordering the primary key to (OrderDate, CustomerID) would break existing data distribution and still not optimize reads by CustomerID alone, as OrderDate would still be required for efficient lookups. Option D is wrong because increasing nodes improves throughput and capacity, not the fundamental latency of point reads caused by missing index support.

Practice this question →
25
MCQmedium

A company has a Cloud SQL for PostgreSQL instance that experiences high CPU usage during peak hours due to read-heavy queries. Which optimization is most effective for reducing CPU load?

A.Use connection pooling
B.Increase memory size
C.Add read replicas
D.Enable automatic storage increase
AnswerC

Read replicas distribute read queries, reducing CPU on the primary instance.

Why this answer

Adding read replicas offloads read-heavy queries from the primary Cloud SQL for PostgreSQL instance, distributing the query load and reducing CPU utilization on the primary. This is the most direct and effective optimization for read-heavy workloads because replicas handle SELECT traffic while the primary focuses on writes and critical operations.

Exam trap

Google Cloud often tests the misconception that connection pooling or memory increases are universal performance fixes, but for read-heavy CPU spikes, offloading reads to replicas is the targeted solution.

How to eliminate wrong answers

Option A is wrong because connection pooling reduces the overhead of establishing new database connections, but it does not reduce the CPU cost of executing the read-heavy queries themselves; the same number of queries still run on the same instance. Option B is wrong because increasing memory size can improve cache hit ratios and reduce disk I/O, but it does not directly lower CPU usage from query execution; CPU-bound workloads are not resolved by adding memory. Option D is wrong because automatic storage increase only prevents out-of-disk errors by expanding disk capacity; it has no effect on CPU utilization or query processing load.

Practice this question →
26
MCQmedium

You manage a Cloud SQL for PostgreSQL instance that handles OLTP workloads. Users in a different region report slow query response times. You notice that the database CPU utilization is below 30%, but network latency is high. What is the most cost-effective solution to reduce query latency without migrating the database?

A.Add more memory to the instance to increase cache hit ratio.
B.Increase the instance's vCPUs to handle more concurrent connections.
C.Create cross-region read replicas and route read queries to the nearest replica.
D.Migrate the database to Cloud Spanner using a live migration service.
AnswerC

Read replicas reduce the network distance for read traffic, improving latency without moving the primary database.

Why this answer

The issue is high network latency for users in a different region, not local resource contention. Creating cross-region read replicas allows read queries to be served from a replica closer to the users, reducing network round-trip time without migrating the database. This is the most cost-effective solution as it avoids expensive instance upgrades or a full migration to Cloud Spanner.

Exam trap

The trap here is that candidates often focus on scaling the instance (CPU or memory) when the symptom is high latency, but the root cause is geographic distance, not resource exhaustion.

How to eliminate wrong answers

Option A is wrong because adding more memory to increase the cache hit ratio addresses local cache misses, not high network latency; CPU utilization is below 30%, indicating no memory pressure. Option B is wrong because increasing vCPUs handles more concurrent connections, but the problem is network latency, not CPU or connection bottlenecks. Option D is wrong because migrating to Cloud Spanner is a costly and complex operation that involves changing the database paradigm from relational to globally distributed, which is overkill for a simple latency issue that can be solved with read replicas.

Practice this question →
27
MCQeasy

Based on the exhibit from Cloud Spanner Query Insights, what is the most likely performance issue?

A.High network latency
B.Full table scan
C.Inefficient join
D.No index on customer_id
AnswerD

Missing index causes a full scan of the Orders table.

Why this answer

The exhibit shows a query with a filter on `customer_id` that is not indexed, forcing Cloud Spanner to perform a full table scan to find matching rows. This is the most likely performance issue, as indicated by high latency and high row scan counts in Query Insights, which directly points to a missing index on the filtered column.

Exam trap

Google Cloud often tests the distinction between a symptom (full table scan) and its root cause (missing index), tricking candidates into selecting the visible effect rather than the underlying configuration issue.

How to eliminate wrong answers

Option A is wrong because high network latency would manifest as increased client-side wait times and not as high row scan counts or CPU usage within the Spanner backend; Query Insights metrics focus on database-side execution, not network round trips. Option B is wrong because a full table scan is a symptom, not the root cause—the underlying reason for the full scan is the missing index on customer_id, making B a description of the effect rather than the most likely performance issue. Option C is wrong because an inefficient join would show high join-related metrics like rows returned from join operations or skewed distribution, but the exhibit does not indicate any join operations; the query appears to be a simple filter on a single table.

Practice this question →
28
MCQhard

Your team uses Cloud Bigtable for a time-series data analytics platform. You observe that the write throughput has dropped significantly, and Cloud Monitoring shows that most of the CPU usage is concentrated on a few nodes. The remaining nodes have low CPU usage. The data model uses sequential timestamps as row keys, and the application writes data for many different sensors. Each sensor ID is part of the row key. What is the most effective action to resolve this hot spotting?

A.Reduce the batch size of writes to decrease the load on each node.
B.Use a different Bigtable cluster and migrate data.
C.Increase the number of nodes in the cluster to provide more CPU capacity.
D.Prepend a hash of the sensor ID to the row key to distribute writes evenly.
AnswerD

This breaks the sequential key pattern and distributes writes across all nodes, eliminating hot spotting.

Why this answer

Hot spotting occurs because sequential timestamps as row keys cause writes to be concentrated on a few nodes. By prepending a hash of the sensor ID to the row key, writes for different sensors are distributed across the entire cluster, eliminating hot spots. Option A (reducing batch size) does not fix the key design issue.

Option B (increasing nodes) might help temporarily but the uneven distribution remains. Option C (using a different cluster) is unnecessary and does not address the root cause.

Practice this question →
29
MCQmedium

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?

A.Enable multi-region replication to distribute read traffic.
B.The database needs to be defragmented periodically; run a compaction command.
C.Migrate the database to Datastore mode for better performance.
D.Review and create composite indexes for common query patterns.
AnswerD

Missing indexes cause full scans, increasing latency as data grows.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that database growth always requires scaling or replication, when in fact the root cause is often missing composite indexes that force full scans, especially in Firestore's automatic indexing model.

How to eliminate wrong answers

Option A is wrong because multi-region replication improves availability and latency for global reads, but the database is single-location (us-central1) and read count hasn't changed; the issue is query efficiency, not geographic distribution. Option B is wrong because Firestore is a NoSQL document database that does not require defragmentation or compaction; such operations are for traditional relational databases or storage engines like LevelDB. Option C is wrong because Datastore mode is a legacy mode with different consistency and scaling characteristics; migrating would not resolve latency caused by missing composite indexes and could introduce compatibility issues.

Practice this question →
30
Multi-Selecthard

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?

Select 2 answers
A.Use a leading column of the primary key that supports range scans.
B.Use a hash prefix on UserId.
C.Create an interleaved table structure.
D.Add a secondary index on Timestamp.
E.Partition the table by Timestamp.
AnswersA, D

Redesigning the primary key with Timestamp as the first column allows efficient range scans across users.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that adding a secondary index is always the best solution, but here both a leading key column change and a secondary index are valid; the trap is that candidates might think partitioning (Option E) is supported in Spanner when it is not.

Practice this question →
31
Multi-Selecteasy

Which THREE metrics from Cloud Monitoring are important for monitoring Cloud Bigtable performance?

Select 3 answers
A.Storage utilization
B.CPU utilization
C.Latency (P99)
D.Request count
E.Disk usage
AnswersB, C, D

High CPU indicates nodes are busy processing requests.

Why this answer

CPU utilization (option B) is a critical metric for Cloud Bigtable because it directly reflects the processing load on the cluster's nodes. High CPU utilization indicates that the cluster is approaching its throughput limits, which can lead to increased latency and throttling. Monitoring this metric helps in scaling decisions, such as adding nodes or optimizing queries, to maintain performance.

Exam trap

The trap here is that candidates often confuse storage-related metrics (like disk usage or storage utilization) with performance metrics, but Cloud Bigtable abstracts storage management, making CPU, latency, and request count the direct indicators of performance health.

Practice this question →
32
MCQmedium

A Firestore database is used for a social app. A collection of posts has indexes on fields `author` and `timestamp`. The query `where author == 'user1' order by timestamp desc limit 10` is performing a large number of document reads. What is the likely cause?

A.The limit is too high.
B.The query is scanning all posts.
C.Index on timestamp is not descending.
D.Missing composite index on (author, timestamp).
AnswerD

A composite index covers both the filter and sort, avoiding large scans.

Why this answer

The query filters on `author` and orders by `timestamp`, which requires a composite index on `(author, timestamp)` to avoid a full scan. Without this composite index, Firestore must scan all documents matching `author == 'user1'` (or all posts if no single-field index on `author` is used) and then sort them in memory, leading to excessive document reads. The existing single-field indexes on `author` and `timestamp` are insufficient for this combined filter and sort operation.

Exam trap

Google Cloud often tests the misconception that single-field indexes are sufficient for combined filter and order queries, when in fact Firestore requires a composite index to avoid scanning all matching documents.

How to eliminate wrong answers

Option A is wrong because a limit of 10 is not inherently too high; the excessive reads are due to the lack of a composite index, not the limit value. Option B is wrong because the query is not scanning all posts if a single-field index on `author` exists, but it still reads all documents for that author and sorts in memory, which is inefficient. Option C is wrong because the index on `timestamp` does not need to be descending; Firestore can reverse the sort order at query time as long as a composite index on `(author, timestamp)` exists, and the issue is the missing composite index, not the direction of the single-field index.

Practice this question →
33
MCQmedium

Refer to the exhibit. You receive an alert from this policy for a Cloud Spanner instance. Which action should you take first?

A.Identify and remove unused indexes
B.Add more nodes to the instance
C.Review the top queries by CPU usage in the Spanner console
D.Split large tables into smaller ones
AnswerB

Directly reduces per-node CPU utilization.

Why this answer

The alert from the policy indicates that the Cloud Spanner instance is experiencing high CPU utilization, which is a sign of resource saturation. Adding more nodes increases the total compute and I/O capacity of the instance, directly alleviating the CPU bottleneck and improving throughput. This is the first and most immediate corrective action because Spanner's performance scales linearly with nodes, and other optimizations (like index or query tuning) are secondary to ensuring sufficient capacity.

Exam trap

Google PCDE often tests the misconception that query optimization or schema changes are the first step to resolve performance alerts, when in fact capacity scaling (adding nodes) is the immediate corrective action for resource saturation in a managed service like Cloud Spanner.

How to eliminate wrong answers

Option A is wrong because removing unused indexes can reduce storage and write overhead, but it does not directly address high CPU usage caused by insufficient node capacity; indexes are a query optimization tool, not a scaling solution. Option C is wrong because reviewing top queries by CPU usage is a diagnostic step that helps identify inefficient queries, but it is not the first action to take when the instance is already saturated—capacity must be added first to prevent further performance degradation. Option D is wrong because splitting large tables into smaller ones can improve query performance and reduce hotspots, but it does not increase the overall CPU capacity of the instance; node scaling is the primary mechanism for handling sustained high CPU load.

Practice this question →
34
MCQhard

Your company runs a global gaming platform using Cloud Spanner as the backend database. The platform has millions of users who play concurrently. You receive reports that during peak hours (7-10 PM UTC), some users experience 'DEADLINE_EXCEEDED' errors and high latency on write operations. You have already verified that there are no hot keys and that the schema uses primary keys with hash prefixes. Monitoring shows CPU utilization averages 60% but spikes to 80% during the peak. The average commit latency is 50ms during peak, and the transaction rate is 10,000 writes per second. The instance currently has 100 nodes. The application team indicates that writes are primarily player score updates. What should you do to resolve the performance issue?

A.Enable Fine-Grained Latency & Replication (FLLR) to improve write latency.
B.Disable client-side buffering for write operations.
C.Increase the number of Spanner nodes to 150.
D.Reduce the size of write transactions by batching fewer mutations per transaction.
AnswerC

More nodes add capacity, reducing CPU pressure and latency.

Why this answer

Increasing the number of Spanner nodes from 100 to 150 directly adds more compute and storage capacity, reducing CPU utilization from the 80% spike and lowering write latency. The 60-80% CPU range with 50ms commit latency indicates the instance is nearing its throughput limit, and adding nodes distributes the write load (10,000 writes/sec) more evenly, alleviating 'DEADLINE_EXCEEDED' errors without requiring schema or application changes.

Exam trap

Google Cloud often tests the misconception that reducing transaction size or disabling buffering always improves performance, but in Spanner, CPU saturation from high write throughput is best resolved by horizontal scaling (adding nodes), not by reducing batch sizes or tweaking client settings.

How to eliminate wrong answers

Option A is wrong because Fine-Grained Latency & Replication (FLLR) is a feature for reducing read latency by placing replicas closer to users, not for improving write throughput or CPU-bound write latency; writes still require a quorum across all replicas. Option B is wrong because disabling client-side buffering would increase the number of round trips and likely worsen latency, as buffering helps batch writes and reduce overhead; the issue is server-side CPU saturation, not client-side batching. Option D is wrong because reducing transaction size by batching fewer mutations per transaction would increase the total number of transactions, potentially raising CPU overhead and commit latency further, and the current 50ms commit latency is already high for small score updates.

Practice this question →
35
MCQhard

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?

A.Enable AOF persistence with fsync every second
B.Change the maxmemory-policy to 'volatile-lru'
C.Increase the maximum memory size of the instance
D.Configure a read replica to offload read traffic
AnswerC

More memory reduces evictions, stabilizing write latency.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that changing the eviction policy (Option B) solves memory pressure, when in fact the policy only controls which keys are evicted, not whether eviction occurs at all.

How to eliminate wrong answers

Option A is wrong because enabling AOF persistence with fsync every second adds disk I/O overhead, which can increase latency rather than reduce it, and does not address the memory pressure causing evictions. Option B is wrong because changing the maxmemory-policy to 'volatile-lru' only affects which keys are evicted (those with TTL set), but does not prevent evictions from occurring when memory is full; the problem is insufficient memory, not the eviction policy. Option D is wrong because configuring a read replica offloads read traffic, but the latency spikes occur during SET (write) operations, and replicas do not handle writes; this does not reduce memory pressure on the primary instance.

Practice this question →
36
MCQhard

You are running a Cloud Bigtable instance for time-series data ingestion. Write throughput has dropped significantly, and you see an increase in 'resource exhausted' errors. The table has one column family and one rowkey format: `#orgId#deviceId#timestamp`. After analyzing cluster metrics, you see that one node is handling most of the traffic. What is the most likely cause?

A.The column family design is creating too many columns.
B.The rowkey design is causing hotspotting on one tablet node due to a prefix (orgId) being written heavily.
C.The cluster does not have enough nodes.
D.The timestamp is too granular, causing many rows with the same timestamp.
AnswerB

If one orgId dominates writes, all writes go to a single tablet.

Why this answer

The rowkey design `#orgId#deviceId#timestamp` causes hotspotting because all writes for a given `orgId` are directed to a single tablet node. Cloud Bigtable partitions data by rowkey range, and sequential or heavily skewed prefixes (like `orgId`) concentrate write traffic on one node, leading to 'resource exhausted' errors and throughput degradation.

Exam trap

Google Cloud often tests the misconception that scaling nodes (Option C) solves performance issues, but the real problem is rowkey design causing uneven load distribution, which cannot be fixed by adding nodes alone.

How to eliminate wrong answers

Option A is wrong because the number of columns in a column family does not cause hotspotting or node-level traffic imbalance; column family design affects storage and read patterns, not write distribution. Option C is wrong because adding more nodes would not fix the root cause—hotspotting—since all writes for the same `orgId` would still target the same node regardless of cluster size. Option D is wrong because timestamp granularity affects row uniqueness, not write distribution; multiple rows with the same timestamp do not cause one node to handle most traffic.

Practice this question →
37
Multi-Selecthard

Which THREE actions can help reduce read latency in Cloud Spanner?

Select 3 answers
A.Use read-only transactions with strong consistency
B.Increase the staleness allowed for read queries
C.Structure tables with interleaved parent-child relationships
D.Use secondary indexes to avoid full table scans
E.Batch multiple write operations into a single mutation
AnswersA, C, D

Read-only transactions can execute faster without locks.

Why this answer

Read-only transactions with strong consistency in Cloud Spanner use lock-free reads that return the most recent data without blocking writes. This reduces read latency because the system can serve the data directly from the current timestamp without waiting for write locks or replication delays, making it ideal for low-latency, strongly consistent reads.

Exam trap

Google Cloud often tests the distinction between read latency and write latency, so candidates may incorrectly choose batching writes (Option E) or increasing staleness (Option B) as read latency reducers, when in fact those affect write performance or trade off consistency for speed.

Practice this question →
38
Drag & Dropmedium

Order the steps to set up a Cloud Spanner instance with a global database.

Drag steps to the numbered slots on the right, or tap a step then tap a slot.

Steps
Order
1Step 1
2Step 2
3Step 3
4Step 4

Why this order

The correct sequence for setting up Cloud Spanner starts with creating the instance to provide compute and storage resources, then creating the database within that instance, followed by defining the schema (tables and indexes), inserting data, and finally configuring IAM to manage access. This logical progression ensures that each step builds on the previous one, avoiding dependency errors.

Practice this question →
39
MCQmedium

You are tuning a Cloud SQL for PostgreSQL instance that runs reporting queries. The slowest query performs a full table scan on a 100 GB table. Which action is most likely to improve performance?

A.Create an index on the columns used in the WHERE clause
B.Increase shared_buffers to 50% of instance memory
C.Set statement_timeout to 30 seconds
D.Move the query to a read replica
AnswerA

An index allows the database to locate rows quickly.

Why this answer

A full table scan on a 100 GB table indicates that PostgreSQL has no efficient access path to retrieve the required rows. Creating an index on the columns used in the WHERE clause allows the query planner to use an index scan instead of a sequential scan, drastically reducing the number of disk pages read and improving query performance. This is the most direct and effective optimization for a query that filters rows without an index.

Exam trap

Google Cloud often tests the misconception that simply adding more memory or offloading work to a replica will fix performance issues caused by missing indexes, but the root cause—lack of an access path—must be addressed directly.

How to eliminate wrong answers

Option B is wrong because increasing shared_buffers to 50% of instance memory can cause excessive memory consumption, leading to increased checkpoint I/O and potential out-of-memory errors; PostgreSQL recommends shared_buffers be set to 25% of total memory, not 50%. Option C is wrong because setting statement_timeout to 30 seconds does not improve performance—it only aborts the query if it exceeds that duration, which would cause the query to fail rather than run faster. Option D is wrong because moving the query to a read replica does not eliminate the full table scan; the replica still performs the same sequential scan on the same large table, so performance remains poor.

Practice this question →
40
MCQhard

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?

A.The descending timestamp in the primary key is causing hotspotting on the tablet leader for the most recent data.
B.The instance has too few nodes to handle the write volume.
C.The lack of a secondary index on UserId is forcing full table scans.
D.The table should be interleaved under a Users table.
AnswerA

Frequent inserts to the same split (latest timestamp) cause contention.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that adding more nodes or secondary indexes solves all performance problems, when in fact the root cause is often a poorly designed primary key that creates hotspotting in Cloud Spanner's distributed architecture.

How to eliminate wrong answers

Option B is wrong because while insufficient nodes can cause performance issues, the specific symptom of high read latency combined with increasing transaction abort rates points to hotspotting from the primary key design, not a general capacity problem. Option C is wrong because the primary key already includes UserId as the first column, so queries filtering by UserId can use the primary key directly without needing a secondary index. Option D is wrong because interleaving the Posts table under a Users table would not solve the hotspotting issue caused by the descending timestamp; it would actually exacerbate the problem by colocating all posts for a user on the same split.

Practice this question →
41
Multi-Selecthard

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?

Select 3 answers
A.Change replication mode from semi-synchronous to asynchronous.
B.Reduce the binlog retention period on the primary.
C.Configure the replica to use parallel replication (slave_parallel_workers > 0).
D.Increase the machine type of the replica.
E.Enable binary log compression on the primary.
AnswersA, C, D

Async replication removes the acknowledgement wait, reducing lag.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that reducing log retention or compressing logs directly reduces replication lag, when in fact these actions affect storage or network transfer, not the apply speed or acknowledgment delay that cause lag.

Practice this question →
42
MCQeasy

You are using Cloud Monitoring to track performance of a Cloud Spanner instance. Which metric indicates that a database is approaching its throughput limits and may need a split or additional nodes?

A.Storage utilization (bytes used)
B.Query error rate (errors per second)
C.High CPU utilization (percentage)
D.Average commit latency
AnswerC

CPU utilization above 65% suggests the instance is nearing its throughput limit.

Why this answer

High CPU utilization (percentage) is the correct metric because Cloud Spanner's CPU utilization directly reflects the processing load on the instance's nodes. When CPU utilization consistently exceeds 65-70%, it indicates the database is approaching its throughput limits, often requiring a split (to distribute load across more tablets) or additional nodes to maintain performance and avoid throttling.

Exam trap

Google Cloud often tests the misconception that storage utilization or latency metrics indicate throughput limits, but the correct answer is CPU utilization because it directly measures the processing capacity headroom in Cloud Spanner's node-based architecture.

How to eliminate wrong answers

Option A is wrong because storage utilization measures data volume, not throughput capacity; a database can have low storage but still hit throughput limits due to high read/write operations. Option B is wrong because query error rate indicates failures (e.g., due to deadlocks or timeouts), but it is a lagging indicator of throughput saturation, not a direct measure of approaching limits. Option D is wrong because average commit latency can increase due to many factors (e.g., network latency, contention) and is not a primary metric for throughput capacity; Spanner's key metric for node saturation is CPU utilization.

Practice this question →
43
MCQeasy

Your Cloud SQL for MySQL instance is experiencing unusually high disk usage. You need to identify the cause. Which metric should you monitor in Cloud Monitoring?

A.InnoDB row reads
B.CPU utilization
C.Query latency
D.Binary log disk usage
AnswerD

Binary logs can consume significant disk space.

Why this answer

Binary logs in MySQL store all data changes (e.g., INSERT, UPDATE, DELETE) and can consume significant disk space, especially under heavy write workloads or if retention is misconfigured. Monitoring 'Binary log disk usage' directly reveals whether accumulated binary logs are the primary cause of high disk usage, which is a common issue in Cloud SQL for MySQL.

Exam trap

The trap here is that candidates may confuse performance metrics (like CPU or latency) with storage metrics, overlooking the direct disk space impact of binary logs in MySQL replication or backup configurations.

How to eliminate wrong answers

Option A is wrong because InnoDB row reads measure the number of rows read from the InnoDB storage engine, which indicates query activity but does not directly correlate with disk space consumption. Option B is wrong because CPU utilization reflects processing load, not storage usage; high CPU could be a symptom of inefficient queries but does not explain disk usage. Option C is wrong because query latency measures the time taken to execute queries, which can be affected by disk I/O but does not identify the specific cause of high disk usage.

Practice this question →
44
Multi-Selecteasy

Which TWO actions can help reduce connection overhead in a Cloud SQL for MySQL instance? (Choose two.)

Select 2 answers
A.Disable SSL encryption for database connections.
B.Enable automatic connection management in the application driver.
C.Use Cloud SQL Proxy or a connection pooler.
D.Increase the max_connections parameter.
E.Add a read replica to handle connect requests.
AnswersB, C

Automatic pooling reduces connection creation.

Why this answer

Enabling automatic connection management in the application driver (e.g., MySQL Connector/J with autoReconnect=true or HikariCP) allows the driver to reuse idle connections and transparently recover from broken connections, reducing the overhead of repeatedly establishing new TCP connections. Option C is correct because Cloud SQL Proxy or a connection pooler (like pgBouncer or ProxySQL) maintains a persistent pool of connections to the database, amortizing the cost of connection setup across multiple client requests and reducing the total number of concurrent connections to the instance.

Exam trap

The trap here is that candidates often confuse increasing max_connections (a capacity setting) with reducing connection overhead, or they mistakenly think disabling SSL reduces overhead significantly, when in reality the overhead of SSL is minor compared to the cost of establishing a new connection from scratch.

Practice this question →
45
MCQhard

Your Spanner instance is running a workload with high read throughput. You notice that read latency has increased significantly. Upon investigating, you find that the instance is experiencing high CPU utilization on the Spanner nodes. The workload consists of many small point lookups (reads by primary key). Which action is most likely to reduce read latency?

A.Add secondary indexes on the primary key columns.
B.Use stale reads with a timestamp bound to allow reads from replicas.
C.Redesign the schema to use interleaved tables.
D.Reduce the number of Spanner nodes to lower CPU overhead.
AnswerB

Stale reads can be served from any replica, reducing load on the leader and improving latency.

Why this answer

B is correct because stale reads allow Cloud Spanner to serve read requests from read-only replicas, which offloads CPU-intensive processing from the leading replica nodes. By using a timestamp bound (e.g., exact_staleness or max_staleness), the workload can tolerate slightly outdated data, reducing the load on the nodes and lowering read latency for point lookups.

Exam trap

Google Cloud often tests the misconception that reducing nodes or adding indexes always improves performance, but in Spanner, reducing nodes starves CPU capacity and secondary indexes increase write overhead, while stale reads directly offload the leader's CPU.

How to eliminate wrong answers

Option A is wrong because adding secondary indexes on primary key columns does not reduce CPU utilization on Spanner nodes; it adds additional index maintenance overhead and may increase write latency without addressing the high read CPU issue. Option C is wrong because redesigning the schema to use interleaved tables improves locality for parent-child relationships but does not directly reduce CPU load from high-throughput point lookups; it may even increase CPU usage due to more complex splits. Option D is wrong because reducing the number of Spanner nodes decreases total CPU capacity, which would likely increase CPU utilization per node and worsen read latency, not reduce it.

Practice this question →
46
MCQhard

Your Cloud Spanner database is experiencing a high volume of read-write conflicts, causing many aborts and high latency. You have already increased the compute capacity. Upon analyzing the schema, you find that most conflicts occur on a single table with frequent updates to the same row. What index or schema change would most effectively reduce contention?

A.Enable batch writes in the client library to combine updates.
B.Use a monotonically increasing timestamp as a prefix to the primary key.
C.Add a secondary index on the most frequently updated column.
D.Change the primary key to include a hash prefix of the original key.
AnswerD

A hash prefix distributes writes evenly across splits, reducing row-level contention.

Why this answer

Adding a hash prefix to the primary key distributes writes across multiple splits, reducing hot-spotting on a single row. Cloud Spanner uses range-based splits; without a hash prefix, monotonically increasing keys cause all writes to target the same split, leading to read-write conflicts and aborts. A hash prefix randomizes the key distribution, spreading load across nodes and minimizing contention.

Exam trap

Google Cloud often tests the misconception that secondary indexes or batching solve hot-spotting, when the root cause is key distribution; candidates must recognize that only changing the primary key structure (e.g., hash prefix) directly addresses split-level contention.

How to eliminate wrong answers

Option A is wrong because batch writes combine multiple mutations into a single request but do not change the underlying key distribution; if all writes target the same row, batching still causes contention on that row. Option B is wrong because using a monotonically increasing timestamp as a prefix to the primary key exacerbates hot-spotting, as all new writes go to the same split, increasing contention rather than reducing it. Option C is wrong because adding a secondary index on the frequently updated column does not affect the primary key distribution; it may even increase write overhead and contention due to index maintenance.

Practice this question →
47
MCQeasy

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?

A.Enable the query cache flag in Cloud SQL database flags.
B.Increase the instance size to provide more memory and CPU.
C.Add indexes on the columns used in JOIN conditions.
D.Rewrite the query to use subqueries instead of JOINs.
AnswerC

Adding indexes on the columns used in JOIN conditions directly reduces full table scans by allowing the database to use index lookups, which is the most effective and immediate action to improve query performance.

Why this answer

Adding appropriate indexes on the join columns will reduce full table scans, which is the most effective immediate action. Option A (enabling query cache) is not recommended because query cache is deprecated in MySQL 8.0 and does not address the root cause of full table scans. Option B (increasing instance size) may provide more resources but does not fix the inefficient query plan, making it a costly temporary solution.

Option D (rewriting with subqueries) could change the execution plan but is more complex and time-consuming; indexing is the standard first step for JOIN performance.

Practice this question →
48
MCQeasy

A Cloud SQL for MySQL instance is experiencing increased replica lag. The write workload is constant. What is the most likely cause?

A.Binary logs are being deleted too frequently on the primary
B.The query cache is enabled on the primary
C.The replica is on a higher machine tier than the primary
D.A long-running query is executing on the replica
AnswerD

Long queries delay the SQL thread from applying changes.

Why this answer

A long-running query on the replica can block the SQL thread from applying relay log events, causing replica lag to increase even if the primary's write workload is constant. This is because replication is single-threaded by default in MySQL, so one slow query stalls all subsequent events until it completes.

Exam trap

Google Cloud often tests the misconception that replica lag is always caused by primary-side issues (like binary log deletion or query cache), when in fact the replica's own processing bottlenecks are a frequent root cause.

How to eliminate wrong answers

Option A is wrong because deleting binary logs too frequently on the primary does not directly cause replica lag; it can cause replication errors if the replica hasn't yet processed the logs, but lag itself is driven by apply delays, not log retention. Option B is wrong because the query cache is deprecated in MySQL 8.0 and, even when enabled, it caches SELECT results on the primary, not affecting replication lag. Option C is wrong because a higher machine tier on the replica would reduce, not increase, replica lag by providing more resources to apply changes faster.

Practice this question →
49
MCQmedium

Your application uses Cloud SQL for PostgreSQL. You notice that the database CPU utilization has been consistently above 90% during peak hours, causing increased query latency. You have already tuned the most expensive queries. What is the most cost-effective next step?

A.Enable high availability with a failover replica.
B.Implement connection pooling via Cloud SQL Proxy or a dedicated pooler like PgBouncer.
C.Increase the machine type to a higher CPU tier.
D.Add a read replica to offload read queries.
AnswerB

Connection pooling reduces the number of active connections, lowering context switching and CPU overhead.

Why this answer

The question states that expensive queries have already been tuned, yet CPU remains high. This indicates that the database is spending excessive CPU on handling connection overhead (forking processes, parsing sessions) rather than query execution. Connection pooling with PgBouncer reduces the number of active connections, allowing PostgreSQL to reuse backend processes and significantly lower CPU usage without scaling hardware.

Exam trap

The trap here is that candidates often assume high CPU always means underpowered hardware (Option C) or that read replicas (Option D) solve all performance issues, but they overlook the hidden cost of connection overhead in PostgreSQL's process-per-connection model. In Google Cloud exams, be mindful that Cloud SQL for PostgreSQL benefits greatly from connection pooling via PgBouncer when CPU is high despite query optimization.

How to eliminate wrong answers

Option A is wrong because enabling high availability with a failover replica does not reduce CPU utilization on the primary instance; it only provides redundancy in case of failure. Option C is wrong because increasing the machine type to a higher CPU tier is a vertical scaling approach that incurs higher cost without addressing the root cause of connection overhead; it may temporarily mask the issue but is not the most cost-effective step. Option D is wrong because adding a read replica offloads read queries but does not reduce CPU consumption from connection management or write-heavy workloads; the primary instance still handles all writes and connection overhead.

Practice this question →
50
MCQeasy

Your Cloud Spanner instance has high latency for point reads. The workload is evenly distributed across all nodes. Which metric should you examine first to identify the bottleneck?

A.CPU utilization per node
B.Storage utilization
C.Rows returned per second
D.Number of committed nodes
AnswerA

High CPU suggests node is overloaded, causing latency.

Why this answer

High latency for point reads in Cloud Spanner, even with evenly distributed workload, often points to CPU saturation on individual nodes. Spanner uses a shared-nothing architecture where each node handles a portion of the data and queries; if CPU utilization per node is high, it indicates that the node is overloaded, causing queuing and increased latency. This metric directly reflects processing capacity and is the first place to look for a bottleneck in point-read performance.

Exam trap

Google Cloud often tests the misconception that evenly distributed workload means no node-level bottleneck, but the trap here is that even distribution does not guarantee low latency if each node is individually under high CPU load, so candidates incorrectly focus on throughput or storage metrics instead of CPU utilization.

How to eliminate wrong answers

Option B is wrong because storage utilization measures disk space usage, not processing capacity; Spanner automatically manages storage distribution and high storage does not directly cause point-read latency. Option C is wrong because rows returned per second is a throughput metric, not a latency metric; high throughput can coexist with high latency if nodes are overloaded, so it does not identify the bottleneck. Option D is wrong because 'number of committed nodes' is not a standard Spanner metric; Spanner uses a fixed number of nodes provisioned, and committed nodes refer to a different concept (e.g., in Google Cloud commitments), not a real-time performance indicator.

Practice this question →
51
MCQhard

Refer to the exhibit. You restored a Spanner database from a backup and are checking the status of the optimize operation. The operation has been running for 15 minutes and is 45% complete. The database is already accessible but queries on it are slower than expected. What should you do?

A.Continue running queries; performance will improve once the optimize operation completes.
B.Wait for the operation to finish before allowing any queries.
C.Drop and restore the database again to start fresh.
D.Cancel the optimize operation to reduce resource usage.
AnswerA

The optimize operation rebuilds indexes and updates statistics, which improves query performance.

Why this answer

Spanner's optimize operation runs asynchronously and does not block database access. Queries are slower during optimization because the operation reorganizes data and rebuilds indexes, which consumes I/O and CPU resources. Once the optimize operation completes, query performance will improve as the data layout becomes more efficient.

Exam trap

Google Cloud often tests the misconception that database maintenance operations like optimization must complete before the database is usable, but Spanner is designed for continuous availability and allows queries during such operations.

How to eliminate wrong answers

Option B is wrong because Spanner allows queries during an optimize operation; waiting is unnecessary and defeats the purpose of high availability. Option C is wrong because dropping and restoring the database would restart the optimization from scratch, wasting time and resources without any benefit. Option D is wrong because canceling the optimize operation would leave the database in a suboptimal state, and performance would remain degraded until optimization is completed or re-triggered.

Practice this question →
52
MCQeasy

A Firestore database has a collection with a composite index on (status, timestamp desc). The query `where status == 'active' order by timestamp desc limit 50` is returning empty results even though there are active documents. What could be the issue?

A.The status field is not indexed.
B.The composite index exists but the order of timestamp is asc instead of desc.
C.The documents have 'active' status but timestamp field is missing.
D.The query requires an index on timestamp only.
AnswerB

The query requires descending order; using an ascending index can cause the query to miss documents.

Why this answer

If the composite index has timestamp in ascending order, the order by desc cannot use the index efficiently, leading to empty results due to scanning in wrong order.

Practice this question →
53
MCQeasy

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?

A.Read latency.
B.Lock conflicts (e.g., Spanner/API/Lock_wait).
C.Storage utilization.
D.CPU utilization per node.
AnswerB

Lock conflicts directly indicate contention causing commit delays.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that CPU or storage metrics are the first indicators of write performance issues, when in fact lock contention is the primary driver for high commit latency in transactional workloads.

How to eliminate wrong answers

Option A is wrong because read latency measures time for read operations, not write commit latency, and single-row inserts are writes. Option C is wrong because storage utilization affects capacity and cost, not commit latency directly; Spanner handles storage scaling automatically. Option D is wrong because CPU utilization per node indicates compute load but does not directly measure lock contention, which is the primary cause of high commit latency for single-row inserts.

Practice this question →
54
Multi-Selecteasy

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

Select 2 answers
A.INFORMATION_SCHEMA tables (e.g., JOBS_BY_PROJECT)
B.Cloud Monitoring (Metrics like 'bigquery.googleapis.com/scheduler/slot_allocation')
C.Cloud Logging (audit logs)
D.bq command-line tool with the '--format=prettyjson' flag
E.Cloud Profiler
AnswersA, B

INFORMATION_SCHEMA provides historical query performance and slot usage.

Why this answer

INFORMATION_SCHEMA tables like `JOBS_BY_PROJECT` provide detailed metadata about query execution, including slot usage, job timing, and user-level performance metrics. This allows the team to analyze historical query performance and identify inefficiencies directly within BigQuery without external tools.

Exam trap

Candidates often mistakenly choose audit logs for performance data because they conflate 'logging' with 'monitoring'. However, Cloud Logging audit logs focus on administrative activities, not detailed query performance metrics.

Practice this question →
55
MCQhard

A Cloud SQL for MySQL instance is configured with 8 vCPUs and 30GB RAM. The Database Advisor suggests adding an index on a table, but after adding, write performance degrades. The table has 10 million rows and receives 500 writes per second. What is the most likely reason?

A.The instance needs more CPU.
B.The index is too large for memory.
C.The index is not used for reads.
D.The index is on a highly selective column causing write amplification.
AnswerD

High selectivity means many unique values, requiring many index page splits and updates, increasing write latency.

Why this answer

Adding an index on a highly selective column (e.g., a column with many unique values) forces MySQL to update the B-tree index structure on every write operation. With 500 writes per second and 10 million rows, each INSERT or UPDATE must maintain the index, causing write amplification that degrades write throughput. The 8 vCPUs and 30GB RAM are sufficient for the workload, but the index maintenance overhead becomes the bottleneck.

Exam trap

Google Cloud often tests the misconception that write degradation after adding an index is caused by insufficient resources (CPU or memory), when the real issue is the overhead of index maintenance on a highly selective column, known as write amplification.

How to eliminate wrong answers

Option A is wrong because the instance has 8 vCPUs, which is typically adequate for 500 writes per second; the degradation is due to index maintenance, not CPU starvation. Option B is wrong because the index size depends on the column data type and row count, but 30GB RAM is ample for a single index on a 10-million-row table; the issue is write amplification, not memory pressure. Option C is wrong because whether the index is used for reads is irrelevant to write performance degradation; the problem is the overhead of maintaining the index during writes, not its read usage.

Practice this question →
56
Multi-Selecthard

In Cloud Spanner, which TWO strategies can help reduce read latency for globally distributed applications?

Select 2 answers
A.Enable follower reads
B.Use stale reads
C.Use a multi-region configuration with a default leader
D.Use database partitioning
E.Increase the number of nodes
AnswersA, B

Follower reads can serve reads from the nearest replica.

Why this answer

Follower reads allow read requests to be served by any replica in a Cloud Spanner multi-region configuration, not just the leader replica. This reduces read latency by enabling reads to be processed from a replica that is geographically closer to the client, without incurring the round-trip time to the leader. Follower reads are particularly effective for globally distributed applications where read consistency can be slightly relaxed.

Exam trap

Google Cloud often tests the misconception that any multi-region configuration automatically reduces read latency, when in fact you must explicitly enable follower reads or stale reads to avoid routing reads to the leader replica.

Practice this question →
57
MCQmedium

You are running a Memorystore for Redis instance with a high write volume. You notice that the eviction rate is high and the cache hit ratio has dropped significantly. Which configuration change would most directly reduce the eviction rate?

A.Use many small keys instead of a few large keys.
B.Decrease the maxmemory setting to force earlier eviction.
C.Set maxmemory-policy to noeviction.
D.Change maxmemory-policy to allkeys-lru.
AnswerD

LRU eviction removes least recently used keys, which can improve cache efficiency.

Why this answer

Setting `maxmemory-policy` to `allkeys-lru` tells Redis to evict the least recently used keys from the entire keyspace when memory is full. This directly reduces the eviction rate by ensuring that only the least accessed data is removed, preserving the most frequently accessed data and improving the cache hit ratio. In a high-write-volume scenario, this policy adapts to access patterns and minimizes unnecessary evictions of hot data.

Exam trap

The trap here is that candidates often confuse 'reducing eviction rate' with 'preventing eviction entirely' and choose `noeviction`, not realizing that `noeviction` causes write failures instead of reducing evictions, while `allkeys-lru` actively manages memory to keep evictions low by targeting only cold keys. In Google Cloud Memorystore for Redis, `allkeys-lru` is the recommended policy for high-write workloads to maintain a healthy cache hit ratio.

How to eliminate wrong answers

Option A is wrong because using many small keys instead of a few large keys does not reduce the eviction rate; it may actually increase memory overhead due to per-key metadata and does not address the root cause of memory pressure. Option B is wrong because decreasing the `maxmemory` setting forces earlier eviction, which would increase the eviction rate, not reduce it. Option C is wrong because setting `maxmemory-policy` to `noeviction` prevents any eviction, causing write operations to fail with an OOM error when memory is full, which does not reduce the eviction rate but instead breaks the application.

Practice this question →
58
Multi-Selectmedium

A Cloud SQL for PostgreSQL instance is experiencing high replication lag between primary and read replica. Which TWO actions would reduce the lag?

Select 2 answers
A.Reduce the value of `max_wal_size`.
B.Increase the replica's machine size.
C.Place the replica in the same zone as the primary.
D.Use a higher number of read replicas.
E.Enable synchronous replication.
AnswersB, C

More CPU and memory on the replica help it apply WAL more quickly, reducing lag.

Why this answer

Increasing the replica's machine size (Option B) reduces replication lag by providing more CPU and memory resources to apply WAL changes faster. Placing the replica in the same zone as the primary (Option C) minimizes network latency between the instances, which directly reduces the time for WAL data to travel from primary to replica, thus lowering lag.

Exam trap

A common misconception is that reducing `max_wal_size` or adding more replicas will reduce replication lag. In Cloud SQL for PostgreSQL, reducing `max_wal_size` can cause more frequent WAL segment switches, potentially increasing I/O and lag. Adding more replicas does not speed up apply on the existing replica; it may even increase load on the primary.

The correct approaches are to increase replica resources or reduce network latency.

Practice this question →
59
MCQmedium

A DBA notices high query cache lock contention on this Cloud SQL for MySQL instance. Which configuration change should be recommended?

A.Set query_cache_type=0
B.Decrease max_connections
C.Increase innodb_buffer_pool_size
D.Increase query_cache_size
AnswerA

Disabling query cache removes lock contention entirely.

Why this answer

Query cache lock contention occurs when multiple threads try to access the query cache simultaneously, causing serialization. Setting `query_cache_type=0` disables the query cache entirely, eliminating the lock contention. This is the recommended fix because MySQL 8.0 deprecated the query cache due to scalability issues, and Cloud SQL for MySQL instances benefit from removing this bottleneck.

Exam trap

Google Cloud often tests the misconception that increasing the size of a cache or buffer always improves performance, but here increasing `query_cache_size` exacerbates lock contention, making the problem worse.

How to eliminate wrong answers

Option B is wrong because decreasing `max_connections` reduces the number of concurrent connections but does not address the internal locking mechanism of the query cache; contention is a lock-level issue, not a connection-level one. Option C is wrong because increasing `innodb_buffer_pool_size` improves InnoDB data caching and reduces disk I/O, but it does not affect query cache locks, which are a separate memory structure. Option D is wrong because increasing `query_cache_size` only allocates more memory to the query cache, which can actually worsen contention by increasing the time spent scanning or invalidating cache entries under high concurrency.

Practice this question →
60
MCQmedium

Your Bigtable cluster is showing high read latency for row key lookups. The application accesses rows with keys in the format 'user_id#timestamp'. You notice that most reads are for recent timestamps. Which optimization should you implement?

A.Increase the number of nodes in the cluster
B.Reverse the row key order to start with the timestamp
C.Configure single cluster routing to reduce cross-cluster latency
D.Use a scan with a prefix filter instead of point reads
AnswerB

This distributes recent writes/reads across tablet servers.

Why this answer

High read latency for recent timestamps occurs because Bigtable stores rows in lexicographic order by row key. With the format 'user_id#timestamp', older timestamps appear first, causing recent data to be scattered across tablets and requiring more seeks. Reversing the row key to start with the timestamp (e.g., 'timestamp#user_id') groups recent data together in contiguous tablets, enabling faster point lookups and reducing latency.

Exam trap

Google Cloud often tests the misconception that scaling nodes (Option A) solves all performance issues, but the trap here is that row key design directly impacts data locality and latency, making key ordering the primary optimization for time-based access patterns.

How to eliminate wrong answers

Option A is wrong because increasing nodes improves throughput and load distribution but does not address the root cause of scattered recent data due to row key ordering; it may even increase latency due to additional network hops. Option C is wrong because single cluster routing reduces cross-cluster latency in multi-cluster setups, but the question does not mention multiple clusters; the latency issue is within a single cluster due to row key design. Option D is wrong because using a scan with a prefix filter is less efficient than point reads for known row keys; it scans unnecessary rows and increases latency, whereas the goal is to optimize point lookups for recent timestamps.

Practice this question →
61
MCQeasy

You are monitoring a Cloud SQL for PostgreSQL instance and notice that the CPU utilization is consistently above 90% during peak hours. What is the most cost-effective initial action to reduce CPU load?

A.Remove unnecessary indexes to reduce write overhead
B.Reduce the max_connections parameter to limit concurrent users
C.Add a read replica to offload read traffic
D.Increase the machine type (e.g., go to a higher vCPU count)
AnswerC

Read replicas handle read-only queries, reducing primary CPU load.

Why this answer

Adding a read replica is the most cost-effective initial action because it offloads read queries from the primary instance, reducing CPU contention without incurring the cost of upgrading the entire instance. Cloud SQL for PostgreSQL replicas use native streaming replication, so they handle read traffic while the primary focuses on writes, directly addressing high CPU utilization from read-heavy workloads.

Exam trap

Google Cloud often tests the misconception that vertical scaling (increasing machine type) is the default fix for high CPU, but the trap here is that adding a read replica is more cost-effective for read-heavy workloads, as it scales horizontally without upgrading the primary instance.

How to eliminate wrong answers

Option A is wrong because removing indexes typically reduces write overhead but does not significantly lower CPU utilization from read-heavy workloads; indexes often speed up reads, and removing them could increase CPU usage due to full table scans. Option B is wrong because reducing max_connections limits concurrent users but does not address the root cause of high CPU from read queries; it may cause application errors without reducing CPU load if queries are already queued. Option D is wrong because increasing the machine type (higher vCPU count) is a vertical scaling approach that is more expensive than adding a read replica and does not leverage the cost efficiency of distributing read traffic across multiple instances.

Practice this question →
62
MCQmedium

You are a database engineer for an e-commerce platform running on Cloud SQL for PostgreSQL. The application team reports that a critical report query taking 5 seconds last week now takes over 30 seconds. The database CPU usage has increased from 40% to 85%. The query plan shows a sequential scan on the orders table. Which action should you take first to diagnose the problem?

A.Migrate the database to Cloud Spanner for better scaling.
B.Add a composite index on the orders table based on the query's WHERE clause.
C.Increase the machine type to add more vCPUs.
D.Enable pg_stat_statements and check the query's execution statistics.
AnswerD

Provides necessary metrics to diagnose the query performance degradation.

Why this answer

Enabling pg_stat_statements provides detailed execution statistics (e.g., total time, calls, rows, I/O) for each query, allowing you to identify the root cause of the performance regression without making speculative changes. Since the query plan shows a sequential scan and CPU is high, pg_stat_statements can reveal if the query is now reading more rows due to data growth or plan changes, guiding the next action (e.g., adding an index or tuning parameters).

Exam trap

Google Cloud often tests the principle of 'diagnose before you treat' — the trap here is that candidates jump to adding an index (Option B) or scaling resources (Option C) without first gathering evidence, which is a common mistake in performance troubleshooting.

How to eliminate wrong answers

Option A is wrong because migrating to Cloud Spanner is a drastic architectural change that does not diagnose the problem; it introduces new complexity and cost without addressing the immediate cause (e.g., missing index or stale statistics). Option B is wrong because adding a composite index is a potential solution, not a diagnostic step; you must first confirm the query's WHERE clause and execution pattern via pg_stat_statements to avoid creating an ineffective index. Option C is wrong because increasing vCPUs treats the symptom (high CPU) rather than the cause (sequential scan); it may temporarily reduce CPU but does not fix the underlying query performance issue and could be wasteful.

Practice this question →
63
MCQhard

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?

A.Enable the pgzstd extension to compress data in memory.
B.Set up connection pooling using PgBouncer to limit the number of concurrent connections.
C.Scale up the instance to a higher memory tier, such as 8 vCPUs and 30 GB of memory.
D.Decrease the shared_buffers configuration parameter to free memory for other uses.
AnswerC

More memory will accommodate the reporting queries' work_mem and cache needs, reducing out-of-memory errors.

Why this answer

Scaling up the instance to a higher memory tier (8 vCPUs, 30 GB) directly increases available memory, addressing the 'out of memory' errors and high memory usage while maintaining read performance. Option A (pgzstd compression) does not significantly reduce memory consumption; it helps with storage or network I/O. Option B (PgBouncer) reduces memory per connection but does not reduce overall memory needs when the database itself requires more memory for caching and operations.

Option D (decreasing shared_buffers) sacrifices cache performance, likely slowing queries, and may not free enough memory to resolve the issue.

Practice this question →
64
MCQeasy

You are monitoring a Memorystore for Redis instance serving as a cache for an e-commerce application. The cache hit ratio has dropped from 95% to 70%. Which action is most likely to restore the hit ratio?

A.Reduce the network latency between the application and Redis by moving them to the same zone.
B.Change the eviction policy to 'noeviction' to prevent key removal.
C.Increase the maxmemory setting to accommodate more cache entries.
D.Enable AOF persistence to ensure data survives restarts.
AnswerC

Larger memory reduces evictions, improving hit ratio.

Why this answer

A drop in cache hit ratio from 95% to 70% indicates that the cache is evicting frequently accessed keys to make room for new ones. Increasing the maxmemory setting allows Redis to store more entries, reducing evictions and restoring the hit ratio. This is the most direct way to address the capacity issue without changing application behavior.

Exam trap

The trap here is that candidates confuse eviction policy changes (like 'noeviction') with capacity increases, or assume that persistence or network optimization can fix a hit ratio problem caused by insufficient memory.

How to eliminate wrong answers

Option A is wrong because network latency affects response times, not the cache hit ratio; moving to the same zone reduces latency but does not prevent evictions or increase the number of cached keys. Option B is wrong because setting 'noeviction' causes Redis to return errors on write operations when memory is full, which can break the application and does not restore existing evicted keys. Option D is wrong because AOF persistence ensures data durability across restarts but does not affect the eviction behavior or the number of keys in memory; it may even reduce available memory for caching due to overhead.

Practice this question →
65
MCQhard

Refer to the exhibit. You notice replication latency is 15ms. What is the most likely cause of this latency?

A.The table load is not uniform
B.Storage utilization is at 70%
C.High CPU utilization (85%) on the cluster
D.The number of nodes is above the recommended count
AnswerC

High CPU can slow down replication operations.

Why this answer

High CPU utilization (85%) on the cluster is the most likely cause of replication latency because the replication process (often using protocols like Raft or Paxos in distributed databases) is CPU-intensive. When CPU resources are saturated, the cluster cannot process replication requests in a timely manner, leading to increased latency. In many distributed database systems, replication involves serialization, checksumming, and network I/O, all of which compete for CPU cycles.

Exam trap

Google Cloud often tests the misconception that storage utilization or node count are the primary causes of replication latency, when in fact CPU saturation is the most direct bottleneck for the replication protocol's processing pipeline.

How to eliminate wrong answers

Option A is wrong because non-uniform table load typically causes hot spots or uneven data distribution, which can lead to performance degradation but does not directly cause replication latency; replication latency is a cluster-wide issue related to the replication protocol's processing speed. Option B is wrong because storage utilization at 70% is within normal operational limits and does not directly impact replication latency; replication latency is more sensitive to CPU and network bandwidth than to storage capacity. Option D is wrong because the number of nodes being above the recommended count can increase network overhead and coordination delays, but the most direct and common cause of replication latency in a cluster with high CPU is CPU saturation, not node count alone.

Practice this question →
66
MCQhard

A Bigtable instance stores time-series data with row keys formatted as `#deviceID#timestamp`. The application often queries recent data for a specific device. Monitoring shows high read latency when scanning multiple devices. The row key design is causing hotspotting. What is the best redesign?

A.Use separate tables per device.
B.Use a hash prefix before deviceID.
C.Store timestamps in column qualifiers.
D.Prefix the row key with the deviceID and reverse the timestamp.
AnswerB

Hashing distributes rows evenly across the keyspace, alleviating hotspotting while preserving locality for device queries.

Why this answer

Prepending a hash prefix (e.g., a cryptographic hash of the deviceID) to the row key distributes writes and reads evenly across Bigtable tablets, eliminating hotspotting caused by sequential deviceID-based keys. This ensures that queries for recent data (which would otherwise concentrate on a single tablet) are spread across multiple nodes, reducing read latency.

Exam trap

Google Cloud often tests the misconception that reversing the timestamp (option D) solves hotspotting, but candidates fail to realize that the leading key component (deviceID) is still sequential, so all recent data for a device remains on the same tablet, and only a hash prefix (option B) truly distributes the load.

How to eliminate wrong answers

Option A is wrong because using separate tables per device does not solve hotspotting; it merely shifts the problem to table-level contention and increases operational overhead, as Bigtable is optimized for a single wide table, not many small tables. Option C is wrong because storing timestamps in column qualifiers does not address row key hotspotting; it only changes the schema structure without distributing the load, and queries still scan the same hot row key range. Option D is wrong because prefixing with deviceID and reversing the timestamp still results in sequential deviceID-based keys, which cause hotspotting; reversing the timestamp only helps if the deviceID is already distributed, but here the deviceID is the leading component, so all recent data for a device still falls on the same tablet.

Practice this question →
67
MCQhard

A slow query log entry shows the above for a Cloud SQL for MySQL instance. Which index would most improve performance?

A.Index on products(product_id)
B.Index on orders(order_date)
C.Composite index on orders(product_id, order_date)
D.Composite index on orders(order_date, product_id)
AnswerC

This index allows the join to quickly find matching product_ids and then apply the date range, reducing the number of rows examined.

Why this answer

The query likely filters or joins on product_id and then sorts or filters by order_date, so a composite index on orders(product_id, order_date) allows the database to satisfy both conditions with a single index scan, avoiding a filesort or extra lookups. In MySQL, a composite index with the most selective column first (product_id) followed by the range/order column (order_date) is optimal for queries that filter on product_id and then order or filter by order_date.

Exam trap

Google Cloud often tests the leftmost prefix rule and the importance of column order in composite indexes, trapping candidates who think any composite index covering both columns is equally effective regardless of column order.

How to eliminate wrong answers

Option A is wrong because indexing only product_id on the products table does not help with filtering or ordering on the orders table's order_date column, and the query likely involves the orders table. Option B is wrong because indexing only order_date on orders does not help with filtering on product_id, which is typically the more selective filter. Option D is wrong because a composite index on orders(order_date, product_id) would be less efficient if the query filters on product_id first, as MySQL cannot use the second column of the index when the first column is not used in a equality condition, leading to a full index scan or extra sorting.

Practice this question →
68
MCQeasy

Refer to the exhibit. You are analyzing a slow query in Cloud SQL for PostgreSQL. The execution plan shows a sequential scan. Which index should you create to most effectively improve query performance?

A.CREATE INDEX idx_orders_partial ON orders(created_at) WHERE user_id = 123;
B.CREATE INDEX idx_orders_created_at ON orders(created_at);
C.CREATE INDEX idx_orders_created_user ON orders(created_at, user_id);
D.CREATE INDEX idx_orders_user_created ON orders(user_id, created_at);
AnswerD

Allows index seek on user_id then range scan on created_at.

Why this answer

The query likely filters on `user_id` and then sorts or filters by `created_at`. A composite index on `(user_id, created_at)` allows PostgreSQL to first narrow down by `user_id` using index seek, then efficiently access rows in `created_at` order, avoiding a sequential scan. This matches the most common pattern for slow queries involving equality on `user_id` and range or ordering on `created_at`.

Exam trap

Google Cloud often tests the misconception that any composite index with the right columns will work, but the column order matters critically — candidates pick `(created_at, user_id)` thinking it covers both, not realizing the leading column must match the equality filter for optimal performance.

How to eliminate wrong answers

Option A is wrong because a partial index with a hardcoded `user_id = 123` only benefits queries for that specific user, not the general slow query; it also ignores the `created_at` column needed for ordering or filtering. Option B is wrong because an index on `created_at` alone does not help if the query filters on `user_id` first — PostgreSQL may still perform a sequential scan or need to filter many rows. Option C is wrong because the column order `(created_at, user_id)` is suboptimal: if the query filters on `user_id` (equality) and then orders by `created_at`, the leading column should be `user_id` to allow index seek; leading with `created_at` forces a full index scan or inefficient filtering.

Practice this question →
69
Multi-Selectmedium

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?

Select 2 answers
A.CPU time
B.Rows scanned
C.Storage utilization
D.Commit latency
E.Lock wait time
AnswersA, B

High CPU time indicates the query is computationally expensive.

Why this answer

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.

Exam trap

Google Cloud often tests the distinction between metrics that indicate query execution inefficiency (CPU time, rows scanned) versus metrics related to storage or write contention, leading candidates to mistakenly select storage utilization or lock wait time for a read-only query performance issue.

Practice this question →
70
Matchingmedium

Match each Cloud SQL backup type to its retention policy.

Drag a concept onto its matching description — or click a concept then click the description.

Concepts
Matches

Configurable retention up to 365 days

Retained until manually deleted

Retained for point-in-time recovery window

Stored in a different region for disaster recovery

Defined start time and window for automated backups

Why these pairings

Cloud SQL backup types: Automated backups have configurable retention (default 7 days). On-demand backups are retained until deleted (max 365 days). Point-in-time recovery logs are kept for the same period as automated backup retention.

Common confusions involve swapping these policies.

Practice this question →
71
MCQhard

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?

A.The index is fragmented; rebuild the index to improve performance.
B.Increase the instance machine type to provide more CPU capacity.
C.The query retrieves all columns, causing significant heap lookup overhead; rewrite the query to select only required columns.
D.The index on `order_date` is not being used; add a hint to force index usage.
AnswerC

Selecting only needed columns reduces I/O and CPU, as fewer heap lookups are required.

Why this answer

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.

Exam trap

Google Cloud often tests the misconception that high CPU is always due to missing or unused indexes, but here the index is used and the real problem is the overhead of fetching all columns from the heap, which candidates overlook when they focus solely on index usage.

How to eliminate wrong answers

Option A is wrong because B-tree index fragmentation in PostgreSQL is typically not a primary cause of high CPU usage; while index bloat can affect performance, the main issue here is the `SELECT *` causing excessive heap lookups, not index fragmentation. Option B is wrong because increasing the instance machine type treats the symptom (high CPU) rather than the root cause (inefficient query design), and it incurs unnecessary cost without addressing the underlying query pattern. Option D is wrong because the index on `order_date` is very likely being used (PostgreSQL's planner will use it for a range scan on a large table), but the high CPU stems from the heap lookups for all columns, not from the index being ignored; forcing index usage would not reduce the heap access overhead.

Practice this question →
72
MCQhard

You are optimizing a Cloud SQL for MySQL instance for an OLTP application. You observe frequent buffer pool contention and high disk reads per second. The instance has 16 vCPUs and 120 GB memory. What is the most effective initial tuning action?

A.Increase the max_connections parameter to handle more concurrent sessions.
B.Reduce max_heap_table_size to avoid in-memory temp tables spilling to disk.
C.Increase innodb_buffer_pool_size to 80% of available memory.
D.Enable query cache to cache SELECT results.
AnswerC

This allows more data in memory, reducing disk I/O.

Why this answer

Buffer pool contention and high disk reads per second are classic symptoms of an undersized InnoDB buffer pool. For a dedicated Cloud SQL for MySQL OLTP instance with 120 GB memory, increasing innodb_buffer_pool_size to 80% (96 GB) maximizes cached data and index pages in memory, reducing physical disk I/O and contention. This directly addresses the root cause—insufficient memory for the working set—without introducing side effects like connection overhead or query cache invalidation.

Exam trap

Google Cloud often tests the misconception that increasing max_connections or enabling the query cache is a universal performance fix, but the trap here is that buffer pool contention and high disk reads specifically point to an undersized innodb_buffer_pool_size, not to connection limits or caching of SELECT results.

How to eliminate wrong answers

Option A is wrong because increasing max_connections does not reduce buffer pool contention or disk reads; it may actually worsen contention by allowing more concurrent sessions to compete for the same limited buffer pool resources. Option B is wrong because reducing max_heap_table_size would force more temporary tables to disk, increasing disk reads per second, which is the opposite of the desired effect. Option D is wrong because the query cache is deprecated and removed in MySQL 8.0; even in earlier versions, it introduces mutex contention and is ineffective for write-heavy OLTP workloads, failing to address buffer pool contention or disk I/O.

Practice this question →
73
MCQhard

A company runs a Cloud Spanner database with a multi-region configuration. They notice that write latency is higher than expected for clients in a region far from the leader region. What action should be taken to reduce write latency?

A.Reduce the number of replicas
B.Use directed reads
C.Change the default leader option to 'NEAREST'
D.Enable follower reads for writes
AnswerC

This places the leader in the nearest region, reducing write latency for that region.

Why this answer

Changing the default leader option to 'NEAREST' allows Cloud Spanner to dynamically assign the leader replica to the region closest to the majority of write requests, reducing the network round-trip time for clients far from the original leader region. This directly addresses the high write latency caused by geographic distance, as writes must be confirmed by the leader before committing.

Exam trap

The trap here is that candidates confuse directed reads (which reduce read latency) with leader placement options (which reduce write latency), or incorrectly assume that reducing replicas or using follower reads can improve write performance.

How to eliminate wrong answers

Option A is wrong because reducing the number of replicas does not reduce write latency; it may actually increase latency by reducing read availability and fault tolerance, and writes still require leader confirmation. Option B is wrong because directed reads are used to route read requests to the nearest replica for lower read latency, but they do not affect write latency, as writes must still go through the leader. Option D is wrong because follower reads are a read-only feature that allows reads from non-leader replicas; writes cannot be performed on followers, so enabling follower reads for writes is technically invalid.

Practice this question →
74
MCQmedium

A Cloud Spanner instance is experiencing high CPU utilization (above 80%) on multiple nodes. The database is used for an e-commerce application with a high volume of read-write transactions. The application uses the googlesql dialect and runs typical OLTP queries. You have already reviewed the query performance and found that most queries are efficient. Which initial step should you take to reduce CPU utilization?

A.Use the INFORMATION_SCHEMA.INDEXES view to identify and drop unused or redundant secondary indexes.
B.Adjust the application to use staleness of 5 seconds for reads to reduce CPU for read-write transactions.
C.Increase the number of nodes in the Spanner instance to spread the CPU load.
D.Create a separate read-only replica pool to offload read traffic.
AnswerA

Unused indexes cause extra write CPU and storage; removing them reduces CPU utilization directly.

Why this answer

High CPU utilization in Cloud Spanner often stems from excessive secondary index maintenance during write operations. Dropping unused or redundant indexes reduces the write amplification and CPU overhead per transaction, directly lowering CPU usage without compromising query performance, as the queries are already efficient.

Exam trap

Google Cloud often tests the misconception that scaling out (adding nodes) is the first step for performance issues, when in reality, eliminating unnecessary index maintenance is a more cost-effective and direct solution for CPU-bound write-heavy workloads.

How to eliminate wrong answers

Option B is wrong because adjusting staleness to 5 seconds for reads reduces read CPU by allowing stale reads, but the problem states high CPU utilization on multiple nodes with read-write transactions; stale reads do not reduce the CPU cost of write operations or index maintenance, which are the primary drivers. Option C is wrong because increasing nodes spreads the CPU load but does not address the root cause; it may mask the issue and increase costs without resolving the underlying index overhead. Option D is wrong because creating a separate read-only replica pool offloads read traffic, but the high CPU is from read-write transactions, and read-only replicas cannot handle writes; they do not reduce CPU for write-heavy workloads.

Practice this question →
75
MCQeasy

You need to set up a Cloud Monitoring alert for a Cloud Spanner instance to notify when the CPU utilization exceeds a threshold that could indicate performance degradation. What is the recommended CPU utilization threshold for Cloud Spanner?

A.90%
B.40%
C.80%
D.65%
AnswerD

65% is the recommended threshold to maintain performance headroom.

Why this answer

The recommended CPU utilization threshold for Cloud Spanner is 65%. This value is based on Google's best practices, as sustained CPU usage above 65% can lead to increased latency and performance degradation due to queuing and contention. Setting the alert at 65% provides a proactive warning before the instance reaches a critical state, allowing time for scaling or optimization.

Exam trap

Google Cloud often tests the misconception that higher thresholds like 80% or 90% are acceptable for alerting, but Cloud Spanner's distributed architecture requires a lower threshold to account for queuing effects and maintain consistent low latency.

How to eliminate wrong answers

Option A is wrong because 90% is too high; at this level, Cloud Spanner nodes experience significant queuing delays and potential throttling, making it a reactive rather than proactive threshold. Option B is wrong because 40% is too conservative; it would trigger false alarms unnecessarily, as Cloud Spanner is designed to handle moderate CPU loads without performance issues. Option C is wrong because 80% is above the recommended threshold; while it may indicate high utilization, it risks performance degradation before the alert fires, as queuing effects become noticeable above 65%.

Practice this question →
Page 1 of 2 · 102 questions totalNext →

Ready to test yourself?

Try a timed practice session using only Db Performance questions.

Start 20-question session