Microsoft Azure Data FundamentalsDescribe how to work with relational data on AzureMedium
An application uses Azure Database for PostgreSQL and experiences inconsistent query performance, particularly during peak hours. The database administrator suspects that some queries are not efficiently utilizing available indexes. Which basic management task should the administrator perform to analyze and potentially resolve this issue?
- AIncrease the database's storage size.
- BUpdate database statistics.
- CChange the database's collation settings.
- DPerform a database backup and restore operation.
Show answer & explanationAnswer & explanation
Correct answer: B. Update database statistics.
Database statistics provide information about the data distribution in tables and indexes. The query optimizer uses these statistics to determine the most efficient execution plan for queries. If statistics are outdated, the optimizer might choose inefficient plans. Updating statistics helps the optimizer make better decisions, leading to improved query performance.
Why the other options are wrong
- A. Increasing storage size addresses capacity issues, not necessarily query performance or index utilization unless the database is running out of space impacting I/O.
- C. Changing collation settings affects how data is sorted and compared (e.g., case sensitivity) but does not directly impact index utilization for general query performance issues.
- D. Backing up and restoring is for disaster recovery or migration, not directly for query performance issues related to index utilization.
Database Statistics
Metadata about the data distribution in tables and indexes that the query optimizer uses to create efficient execution plans.
- Used by query optimizer
- Impacts query execution plan efficiency
- Outdated statistics can lead to poor performance
- Should be updated regularly or automatically
Memory trick: Optimize queries: check plans, update stats, tune indexes.