Question 227 of 919
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.
About these practice questions
Courseiva creates original exam-style practice questions with explanations and wrong-answer analysis. It does not publish real exam questions, exam dumps, or protected exam content. Learn why practice questions differ from exam dumps →
Last reviewed: Jun 21, 2026
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.
Question Discussion
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
Sign in to join the discussion.