DP-300 Practice Question: Monitor, configure, and optimize database resources
You manage an Azure SQL Database that experiences periodic performance degradation. You need to identify the top queries by CPU consumption over the last hour. Which dynamic management view should you query?
⚠ Common exam trap
DP-300 often tests the distinction between DMVs that report cumulative historical statistics (sys.dm_exec_query_stats) and those that report only the current instant (sys.dm_exec_requests) — candidates pick the 'requests' DMV because it sounds like it tracks query activity.
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_exec_query_stats
sys.dm_exec_query_stats returns one row per cached query plan and includes cumulative execution statistics such as total_worker_time (CPU), total_elapsed_time, and execution_count. Aggregating total_worker_time over the last hour identifies the top CPU-consuming queries. This is the standard DMV for query-level performance analysis 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_exec_sessions
Why it's wrong here
sys.dm_exec_sessions reports session-level connection state and resource usage, not per-statement CPU aggregated over a time window. It is tempting because it exposes live session activity, which would be correct when identifying currently connected sessions or blocking, rather than ranking queries by CPU consumed in the last hour.
- ✗
sys.dm_exec_query_plan
Why it's wrong here
sys.dm_exec_query_plan returns the XML showplan for a supplied plan handle; it holds no aggregated CPU totals or time-window ranking. It is tempting because it reveals per-query execution detail, which would be correct when inspecting the operator-level plan of one identified slow query rather than finding the top CPU consumers.
- ✓
sys.dm_exec_query_stats
Why this is correct
sys.dm_exec_query_stats aggregates cumulative execution statistics per cached query plan, including total worker time, so ordering by CPU time over the last hour identifies the top CPU-consuming queries. It satisfies the need to rank queries by CPU consumption.
- ✗
sys.dm_exec_requests
Why it's wrong here
sys.dm_exec_requests shows only currently executing requests, so queries that already finished within the last hour are absent. It is tempting because it exposes live CPU and wait data for active sessions, and it would be the right choice when diagnosing what a session is running right now.
Go deeper
Related to this question
Learn chapter
Optimizing Database Query and Index Performance
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 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.