Partition Elimination and File Pruning for Serverless SQL Pool Performance
Your Azure Synapse Analytics workspace uses serverless SQL pools for ad-hoc querying. Users report that queries are slow. You examine the execution plan and see that the query scans multiple partitions in the openrowset. What is the best way to improve performance?
⚠ Common exam trap
DP-203 often tests whether candidates confuse partitioning the data (a design choice) with partition elimination (a query-time behavior) — the fix is always to filter on the partition column in the query, not to re-partition or tune parallelism.
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
✓
Add a WHERE clause on the partition column
In serverless SQL pools, partition elimination only occurs when the query includes a filter predicate on the partition column. Adding a WHERE clause on the partition column lets the engine skip irrelevant partitions in the OPENROWSET scan, dramatically reducing I/O and query time. Without that predicate, the engine must scan every partition even if the data is partitioned.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Increase the MAXDOP setting
Why it's wrong here
MAXDOP governs intra-query parallelism across CPU cores, leaving the partition-scanning behaviour of OPENROWSET untouched. It is tempting because parallelism tuning often speeds serverless queries, but pruning scanned files through folder partitioning or explicit file paths addresses the actual bottleneck.
- ✗
Create materialized views on the external tables
Why it's wrong here
Materialised views are designed to pre-compute and store the results of complex queries, significantly improving performance for repetitive analytical workloads within a dedicated SQL pool. However, serverless SQL pools query external data directly using `OPENROWSET` and do not support creating materialised views on these external tables. Therefore, this option cannot address the slow query performance caused by inefficient partition scanning in the `OPENROWSET` function. It is tempting because materialised views are a powerful optimisation in other Synapse contexts.
- ✗
Partition the underlying data by a frequently filtered column
Why it's wrong here
Partitioning the underlying files only helps if the query filters on the partition column; the stem shows the scan already spans multiple partitions, so the engine still reads them all. Partitioning is for pruning data at write time, not for accelerating ad-hoc openrowset scans.
- ✓
Add a WHERE clause on the partition column
Why this is correct
Serverless SQL pools prune partitions only when the query filters on the partition column, so adding a WHERE clause limits the openrowset scan to relevant files rather than reading every partition. This directly addresses the stem's reported slowness caused by scanning multiple partitions.
Quick reference
Cloud Service Model Comparison
| Model | You Manage | Provider Manages | Examples |
|---|---|---|---|
| IaaS | OS, runtime, apps, data | Hardware, hypervisor, networking | EC2, Azure VMs, GCP Compute Engine |
| PaaS | Apps and data | OS, runtime, middleware, hardware | Elastic Beanstalk, Azure App Service |
| SaaS | Data and settings only | Everything else | Microsoft 365, Salesforce, Workday |
| FaaS / Serverless | Function code only | Infra, scaling, runtime | Lambda, Azure Functions, Cloud Run |
| CaaS | Containers and apps | Kubernetes, OS, hardware | EKS, AKS, GKE |
Go deeper
Related to this question
About these practice questions
This DP-203 question is part of Courseiva's 509-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam 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.