DP-300 Practice Question: Monitor, configure, and optimize database resources
Which THREE actions can help reduce the frequency of parameter-sensitive plan (PSP) problems in Azure SQL Database? (Choose three.)
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 Optimize for ad hoc workloads setting (or OPTIMIZE FOR UNKNOWN).
Options A, C, and D are correct. Option A (Optimize for ad hoc workloads or OPTIMIZE FOR UNKNOWN) helps by using average distribution instead of sniffing parameter values, reducing parameter-sensitive plan (PSP) problems. Option C (RECOMPILE hint) forces a new plan per execution, avoiding stale plans caused by parameter sniffing. Option D (forced parameterization) creates multiple cached plans for different parameter values, directly addressing PSP. Option B (disabling parameter sniffing) is not a recommended approach and can lead to suboptimal plans. Option E (Query Store) is a monitoring tool that captures query data but does not directly reduce PSP frequency.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Use the Optimize for ad hoc workloads setting (or OPTIMIZE FOR UNKNOWN).
Why this is correct
Helps balance plan choice.
- ✗
Disable parameter sniffing by using the DISABLE_PARAMETER_SNIFFING hint.
Why it's wrong here
May lead to suboptimal plans.
- ✓
Add the RECOMPILE query hint to problematic queries.
Why this is correct
Forces recompilation for each execution.
- ✓
Create separate cached plans using forced parameterization.
Why this is correct
Can create multiple plans for different parameter values.
- ✗
Enable Query Store and use the Performance Dashboard.
Why it's wrong here
Only monitors, does not fix.
Go deeper
Related to this question
Learn chapter
Overview of Azure Data Platform Options
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.
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.