DP-300 Practice Question: Monitor, configure, and optimize database resources
You are a database administrator for a large retail company. The company uses an Azure SQL Database in the Business Critical tier (8 vCores, 480 GB storage) to run its core transaction processing system. The database has automatic tuning enabled, including FORCE_LAST_GOOD_PLAN and CREATE_INDEX. You notice that the database is experiencing high CPU usage (90% average) during peak hours, and the Query Store shows that a specific query (Query ID 123) has regressed. The automatic tuning feature has forced a plan for this query, but the performance is still poor. You need to resolve the CPU issue and ensure the query runs efficiently. What should you do first?
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
✓
Use Query Store to compare the forced plan with the previous good plan and update statistics.
Reviewing the plan history in Query Store helps identify why the forced plan is not optimal, and perhaps the regression is due to parameter sniffing or outdated statistics. Option A is wrong because disabling automatic tuning may cause further regression and is not the first step. Option C is wrong because adding query hints like OPTIMIZE FOR UNKNOWN may not resolve the plan regression and could lead to suboptimal plans for other parameter values. Option D is wrong because scaling up the database increases resources temporarily but does not address the underlying plan regression issue.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Disable automatic tuning and manually create a plan guide for the query.
Why it's wrong here
Plan guides are a last resort; better to understand the root cause first.
- ✓
Use Query Store to compare the forced plan with the previous good plan and update statistics.
Why this is correct
Plan regression often due to statistics; updating may let optimizer pick a better plan.
- ✗
Modify the query to use query hints like OPTIMIZE FOR UNKNOWN.
Why it's wrong here
Changing application code is more invasive.
- ✗
Scale up the database to 16 vCores to handle the CPU load.
Why it's wrong here
Scaling up treats symptom, not cause.
Visual reference
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
One of 906 original DP-300 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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.