DP-300 Practice Question: Monitor, configure, and optimize database resources
You manage an Azure SQL Database that runs a reporting workload. Users report that queries against a large fact table sometimes take much longer than usual, and you suspect that a plan regression occurred after a recent statistics update. You need to identify which queries have a plan that changed and performed worse, without deploying any external monitoring tools. What should you use?
⚠ Common exam trap
The trap here is assuming that a performance dashboard or index tool will identify plan regressions, when only Query Store tracks plan changes and their performance impact over time.
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's Regressed Queries view
Query Store persists query text, plans, and runtime statistics inside the database and includes a Regressed Queries view that compares plan performance across time windows. When a statistics update causes a plan change that degrades a reporting query, that view surfaces the affected queries with their historical plans and metrics, letting you force a known-good plan or take other corrective action without external monitoring.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
SQL Server Profiler traces collected to a file
Why it's wrong here
Profiler is deprecated for Azure SQL Database and is not needed here. Even if traces were captured, they would show statement-level events but not a built-in comparison of plan performance over time. The requirement is to find regressed queries without external tooling, which Profiler does not provide.
- ✗
Azure SQL Database automatic tuning's CREATE INDEX recommendations
Why it's wrong here
Automatic tuning recommends or creates indexes based on workload analysis, but it does not expose a view of queries whose plans regressed. It may improve performance over time, yet it will not identify the specific queries that became slower after the statistics update, so it does not meet the requirement.
- ✓
Query Store's Regressed Queries view
Why this is correct
Query Store automatically captures query plans and runtime statistics, and its Regressed Queries view compares plan performance over time to highlight queries whose plan changed and became slower. Because the feature is built into the database, no external monitoring tool is required, and it directly answers which queries regressed after the statistics update.
- ✗
sys.dm_db_index_physical_stats
Why it's wrong here
This dynamic management function reports index fragmentation and page density, not query plan changes. It can help you decide whether to rebuild or reorganize an index, but it cannot tell you which queries regressed or whether their execution plan changed. In this scenario the issue is plan choice, not physical index structure.
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.
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 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.