DP-300 Practice Question: Monitor, configure, and optimize database resources
You are troubleshooting a performance issue on an Azure SQL Database. Which THREE Dynamic Management Views (DMVs) should you query to identify the most resource-intensive queries?
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_requests
The correct answers are A, C, and E. Option A (sys.dm_exec_requests) shows currently executing requests, which helps identify active resource-intensive queries. Option C (sys.dm_exec_query_plan) provides the execution plan for cached queries, enabling analysis of query performance. Option E (sys.dm_exec_query_stats) returns aggregated performance statistics (e.g., CPU time, logical reads) for cached query plans, making it ideal for identifying resource-intensive queries across all executions. Option B (sys.dm_os_wait_stats) is incorrect because it shows instance-level wait statistics, not query-specific resource usage. Option D (sys.dm_exec_sessions) is incorrect as it provides session-level information, not query-level resource metrics.
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_requests
Why this is correct
This DMV shows currently executing requests and their resource consumption.
- ✗
sys.dm_os_wait_stats
Why it's wrong here
This DMV shows wait statistics, not per-query resource usage.
- ✓
sys.dm_exec_query_plan
Why this is correct
This DMV retrieves the execution plan for a given plan handle.
- ✗
sys.dm_exec_sessions
Why it's wrong here
This DMV provides session-level information, not query resource usage.
- ✓
sys.dm_exec_query_stats
Why this is correct
`sys.dm_exec_query_stats` returns aggregated performance statistics for cached query plans, including total worker time, logical reads, and execution count. Querying it with an `ORDER BY` clause on `total_elapsed_time DESC` or `total_logical_reads DESC` directly identifies the most resource-intensive queries, satisfying the stem’s requirement to pinpoint high-resource consumers for troubleshooting.
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.