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
Outdated or missing statistics can cause the optimizer to choose suboptimal access methods like table scans.
- ✗
The tables are distributed using round-robin distribution.
Why it's wrong here
Round-robin distribution distributes data evenly but does not directly cause table scans.
- ✗
Result-set caching is disabled.
Why it's wrong here
Result-set caching improves repeated query performance but does not affect the access method.
- ✗
The resource class for the user is set to smallrc.
Why it's wrong here
Resource class affects concurrency and memory allocation, not the access method.
Go deeper
Related to this question
About these practice questions
One of 760 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.