DP-300 Practice Question: Monitor, configure, and optimize database resources
Your Azure SQL Database is experiencing high CPU utilization due to frequent recompilations of a stored procedure. Which TWO actions should you take to reduce recompilations?
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 the KEEPFIXED PLAN query hint
Options B and E are correct. Option B: Using the KEEPFIXED PLAN query hint prevents recompilations caused by statistics changes, ensuring the query plan remains stable. Option E: Ensuring parameter data types match the column data types avoids type mismatches that trigger recompilations. Option A (sp_refreshsqlmodule) updates metadata but doesn't reduce recompilations. Option C (OPTIMIZE FOR UNKNOWN) can lead to plan instability without reducing recompilations. Option D (RECOMPILE) forces recompilation on each execution, increasing recompilations.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Execute sp_refreshsqlmodule on the stored procedure
Why it's wrong here
sp_refreshsqlmodule updates metadata but does not reduce recompilations.
- ✓
Use the KEEPFIXED PLAN query hint
Why this is correct
KEEPFIXED PLAN prevents recompilation due to statistics changes, reducing recompilations.
- ✗
Use the OPTIMIZE FOR UNKNOWN query hint
Why it's wrong here
OPTIMIZE FOR UNKNOWN affects the plan but does not reduce recompilations.
- ✗
Use the RECOMPILE query hint
Why it's wrong here
RECOMPILE forces recompilation on each execution, increasing recompilations.
- ✓
Ensure parameter data types match the column data types
Why this is correct
Data type mismatches often cause recompilations; matching types reduces them.
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.