CompTIA DataSys+ (DS0-001)Database Management and MaintenanceMedium
A database administrator is performing routine maintenance and notices that several tables have become significantly fragmented. This fragmentation is impacting query performance due to increased I/O operations as data blocks are scattered across disk. Which of the following maintenance tasks should the administrator perform to address this issue?
- AUpdating database statistics.
- BAdjusting the database isolation level.
- CIncreasing the database auto-commit interval.
- DRebuilding or reorganizing indexes and tables.
Show answer & explanationAnswer & explanation
Correct answer: D. Rebuilding or reorganizing indexes and tables.
Rebuilding or reorganizing indexes and tables physically reorders the data and index pages, consolidating free space and reducing fragmentation. This leads to more efficient data retrieval and reduced I/O.
Why the other options are wrong
- A. Updating database statistics helps the query optimizer but does not address physical data fragmentation on disk.
- B. Adjusting the database isolation level affects concurrency control and locking behavior, not physical data storage or fragmentation.
- C. Increasing the auto-commit interval affects transaction behavior but has no direct impact on physical data fragmentation.
Database Fragmentation
A condition where data and index blocks are scattered non-contiguously across disk, requiring more I/O operations to retrieve complete data sets, thus degrading performance.
- Caused by frequent INSERTs, UPDATEs, and DELETEs.
- Leads to increased disk I/O and slower query execution.
- Resolved by rebuilding or reorganizing indexes and tables.
Memory trick: Fragmentation is like a messy bookshelf, reorganization tidies it up.