CompTIA DataSys+ (DS0-001)Database Management and MaintenanceMedium
A database administrator is investigating a report of intermittent query slowdowns on a critical production database. They suspect that the database optimizer might be using outdated information for query plan generation, leading to inefficient execution paths. Which database maintenance task should the administrator prioritize to address this issue?
- ADatabase Index Rebuild
- BUpdate Database Statistics
- CTransaction Log Backup
- DDatabase Sharding
Show answer & explanationAnswer & explanation
Correct answer: B. Update Database Statistics
Outdated database statistics can lead the query optimizer to make incorrect assumptions about data distribution and cardinality, resulting in inefficient query plans and slowdowns. Updating statistics provides the optimizer with current information to generate optimal execution paths.
Why the other options are wrong
- A. Database Index Rebuild addresses fragmentation and improves index efficiency, but statistics update is more direct for optimizer plan issues.
- C. Transaction Log Backup is for disaster recovery and log space management, not query plan optimization.
- D. Database Sharding is a scaling technique, not a maintenance task for optimizer issues.
Database Statistics Update
The process of collecting and updating metadata about the data distribution and physical storage characteristics within a database.
- Informs the query optimizer
- Crucial for efficient query plan generation
- Should be run regularly or after significant data changes
Memory trick: Stats are the map for the optimizer's journey.