DP-300 Practice Question: Monitor, configure, and optimize database resources
Which THREE actions can you take to optimize query performance in Azure SQL Database using Intelligent Query Processing?
⚠ Common exam trap
DP-300 often tests candidates who conflate all performance features (Query Store, columnstore, IQP) into one bucket — the key is recognizing that IQP is specifically about runtime plan adaptation, not storage or monitoring.
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
✓
Enable adaptive joins
Adaptive joins (A) are an Intelligent Query Processing feature that lets the optimizer defer the choice between a hash join and a nested loops join until runtime, switching to the better plan based on actual row counts and thereby improving performance for queries with inaccurate cardinality estimates. Interleaved execution for MSTVFs (B) is also an IQP feature that lets multi-statement table-valued functions be executed in an interleaved manner so the optimizer can use actual row counts from the function's first execution to produce a better overall plan. Approximate count distinct (E) is an IQP capability (APPROX_COUNT_DISTINCT) that returns statistically accurate distinct counts with much less CPU and memory than exact COUNT(DISTINCT), speeding up aggregation-heavy queries. Query Store (C) is a monitoring and plan-capture feature, not an IQP query-optimization action, and columnstore indexes (D) are a physical data-access/columnar storage technology rather than an Intelligent Query Processing optimization, so neither belongs among the three IQP actions.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Enable adaptive joins
Why this is correct
Adaptive joins let the optimiser defer the hash-versus-nested-loops decision until after the first input is scanned, choosing based on actual row counts. This corrects poor join choices caused by inaccurate cardinality estimates at compile time.
- ✓
Enable interleaved execution for MSTVFs
Why this is correct
Interleaved execution lets SQL Server pause query execution mid-plan to obtain the actual row count from a multi-statement table-valued function, then resume with corrected cardinality estimates. This directly addresses the fixed-estimate misestimations that degrade plans for MSTVF joins, satisfying the stem's requirement for an Intelligent Query Processing feature.
- ✗
Enable Query Store
Why it's wrong here
Query Store is a prerequisite that must be enabled for several Intelligent Query Processing features to gather workload data, but enabling it is not itself an optimisation action. It tempts because it underpins IQP feedback, and would be correct when preparing a database for those features.
- ✗
Enable columnstore indexes
Why it's wrong here
Columnstore indexes are a physical design feature, not part of Intelligent Query Processing; IQP adapts plans at runtime via batch mode, adaptive joins and memory grant feedback. Columnstore suits analytical workloads with large scans, so it would be the right choice for a dedicated reporting or data warehouse scenario, not for IQP optimisation.
- ✓
Use approximate count distinct
Why this is correct
Approximate count distinct (APPROX_COUNT_DISTINCT) returns statistically accurate distinct counts using less memory and CPU than exact COUNT(DISTINCT), satisfying the stem's requirement for an Intelligent Query Processing feature that optimises query performance. It suits large-scale aggregations where slight variance is acceptable, reducing tempdb spills and speeding execution.
Go deeper
Related to this question
Learn chapter
Managing Identity and Access for Azure SQL
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
This DP-300 question is part of Courseiva's 574-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam 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.