Google Professional Cloud Database Engineer Question 198
Select 3Google Cloud PlatformYou are managing a Cloud Spanner database that has been in production for over a year. Over time, you notice degraded query performance for some tables. You suspect table fragmentation might be causing the issue. Which of the following steps should you take to assess table fragmentation in Cloud Spanner?
- A
Use the INFORMATION_SCHEMA.TABLES table to check the physical size of tables and identify potential fragmentation.
- B
Analyze the query execution plans using the Query Insights feature to identify high-latency queries caused by fragmentation.
- C
Review the statistics in the INFORMATION_SCHEMA.INDEXES table to check for fragmented indexes.
- D
Check the number of splits in the INFORMATION_SCHEMA.DATABASE_SPLITS table to identify excessive splits that may indicate fragmentation.
- E
Export the table data to Cloud Storage and compare it with the original data size to detect any fragmentation.
Show answer and explanation
Correct answers: A, C, D
Explanation
Table fragmentation in Cloud Spanner can lead to degraded performance. Using INFORMATION_SCHEMA tables, such as TABLES and INDEXES, provides insights into table size and index fragmentation. Additionally, the DATABASE_SPLITS table helps identify excessive splits, which may indicate fragmentation. These methods are efficient and align with Cloud Spanner's native tooling for diagnosing fragmentation issues.
- A. Correct.
Correct: The INFORMATION_SCHEMA.TABLES table contains metadata about table sizes, which can help identify tables that have excessive fragmentation.
- B. Incorrect.
Incorrect: Query Insights is useful for performance monitoring but does not provide direct information about table fragmentation.
- C. Correct.
Correct: Fragmented indexes can contribute to performance degradation. The INFORMATION_SCHEMA.INDEXES table provides useful metadata for identifying such issues.
- D. Correct.
Correct: The INFORMATION_SCHEMA.DATABASE_SPLITS table provides information on splits, which can indicate fragmentation in Cloud Spanner.
- E. Incorrect.
Incorrect: While exporting and comparing data might suggest differences in size, it is not a reliable or efficient method to assess fragmentation in Cloud Spanner.