CompTIA DataSys+ (DS0-001)Database Management and MaintenanceMedium
A database administrator notices a significant slowdown in query execution times on a production database, particularly for queries involving large joins and aggregations. After reviewing the database's performance metrics, they observe high I/O wait times and frequent full table scans. Which of the following database maintenance tasks is MOST likely to resolve this performance issue?
- APerforming a full database backup
- BUpdating database statistics
- CRestarting the database server
- DIncreasing the database cache size
Show answer & explanationAnswer & explanation
Correct answer: B. Updating database statistics
Updating database statistics provides the query optimizer with accurate information about data distribution, which helps it choose more efficient execution plans, reducing full table scans and I/O wait times.
Why the other options are wrong
- A. A full database backup is for disaster recovery and data protection, not directly for resolving query performance issues.
- C. Restarting the server might temporarily clear some memory issues but does not address underlying query optimization problems or inefficient execution plans.
- D. While increasing cache size can help, without accurate statistics, the optimizer might still choose inefficient plans that don't fully utilize the cache, leading to continued high I/O.
Database Statistics
Metadata about the data stored in a table or index, used by the query optimizer to determine the most efficient execution plan for SQL queries.
- Includes information like data distribution, number of rows, and column cardinality.
- Outdated statistics lead to poor query plans and performance degradation.
- Should be updated regularly, especially after significant data changes.
Memory trick: Stats are the map for the query's journey.