DP-300 Plan and implement data platform resources Practice Question
You have an Azure SQL Managed Instance that is experiencing performance degradation. You suspect a query is causing excessive blocking. You need to identify the blocking chain and the resource holding the lock. Which DMV should you query?
⚠ Common exam trap
The trap here is that candidates often pick sys.dm_exec_requests (Option A) because it shows wait_type and blocking_session_id, but it lacks the granular lock resource information (e.g., RID, KEY) that sys.dm_tran_locks provides, which is essential for identifying the exact resource holding the lock.
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_tran_locks and sys.dm_os_waiting_tasks
To identify the blocking chain and the specific resource holding the lock, you need to combine lock metadata with wait information. sys.dm_tran_locks shows current locks and their resource types (e.g., RID, KEY, PAGE, OBJECT), while sys.dm_os_waiting_tasks reveals which sessions are waiting on those locks and the blocking session ID. Together, these DMVs allow you to trace the blocking chain from the blocked session back to the blocker and pinpoint the exact resource causing contention.
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 it's wrong here
sys.dm_exec_requests shows currently executing requests and their wait information but does not expose the full blocking chain or the head blocker's identity. sys.dm_os_waiting_tasks maps blocking_session_id relationships to reveal the chain. It is tempting because it lists sessions and waits, which is the correct choice for inspecting individual query execution details.
- ✓
sys.dm_tran_locks and sys.dm_os_waiting_tasks
Why this is correct
sys.dm_tran_locks reveals the granted and requested locks with their owning sessions, while sys.dm_os_waiting_tasks exposes the blocking chain through blocking_session_id. Together they satisfy the stem's need to identify both the blocking chain and the lock holder.
- ✗
sys.dm_exec_query_stats
Why it's wrong here
sys.dm_exec_query_stats returns aggregated execution statistics per cached plan, with no session or blocking columns, so it cannot reveal the blocking chain. It is the right DMV for finding high-CPU or high-read queries by plan, not for live lock contention.
- ✗
sys.dm_tran_active_snapshot_database_transactions
Why it's wrong here
sys.dm_tran_active_snapshot_database_transactions lists transactions using row versioning under snapshot isolation, so it cannot reveal which session holds a blocking lock. It is tempting because it exposes transaction-level detail, and it would be the right choice when diagnosing version-store growth or long-running snapshot transactions in a database with READ_COMMITTED_SNAPSHOT or ALLOW_SNAPSHOT_ISOLATION enabled.
Go deeper
Related to this question
Learn chapter
Deploying and Configuring Azure SQL Managed Instance
Key term
Azure SQL Managed Instance
Azure SQL Managed Instance is a fully managed cloud database service that gives you nearly all the features of Microsoft SQL Server on your own server, without you having to manage the hardware or operating system.
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.