DP-203 Design and implement data storage Practice Question
A company uses Azure Synapse Analytics dedicated SQL pool to store sales data. The sales table is partitioned by month and has a clustered columnstore index. Over time, the performance of queries filtering on a specific month has degraded. The data engineer suspects high rowgroup elimination. Which action should be taken to improve performance?
⚠ Common exam trap
Many candidates confuse rowgroup elimination (a columnstore physical storage concept) with partition elimination (a table design concept), and incorrectly choose partition-related actions like dropping partitions or updating statistics instead of addressing the columnstore index fragmentation directly.
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
✓
Reorganize or rebuild the columnstore index on the table.
Reorganizing or rebuilding the columnstore index compresses fragmented rowgroups and merges small rowgroups into optimal sizes (typically 102,400 rows per rowgroup). This directly addresses the degraded rowgroup elimination: when rowgroups are too small or fragmented, the engine cannot efficiently skip entire rowgroups during partition-level scans, causing more I/O and slower performance.
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 table distribution to hash-distributed on the partition key.
Why it's wrong here
Distribution does not affect rowgroup fragmentation.
- ✓
Reorganize or rebuild the columnstore index on the table.
Why this is correct
Reorganizing or rebuilding consolidates small rowgroups, improving partition elimination.
- ✗
Drop and recreate the affected partitions.
Why it's wrong here
This is disruptive and not the recommended maintenance approach.
- ✗
Update statistics on the partitioned column.
Why it's wrong here
Statistics help query optimization but do not fix rowgroup fragmentation.
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.