DP-300 Practice Question: Monitor, configure, and optimize database resources
Exhibit
Extended Events session trace: name: system_health session_id: 5 total_events: 12450 event_name: sql_statement_completed cpu_time: 2500 ms logical_reads: 45000 duration: 3000 ms name: system_health session_id: 5 total_events: 12451 event_name: sql_statement_completed cpu_time: 3200 ms logical_reads: 62000 duration: 4000 ms Wait statistics from sys.dm_os_wait_stats: wait_type: PAGEIOLATCH_SH waiting_tasks_count: 500000 wait_time_ms: 120000 wait_type: LCK_M_S waiting_tasks_count: 200 wait_time_ms: 500
Refer to the exhibit. An Azure SQL Database is experiencing performance degradation. Based on the Extended Events and wait statistics, which is the most likely root cause?
⚠ Common exam trap
The trap here is that candidates see PAGEIOLATCH_SH and assume it is always caused by insufficient memory, but the combination with WRITELOG waits clearly points to an I/O bottleneck, not a memory issue.
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
✓
I/O subsystem bottleneck
The exhibit shows PAGEIOLATCH_SH and WRITELOG waits dominating the wait statistics, which are classic indicators of I/O subsystem bottlenecks. PAGEIOLATCH_SH waits occur when a session is waiting for a data page to be read from disk into the buffer pool, while WRITELOG waits indicate delays in writing to the transaction log. These waits are not caused by CPU or memory pressure, but by slow disk I/O, making option C the correct root cause.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Blocking due to lock contention
Why it's wrong here
Lock contention would appear as LCK_M_* waits with blocking chains in the Extended Events session; the exhibit's waits do not match. Blocking is tempting because it is a frequent cause of sudden slowdowns, and it would be correct if the captured waits showed sessions waiting on locks held by another transaction.
- ✗
CPU pressure from high-complexity queries
Why it's wrong here
The exhibit's waits would need to show CPU-related signals such as SOS_SCHEDULER_YIELD alongside high worker utilisation; the stem's evidence points elsewhere. CPU pressure is tempting because expensive queries are a common cause of degradation, and it would be correct if the waits and Query Store showed sustained high CPU with those signals.
- ✓
I/O subsystem bottleneck
Why this is correct
Elevated wait times on PAGEIOLATCH and related I/O waits, combined with the Extended Events output, point to storage latency rather than CPU or blocking. The database is waiting on data pages to be read from disk, indicating the underlying I/O subsystem cannot meet demand.
- ✗
Insufficient memory allocation for the database
Why it's wrong here
Memory pressure would surface as RESOURCE_SEMAPHORE or PAGEIOLATCH waits with low page-life expectancy; the exhibit's evidence indicates otherwise. Insufficient memory is tempting because it commonly degrades Azure SQL Database performance, and it would be correct if the waits and memory DMVs showed grant waits or buffer-pool pressure.
Go deeper
Related to this question
Learn chapter
Monitoring Database Performance with Azure Tools
Key term
Azure SQL Performance Tuning
Azure SQL Performance Tuning is the process of optimizing the speed and efficiency of queries and database operations in Microsoft Azure SQL Database or SQL Managed Instance to reduce latency and improve throughput.
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 by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
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.