DP-203 Design and implement data storage Practice Question
You have an Azure Synapse Analytics dedicated SQL pool with a large fact table partitioned by date. As data grows, query performance on recent data degrades. You need to improve performance for queries filtering on the current month without affecting queries on older data. What should you do?
⚠ Common exam trap
It's easy for candidates to confuse partition switching with partition elimination or index maintenance, thinking that updating statistics or scaling resources will solve the performance issue, when the real bottleneck is the sheer volume of data in the table that must be scanned for queries on recent data.
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
✓
Implement partition switching to move older partitions to a different filegroup with slower storage
Partition switching allows you to efficiently move older partitions to a different filegroup (e.g., slower or compressed storage) without affecting queries on recent data. This reduces the active data volume in the fact table, improving scan performance for queries filtering on the current month. The operation is metadata-only and does not require data movement, so it does not impact ongoing queries on the remaining partitions.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Implement partition switching to move older partitions to a different filegroup with slower storage
Why this is correct
Partition switching is correct because it allows moving older partitions to a different filegroup, reducing the data scanned for recent queries without affecting older data. It is a metadata-only operation.
- ✗
Increase the service level objective (SLO) of the dedicated SQL pool
Why it's wrong here
Increasing the SLO scales resources but does not reduce the amount of data scanned; it may help but is not the best solution for reducing data volume for recent queries.
- ✗
Change the table distribution to round-robin
Why it's wrong here
Changing distribution to round-robin would distribute data evenly but not improve performance for date-filtered queries; it may actually harm performance for queries that benefit from hash distribution on a join key.
- ✗
Update statistics on the table for the current month partition
Why it's wrong here
Updating statistics helps the optimizer but does not reduce the volume of data scanned; the real issue is the size of the fact table, not stale statistics.
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.