DP-203 Practice Question: Secure, monitor, and optimize data storage and data processing
You manage an Azure Synapse Analytics dedicated SQL pool that contains a large fact table 'Orders' with 500 million rows. The table is hash-distributed on 'OrderDate' and uses a clustered columnstore index. Query performance has degraded over time. You check the system DMVs and find that the columnstore segments have poor quality, with many deleted rows and compressed rowgroups below 1 million rows. You need to improve query performance without blocking writes to the table. What should you do?
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
✓
Run ALTER INDEX REORGANIZE with COMPRESS_ALL_ROW_GROUPS = ON.
ALTER INDEX REORGANIZE with COMPRESS_ALL_ROW_GROUPS = ON is an online operation that compresses rowgroups with deleted rows without blocking writes. This directly addresses the poor columnstore segment quality. Option B is wrong because dropping and recreating the clustered columnstore index is an offline operation that blocks writes. Option C is wrong because changing the distribution key would require recreating the table, which is offline and does not specifically fix columnstore segment quality. Option D is wrong because ALTER INDEX REBUILD is also an offline operation that blocks writes.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Run ALTER INDEX REORGANIZE with COMPRESS_ALL_ROW_GROUPS = ON.
Why this is correct
Online operation that improves columnstore quality.
- ✗
Drop and recreate the clustered columnstore index.
Why it's wrong here
Offline and disrupts availability.
- ✗
Re-cluster the table using a different distribution key.
Why it's wrong here
Re-clustering changes distribution, not columnstore quality.
- ✗
Run ALTER INDEX REBUILD on the clustered columnstore index.
Why it's wrong here
REBUILD is an offline operation.
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.