CompTIA DataSys+ (DS0-001)Database Management and MaintenanceHard
A database administrator is routinely checking the health of a production database. They observe that the `tempdb` database is experiencing very high I/O wait times and frequent growth events. The database primarily supports OLTP applications with many small, concurrent transactions. What is the MOST likely cause of this `tempdb` contention?
- AInsufficient memory allocated to the buffer pool.
- BExcessive use of large sorts or hash joins in queries.
- CA single `tempdb` data file on a slow disk.
- DOutdated statistics causing inefficient query plans.
Show answer & explanationAnswer & explanation
Correct answer: C. A single `tempdb` data file on a slow disk.
In OLTP environments with many small, concurrent transactions, `tempdb` can become a bottleneck due to allocation contention. Having a single `tempdb` data file, especially on a slow disk, means all these concurrent allocation requests must serialize, leading to high I/O wait times and growth events as the database tries to manage this contention.
Why the other options are wrong
- A. Insufficient memory would primarily lead to increased physical I/O for user databases, not necessarily specific high I/O wait times and frequent growth in `tempdb` unless `tempdb` pages are constantly being swapped out.
- B. Excessive use of large sorts or hash joins would certainly consume `tempdb` space and generate I/O, but the 'frequent growth events' and 'high I/O wait times' in a highly concurrent OLTP scenario often point more directly to allocation contention within `tempdb` itself, especially with a single file.
- D. Outdated statistics can cause inefficient query plans that might use `tempdb` excessively, but the specific symptoms of high I/O wait times and frequent growth events in `tempdb` in a concurrent OLTP environment are more directly linked to physical allocation contention.
Tempdb Contention
Performance bottleneck in SQL Server's `tempdb` database, often caused by many concurrent processes trying to allocate pages, leading to waits on allocation structures.
- Commonly occurs in highly concurrent OLTP systems.
- Symptoms include high PAGELATCH_EX and PAGELATCH_SH waits on `tempdb` pages.
- Mitigated by creating multiple `tempdb` data files and ensuring they are on fast storage.
Memory trick: Tempdb needs many files, fast disks, and good queries.