DP-203 Practice Question: Secure, monitor, and optimize data storage and data processing
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?
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, performance is improved by file pruning, which reduces the amount of data scanned. Adding a WHERE clause on the partition column allows the query engine to skip irrelevant partitions, thus reducing scan size. Option A is incorrect because MAXDOP controls parallelism, not data pruning. Option B is incorrect because materialized views are not supported in serverless SQL pools. Option C is incorrect because partitioning the underlying data helps, but the question asks for the best way to improve performance given the current query behavior; adding a WHERE clause on the partition column is the most direct and effective solution.
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 controls parallelism, not partition pruning.
- ✗
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
This is a valid optimization but requires data reorganization; filtering on existing partition column is more direct.
- ✓
Add a WHERE clause on the partition column
Why this is correct
Filtering on partition column enables partition elimination, reducing data scanned.
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 760-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 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.