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
LCK_M_S waits are minimal compared to PAGEIOLATCH_SH.
- ✗
CPU pressure from high-complexity queries
Why it's wrong here
CPU time is moderate; wait stats point to I/O.
- ✓
I/O subsystem bottleneck
Why this is correct
PAGEIOLATCH_SH waits indicate I/O latency.
- ✗
Insufficient memory allocation for the database
Why it's wrong here
Memory pressure typically shows PAGEIOLATCH_SH, but the primary indicator is high logical reads and IO waits.
Go deeper
Related to this question
Learn chapter
Overview of Azure Data Platform Options
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 — 906 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.