Secure, monitor, and optimize data storage and data processing →mediumMultiple ChoiceObjective-mapped
DP-203 Practice Question: Secure, monitor, and optimize data storage and data processing
Your team is troubleshooting slow query performance on a dedicated SQL pool in Azure Synapse Analytics. The query uses a hash-distributed fact table with 60 distributions. After reviewing the execution plan, you notice a high number of data moves. Which action would most likely reduce data movement?
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
✓
Redistribute the fact table on the join column using hash distribution.
Redistributing the fact table on the join column using hash distribution aligns the distribution key with the join column. This ensures that rows with the same join key value are stored on the same distribution, minimizing data movement during joins. Option A is wrong because round-robin distribution places rows randomly, which actually increases data movement for joins. Option B is wrong because while updating statistics helps the optimizer choose better plans, it does not directly reduce data movement. Option C is wrong because increasing the number of distributions does not change the alignment of the distribution key with the join column, so data movement remains high.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Change the distribution type to round-robin.
Why it's wrong here
Changing to round-robin places rows randomly across distributions, which would increase data movement during joins because no collocation exists.
- ✗
Update statistics on all columns used in joins.
Why it's wrong here
Updating statistics improves query optimization but does not structurally reduce data movement; the distribution scheme remains unchanged.
- ✗
Increase the number of distributions to 120.
Why it's wrong here
Increasing the number of distributions does not align the distribution key with the join column, so data movement remains high.
- ✓
Redistribute the fact table on the join column using hash distribution.
Why this is correct
Redistributing the fact table on the join column using hash distribution ensures collocation of matching rows, directly reducing data movement.
Go deeper
Related to this question
About these practice questions
Courseiva writes every DP-203 question from scratch — 760 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-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.