DP-203 Develop data processing Practice Question
Your organization is using Azure Synapse Analytics dedicated SQL pool. You notice that queries are running slower than expected. Upon reviewing the execution plans, you see that some queries are performing table scans instead of seeks on large fact tables. What is the most likely cause?
⚠ Common exam trap
A common mix-up: candidates confuse performance issues caused by distribution type or resource class with the optimizer's reliance on statistics, overlooking that even with optimal distribution and sufficient resources, stale statistics force scans instead of seeks.
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
✓
The statistics on the tables are outdated or missing.
Outdated or missing statistics prevent the Azure Synapse Analytics dedicated SQL pool query optimizer from accurately estimating row counts and data distribution. Without reliable statistics, the optimizer may incorrectly choose a table scan over a more efficient index seek or partition elimination, leading to slower query performance on large fact tables.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
The statistics on the tables are outdated or missing.
Why this is correct
Dedicated SQL pool relies on statistics to estimate cardinality and choose seeks over scans. Outdated or missing statistics mislead the optimiser into underestimating selectivity, producing full table scans on large fact tables. Updating statistics restores accurate costing and enables index seeks.
- ✗
The tables are distributed using round-robin distribution.
Why it's wrong here
Round-robin distribution spreads rows evenly without clustering on join or filter columns, so the engine cannot eliminate partitions and scans. It is tempting because round-robin suits staging loads, but hash distribution on frequently joined columns enables partition elimination and seeks. Scans here indicate missing or misaligned indexes and statistics.
- ✗
Result-set caching is disabled.
Why it's wrong here
Result-set caching stores completed query results and has no bearing on whether the optimiser chooses a scan or a seek within a single execution. It is tempting because caching improves repeated-query latency, but scans stem from missing or unusable indexes, statistics, or partitioning that prevent seek operations on large fact tables.
- ✗
The resource class for the user is set to smallrc.
Why it's wrong here
Smallrc grants the smallest memory and concurrency allocation per query, affecting resources rather than access paths; the optimiser still chooses scans when no suitable index or statistics exist. Smallrc is correct for lightweight concurrent queries, but scan-versus-seek decisions depend on indexing, distribution, and statistics, not resource class.
Go deeper
Related to this question
About these practice questions
One of 509 original DP-203 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-203 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-203 exam.