DP-300 Practice Question: Monitor, configure, and optimize database resources
You are monitoring an Azure SQL Database that is experiencing high DTU consumption. You need to identify the queries that are causing high resource usage. Which two data sources can you use? (Choose two.)
⚠ Common exam trap
Many candidates confuse wait statistics (sys.dm_os_wait_stats) with query-level performance data, but wait stats show system-wide bottlenecks, not the specific queries causing high DTU.
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
✓
Query Store
Query Store (Option B) captures a history of query execution plans and runtime statistics, allowing you to identify queries with high CPU, I/O, or duration. sys.dm_exec_query_stats (Option C) returns aggregate performance statistics for cached query plans, including total CPU time and logical reads, which directly points to resource-intensive queries. Both are valid sources for diagnosing high DTU consumption in Azure SQL Database.
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_os_wait_stats
Why it's wrong here
sys.dm_os_wait_stats aggregates wait types across the whole instance, so it shows what the engine waited on, not which query caused the consumption. It is the right source when diagnosing instance-wide contention such as blocking or latch waits.
- ✓
Query Store
Why this is correct
Query Store persists execution plans, runtime statistics and wait categories per query, letting you rank statements by CPU, duration or logical reads. This directly identifies the queries driving high DTU consumption on the Azure SQL Database.
- ✓
sys.dm_exec_query_stats
Why this is correct
sys.dm_exec_query_stats exposes cumulative execution counts, worker time and logical reads for cached plans, so aggregating by query hash reveals the heaviest consumers. This pinpoints the statements responsible for elevated DTU usage on the Azure SQL Database.
- ✗
sys.dm_db_index_usage_stats
Why it's wrong here
sys.dm_db_index_usage_stats reports index seek, scan, lookup and update counts, not per-query CPU, duration or DTU consumption, so it cannot identify the offending queries. It is tempting because it exposes resource-heavy index operations, and would suit index tuning or unused-index analysis rather than query-level DTU attribution.
- ✗
sys.dm_io_virtual_file_stats
Why it's wrong here
sys.dm_io_virtual_file_stats reports per-file I/O latency and throughput, not per-query CPU or DTU attribution, so it cannot name the offending queries. It is the right source when diagnosing storage-level bottlenecks such as data or log file latency.
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.
Key term
Query Store
Query Store is a built-in SQL Server feature that captures and stores a history of query execution plans and performance data for easy monitoring and troubleshooting.
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.