DP-300 Practice Question: Monitor, configure, and optimize database resources
You are a database administrator for an Azure SQL Managed Instance that hosts a critical OLTP database. You notice that the instance is experiencing high PAGELATCH_EX waits on tempdb allocation pages. You need to reduce this contention without changing the service tier. What should you do?
⚠ Common exam trap
The trap here is thinking that faster storage or Resource Governor will fix latch contention, when the issue is concurrent allocation on the same pages.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Add more tempdb data files.
PAGELATCH_EX waits on tempdb allocation pages are caused by multiple concurrent sessions trying to allocate space on the same pages. Adding more tempdb data files spreads the allocation across multiple files, reducing contention. This is a well-known tuning technique for tempdb. Other options do not address the root cause: ADR affects recovery, Resource Governor limits resources, and faster storage addresses I/O waits, not latch contention. Therefore, adding more tempdb data files is the correct solution.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Configure Resource Governor to limit tempdb usage.
Why it's wrong here
Resource Governor can limit resource consumption for workloads, but it does not directly reduce PAGELATCH_EX contention on tempdb allocation pages. Limiting tempdb usage might throttle workloads but would not solve the underlying allocation bottleneck. It could even cause performance issues for legitimate queries. Therefore, it is not the correct remedy for this specific wait type.
- ✗
Enable Accelerated Database Recovery (ADR) on the instance.
Why it's wrong here
ADR improves recovery time and reduces blocking from long-running transactions, but it does not address allocation page contention in tempdb. The waits are on PAGELATCH_EX for allocation pages, which are caused by concurrent space allocation, not by recovery operations. Enabling ADR would not reduce these waits and may not be relevant to the scenario.
- ✗
Move the tempdb files to a faster storage tier.
Why it's wrong here
Moving tempdb to faster storage may improve I/O latency, but PAGELATCH_EX waits are latch contention, not I/O waits. They occur in memory when threads contend for page latches during allocation. Faster storage does not reduce latch contention. The solution is to reduce the number of concurrent allocations per page, typically by adding more files.
- ✓
Add more tempdb data files.
Why this is correct
PAGELATCH_EX waits on tempdb allocation pages (such as PFS, GAM, SGAM) occur when many concurrent sessions allocate space in tempdb. Adding more tempdb data files distributes the allocation load across multiple files, reducing contention on allocation pages. This is a standard remedy for tempdb allocation bottlenecks. Since the service tier cannot change, adding files is the appropriate action to alleviate the contention without scaling up.
Go deeper
Related to this question
Learn chapter
Configuring Disaster Recovery for Azure SQL
Key term
Azure SQL Managed Instance
Azure SQL Managed Instance is a fully managed cloud database service that gives you nearly all the features of Microsoft SQL Server on your own server, without you having to manage the hardware or operating system.
About these practice questions
Courseiva writes every DP-300 question from scratch — 574 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Microsoft exam blueprint
This DP-300 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the DP-300 exam.