DP-900 Practice Question: Identify considerations for relational data on Azure
A data warehouse team in Azure Synapse Analytics notices query performance degradation on a large fact table. The table is partitioned by date and has a clustered columnstore index. Which action is most likely to improve performance?
⚠ Common exam trap
Candidates often confuse index maintenance (reorganize/rebuild) with statistics updates or distribution changes, mistakenly believing that stale statistics or a different distribution method are the primary causes of performance degradation on a large, partitioned fact table with a clustered columnstore index.
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 the clustered columnstore index
Reorganizing the clustered columnstore index (option C) is the most likely action to improve performance because, over time, columnstore indexes can become fragmented due to data modifications (inserts, updates, deletes). Reorganizing the index physically recompresses the data into optimal rowgroups, removing deleted rows and merging small rowgroups, which directly improves query scan efficiency and reduces I/O.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Update statistics on the fact table
Why it's wrong here
Updating statistics on the fact table refreshes row-count and density estimates used by the query optimizer, which can help choose better join and filter strategies. However, it does not physically amend the storage layout; fragmented columnstore rowgroups, deleted rows, and poorly compressed segments remain untouched. Because the observed symptom is likely slow scans caused by suboptimal columnstore segments, statistics maintenance alone will not reclaim that performance loss.
- ✗
Drop and recreate the partition boundaries
Why it's wrong here
Dropping and recreating partition boundaries is a metadata operation used to add, merge, or move data ranges for partition-switching scenarios, not a rowgroup-level maintenance action. It does not compress open deltastores, merge small compressed segments, or purge deleted rows that cause fragmentation. Moreover, rebuilding boundaries can trigger implicit data movement and block concurrent loads, making performance worse without addressing the root cause of fragmented columnstore storage.
- ✓
Reorganize the clustered columnstore index
Why this is correct
Reorganizing the clustered columnstore index invokes the tuple mover to force closed rowgroups into compressed segments, merge small compressed segments, and eliminate logically deleted rows. This compacts the physical storage, improves segment density and min/max statistics, and accelerates scan pruning and predicate evaluation. It is the correct, online operation for resolving columnstore fragmentation in Azure Synapse Analytics while avoiding a full rebuild.
- ✗
Change the distribution to ROUND_ROBIN
Why it's wrong here
Changing the distribution to ROUND_ROBIN alters how rows are spread across the compute nodes, which may improve distribution skew for some workloads but has no impact on the physical condition of individual columnstore segments. It requires a table rebuild (e.g., CREATE TABLE AS SELECT or ALTER TABLE REBUILD), causing substantial I/O and blocking, and it can even introduce shuffle overhead for joins. Since the problem is segment-level fragmentation, redistribution is an expensive, unrelated remedy.
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Data warehouse
A data warehouse is a central repository that stores large amounts of structured data from multiple sources, optimized for querying and analysis rather than day-to-day transactions.
Key term
Data
Data is raw, unprocessed information, like numbers, words, or measurements, that can be stored, processed, and analyzed by computers.
About these practice questions
Courseiva writes every DP-900 question from scratch — 820 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-900 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-900 exam.