CompTIA DataSys+ (DS0-001)Database Management and MaintenanceMedium
A database administrator is reviewing the database's performance during peak hours. They notice that the `buffer cache hit ratio` is consistently below 80%, and disk I/O for data reads is very high. This indicates that the database is frequently retrieving data directly from disk rather than from memory. Which of the following actions would MOST directly improve the `buffer cache hit ratio` and reduce disk I/O for reads?
- AImplementing more granular table-level locking.
- BIncreasing the size of the transaction log.
- CIncreasing the database buffer cache (or buffer pool) size.
- DAdding more CPU cores to the server.
Show answer & explanationAnswer & explanation
Correct answer: C. Increasing the database buffer cache (or buffer pool) size.
The buffer cache (or buffer pool) stores recently accessed data blocks in memory. Increasing its size allows more data to be held in RAM, leading to a higher cache hit ratio and reducing the need to read data from slower disk storage.
Why the other options are wrong
- A. Implementing more granular table-level locking (e.g., row-level locking) is for reducing contention during write operations, not for improving read performance or cache hit ratio.
- B. Increasing the transaction log size primarily affects write performance and recovery, not read performance or buffer cache hit ratio.
- D. Adding more CPU cores helps with CPU-bound workloads but doesn't directly address the issue of data not being found in the buffer cache.
Buffer Cache Hit Ratio
A performance metric that indicates the percentage of data requests that were satisfied by retrieving data from the database's memory cache (buffer pool) rather than from slower disk storage.
- Higher ratio (e.g., >90%) indicates efficient memory utilization.
- Low ratio suggests insufficient cache size or inefficient queries.
- Directly impacts read performance and disk I/O.
Memory trick: The buffer cache is the database's short-term memory.