CompTIA DataSys+ (DS0-001)Database Management and MaintenanceMedium
A database administrator is investigating reports of intermittent query slowdowns. They suspect that the database's query optimizer is making suboptimal execution plans because it lacks up-to-date information about the data distribution. What action should the administrator take to provide the optimizer with the most current data distribution information?
- AIncrease the `query_timeout` parameter.
- BUpdate database statistics for the affected tables.
- CRebuild all indexes on the affected tables.
- DDisable the query optimizer for complex queries.
Show answer & explanationAnswer & explanation
Correct answer: B. Update database statistics for the affected tables.
Database statistics provide the query optimizer with information about the data distribution and cardinality of columns. Updating these statistics ensures the optimizer has current information to create efficient execution plans, directly addressing suboptimal plans due to outdated data distribution knowledge.
Why the other options are wrong
- A. Increasing `query_timeout` just allows slow queries to run longer; it doesn't optimize their execution.
- C. Rebuilding indexes primarily addresses fragmentation or index corruption, not directly the optimizer's knowledge of data distribution.
- D. Disabling the query optimizer would force queries to use default, often inefficient, plans, worsening performance.
Database Statistics Update
The process of collecting and updating metadata about the data distribution and cardinality within database tables and indexes, used by the query optimizer.
- Crucial for the query optimizer to choose efficient execution plans.
- Should be updated regularly, especially after significant data changes.
- Can be updated manually or automatically.
Memory trick: Optimizer needs fresh 'stats' to plan the best route.