DP-300 Practice Question: Monitor, configure, and optimize database resources
You have a SQL Managed Instance that hosts a critical OLTP database. You notice that the average query wait time has increased significantly over the past hour. You need to identify the top resource waits. What should you use?
⚠ Common exam trap
Watch out — candidates often confuse performance metrics DMVs (like sys.dm_exec_query_stats) with wait statistics DMVs, or they assume Query Store Wait Stats is the primary diagnostic tool for real-time wait analysis, when sys.dm_os_wait_stats is the direct and authoritative source for identifying top resource waits.
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
✓
sys.dm_os_wait_stats
C is correct because sys.dm_os_wait_stats is the dynamic management view that aggregates wait statistics across all sessions in the SQL Server instance, including SQL Managed Instance. It provides cumulative wait times categorized by wait type (e.g., PAGEIOLATCH, LCK_M_S), making it the appropriate tool to identify top resource waits when average query wait time increases.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
sys.dm_exec_query_stats
Why it's wrong here
sys.dm_exec_query_stats returns per-query execution and CPU statistics, not wait-type breakdowns, so it cannot rank resource waits. It is tempting because it is the standard DMV for finding expensive queries, but the stem asks which resource the instance is waiting on, which sys.dm_os_wait_stats reports.
- ✗
Query Store Wait Stats in SSMS
Why it's wrong here
Query Store wait stats aggregate waits per query over time, not the instance-wide top resource waits occurring in the last hour. It is tempting because Query Store is the right tool for identifying regressed queries and their plans, but the requirement is instance-level wait categories, which sys.dm_os_wait_stats provides.
- ✓
sys.dm_os_wait_stats
Why this is correct
sys.dm_os_wait_stats aggregates cumulative wait statistics by wait type across the instance, letting you rank the top resource waits causing the slowdown. It directly satisfies the need to identify which resource the OLTP workload is waiting on.
- ✗
sys.dm_db_index_usage_stats
Why it's wrong here
sys.dm_db_index_usage_stats counts index seeks, scans, lookups and updates, revealing unused or missing indexes rather than wait categories. It is tempting because it is the correct DMV for index tuning, but the scenario requires identifying top resource waits, which sys.dm_os_wait_stats supplies.
Go deeper
Related to this question
About these practice questions
This DP-300 question is part of Courseiva's 574-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam 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.