DP-203 Practice Question: Secure, monitor, and optimize data storage and data processing
You are optimizing an Azure Synapse Analytics dedicated SQL pool that experiences performance degradation during peak hours. You need to reduce query execution time by improving data distribution and reducing data movement. Which two actions should you take? (Choose two.)
⚠ Common exam trap
The trap here is assuming that scaling up or partitioning automatically solves data movement issues, when the core solution is to choose appropriate distribution strategies for fact and dimension tables.
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
✓
Convert small dimension tables to replicated tables.
Hash distributing large fact tables on a high-cardinality join column colocates matching rows, minimizing data movement during joins. Replicating small dimension tables eliminates the need to shuffle them across nodes. Together, these actions directly reduce data movement and improve query performance in a dedicated SQL pool. Other options either add resources without addressing distribution or introduce inappropriate indexing.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Convert small dimension tables to replicated tables.
Why this is correct
Replicating small dimension tables places a full copy on every compute node, eliminating the need to shuffle data during joins with large fact tables. This reduces data movement and speeds up queries. It is a best practice for dimension tables under a certain size, typically a few gigabytes, and directly contributes to performance improvement.
- ✓
Change the distribution of large fact tables to hash distribution on a high-cardinality column frequently used in joins.
Why this is correct
Hash distributing large fact tables on a high-cardinality column that is commonly used in joins ensures that rows with the same join key are colocated on the same distribution. This minimizes data movement during joins, which is a major performance bottleneck. It directly addresses the goal of reducing query execution time by optimizing data distribution.
- ✗
Implement table partitioning on a date column in the fact tables.
Why it's wrong here
Partitioning can improve query performance by enabling partition elimination when queries filter on the partitioning column. However, it does not directly reduce data movement during joins, which is the primary concern here. Partitioning is complementary but does not address the distribution and data movement issues described. Thus, it is not one of the two best actions.
- ✗
Create a clustered index on all columns of the fact tables.
Why it's wrong here
Creating a clustered index on all columns is not feasible because a clustered index can only be defined on one set of columns, and it would not be a columnstore index. Moreover, dedicated SQL pools perform best with clustered columnstore indexes for large fact tables. This action would likely degrade performance and increase storage, not reduce data movement.
- ✗
Increase the number of distributions by scaling the dedicated SQL pool to a higher service level.
Why it's wrong here
Scaling to a higher service level increases the number of compute nodes and distributions, which can improve throughput, but it also increases cost and does not inherently optimize data distribution or reduce data movement. The scenario asks for actions to improve distribution and reduce data movement, not just add resources. Scaling alone may not address the root cause.
Go deeper
Related to this question
About these practice questions
Courseiva writes every DP-203 question from scratch — 509 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 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-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.