Scenario practice questions

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?

Trap 1: Monitor 'cloudsql.googleapis.com/network/received_bytes_count' and…

Network bytes received is a throughput metric, not a direct indicator of connection spikes.

Trap 2: Monitor 'cloudsql.googleapis.com/database/mysql/replication/seconds_…

This metric tracks replication lag, which is unrelated to connection spikes causing performance issues.

Trap 3: Monitor 'cloudsql.googleapis.com/instance/uptime' and check for…

Uptime indicates availability, not connection spikes or performance.

A
Monitor 'cloudsql.googleapis.com/network/received_bytes_count' and compare with connection count.
Why wrong: Network bytes received is a throughput metric, not a direct indicator of connection spikes.
B
Monitor 'cloudsql.googleapis.com/database/mysql/replication/seconds_behind_master' and compare with query latency.
Why wrong: This metric tracks replication lag, which is unrelated to connection spikes causing performance issues.
C
Monitor 'cloudsql.googleapis.com/instance/uptime' and check for instance restarts during degradation.
Why wrong: Uptime indicates availability, not connection spikes or performance.
D
Monitor 'cloudsql.googleapis.com/database/mysql/threads/threads_connected' and correlate with CPU utilization and query latency.
Threads connected directly indicates active connections, and correlating with CPU and latency helps identify the impact.

Question 2mediummultiple choice

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?

Trap 1: Use many small keys instead of a few large keys.

Small keys increase memory overhead due to key storage, potentially increasing evictions.

Trap 2: Decrease the maxmemory setting to force earlier eviction.

Lowering maxmemory would increase eviction rate.

Trap 3: Set maxmemory-policy to noeviction.

This would cause writes to fail instead of evicting keys.

A
Use many small keys instead of a few large keys.
Why wrong: Small keys increase memory overhead due to key storage, potentially increasing evictions.
B
Decrease the maxmemory setting to force earlier eviction.
Why wrong: Lowering maxmemory would increase eviction rate.
C
Set maxmemory-policy to noeviction.
Why wrong: This would cause writes to fail instead of evicting keys.
D
Change maxmemory-policy to allkeys-lru.
LRU eviction removes least recently used keys, which can improve cache efficiency.

Question 3easymultiple choice

A BI team queries this table with a WHERE clause that filters on product_id but does not include a sale_date filter. What is the outcome?

Exhibit

Refer to the exhibit.

CREATE TABLE mydataset.fact_sales (
  sale_id INT64,
  product_id INT64,
  sale_date DATE,
  amount FLOAT64
)
PARTITION BY DATE_TRUNC(sale_date, MONTH)
CLUSTER BY product_id
OPTIONS(require_partition_filter=true);

Trap 1: The query runs successfully and only scans partitions containing…

Clustering does not override partition filter requirement.

Trap 2: The query runs successfully and scans only the latest partition.

No partition filter is applied.

Trap 3: The query runs successfully but scans all partitions.

The table requires a partition filter.

A
The query fails with an error.
require_partition_filter=true causes query to fail without a partition filter.
B
The query runs successfully and only scans partitions containing product_id values.
Why wrong: Clustering does not override partition filter requirement.
C
The query runs successfully and scans only the latest partition.
Why wrong: No partition filter is applied.
D
The query runs successfully but scans all partitions.
Why wrong: The table requires a partition filter.

Question 4hardmultiple choice

An organization has a Cloud SQL for MySQL instance that stores sensitive data. They need to encrypt the data at rest using a customer-managed encryption key (CMEK). The database engineer creates a Cloud KMS key ring and key, and configures the Cloud SQL instance to use CMEK. However, after 30 days, the instance becomes inaccessible and the error message indicates the CMEK key is disabled. What is the most likely cause?

Trap 1: The Cloud SQL instance exceeded the number of times it can access…

There is no access count limit for CMEK keys.

Trap 2: The CMEK key was rotated, and the old key version was disabled.

Key rotation creates a new version; the old version remains enabled for decryption unless disabled.

Trap 3: The CMEK key expired, causing automatic disablement.

Cloud KMS keys do not auto-expire; they must be explicitly disabled or deleted.

A
The CMEK key was disabled by the key administrator for security reasons.
If the key is disabled, Cloud SQL cannot access it, causing the instance to become unavailable.
B
The Cloud SQL instance exceeded the number of times it can access the CMEK key.
Why wrong: There is no access count limit for CMEK keys.
C
The CMEK key was rotated, and the old key version was disabled.
Why wrong: Key rotation creates a new version; the old version remains enabled for decryption unless disabled.
D
The CMEK key expired, causing automatic disablement.
Why wrong: Cloud KMS keys do not auto-expire; they must be explicitly disabled or deleted.

Question 5easymultiple choice

A BI developer needs to write a query that calculates total sales by month for the current year. They create a Common Table Expression (CTE) to define monthly aggregates, then reference it in a final SELECT. What is the main benefit of using a CTE over a subquery in this scenario?

Trap 1: CTEs are always faster than subqueries.

CTEs and subqueries have similar performance in BigQuery.

Trap 2: CTEs reduce the amount of memory used by the query.

CTEs do not affect memory usage significantly.

Trap 3: CTEs automatically cache results for subsequent queries.

CTEs are not cached; they are executed each time the CTE is referenced.

A
CTEs are always faster than subqueries.
Why wrong: CTEs and subqueries have similar performance in BigQuery.
B
CTEs reduce the amount of memory used by the query.
Why wrong: CTEs do not affect memory usage significantly.
C
CTEs automatically cache results for subsequent queries.
Why wrong: CTEs are not cached; they are executed each time the CTE is referenced.
D
CTEs enhance query readability and maintainability.
CTEs allow you to break down complex queries into named steps.