CompTIA DataSys+ (DS0-001)Database Management and MaintenanceHard
A database administrator is configuring a new production database server. To ensure optimal performance and stability, they need to separate different types of database files onto distinct physical disks or disk arrays. Which of the following file types should ideally be placed on its OWN dedicated, fast storage?
- AData files for read-only historical data.
- BLog files (transaction logs).
- COperating system files.
- DBackup files.
Show answer & explanationAnswer & explanation
Correct answer: B. Log files (transaction logs).
Transaction log files are critical for database recovery and write performance. They are written to sequentially and synchronously. Placing them on their own dedicated, fast storage (e.g., SSDs) minimizes I/O contention with data files and improves transaction commit times, which is vital for OLTP performance and recovery. Data files for read-only historical data or backup files do not have the same stringent latency requirements.
Why the other options are wrong
- A. Data files for read-only historical data typically have less stringent performance requirements compared to active OLTP data or transaction logs. They can often reside on slower, higher-capacity storage.
- C. Operating system files should be on their own dedicated storage, but this is distinct from database-specific files. The question asks about database file types.
- D. Backup files are typically large, sequential write operations that don't directly impact live database performance. They are usually placed on separate, often slower, but high-capacity storage for archiving.
Database File Separation
The practice of placing different types of database files (data, log, tempdb) on separate physical storage devices to reduce I/O contention and improve performance.
- Transaction logs benefit most from dedicated, fast storage due to sequential write patterns.
- Separating data and log files prevents read operations from competing with write operations.
- `tempdb` files also benefit from dedicated, fast storage to avoid contention.
Memory trick: Logs are fast, data is balanced, backups are slow.