DP-203 Practice Question: Secure, monitor, and optimize data storage and data processing
You have an Azure Synapse Analytics dedicated SQL pool that contains a large fact table named FactSales. The table is partitioned by date and has a clustered columnstore index. You notice that queries filtering on a specific date range are slow. You need to improve query performance for these queries. What should you do?
⚠ Common exam trap
The trap here is assuming that index maintenance or distribution changes will fix slow date-range queries, when the actual issue is often lack of partition elimination.
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
✓
Ensure that the queries use a predicate on the partitioning column so that partition elimination occurs.
Partition elimination is a key performance feature in dedicated SQL pools. When queries filter on the partitioning column with a sargable predicate, the engine can prune partitions and read only the relevant data. This reduces the amount of data scanned and speeds up queries. Ensuring that queries are written to take advantage of partition elimination is the most direct solution for slow date-range queries.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Rebuild the clustered columnstore index on the FactSales table.
Why it's wrong here
Rebuilding the columnstore index can improve compression and query performance, but it is a maintenance operation that may not address the specific issue of partition elimination. If the queries are slow due to scanning all partitions, rebuilding alone will not help. The root cause is likely missing partition elimination, so a targeted approach is needed.
- ✗
Create a nonclustered index on the date column.
Why it's wrong here
A nonclustered index on the date column might help, but in a dedicated SQL pool with a clustered columnstore index, nonclustered indexes are often less effective for large scans. The primary mechanism for date-range performance is partition elimination, which does not require an additional index and is more efficient for large fact tables.
- ✓
Ensure that the queries use a predicate on the partitioning column so that partition elimination occurs.
Why this is correct
Partition elimination allows the query optimizer to skip partitions that do not contain relevant data. If queries filter on the partitioning column (e.g., date), and the predicate is sargable, the engine can scan only the necessary partitions. This directly reduces I/O and improves performance for date-range queries.
- ✗
Switch the table distribution to round-robin.
Why it's wrong here
Changing the distribution to round-robin would move data across distributions but does not address partition elimination. Round-robin is typically used for staging tables, not large fact tables. For a fact table, hash distribution on a key and partitioning are preferred for performance, so this change could degrade performance.
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.