Question 98 of 760
Secure, monitor, and optimize data storage and data processing →mediumMultiple ChoiceObjective-mapped
Partition Elimination and File Pruning for Serverless SQL Pool Performance
Your team uses Azure Synapse Analytics serverless SQL pool to query data in Azure Data Lake Storage Gen2. You notice that queries are running slower than expected. You need to improve query performance by reducing the amount of data scanned. Which two features should you implement? (Select two.)
Quick Answer
Both partition elimination and file pruning attack the same underlying cost driver in serverless SQL pool: you are billed and slowed down based on how much data actually gets read from Data Lake Storage, so the fastest way to speed up and cheapen a query is to have it read fewer files in the first place, rather than optimizing how it processes the files it does read. File pruning, specifically by naming exact file paths in an OPENROWSET query, lets you tell the engine directly which files are relevant, so it never even opens the irrelevant ones. Partition elimination achieves a similar effect at a folder level, letting the engine skip entire partitions that a query's filters rule out. The wrong options fail for related reasons: result-set caching only helps repeat runs of an identical query, since the first execution still scans the full data; auto-optimize is a Delta Lake maintenance feature that does not apply to serverless SQL pool; and materialized views are not supported in serverless SQL pool at all. Whenever a serverless SQL pool question is about reducing data scanned specifically, look for answers that limit which files or partitions get touched, not answers about caching results or optimizing processing after the data is already read.
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
✓
Partition the data in the data lake and use partition elimination in queries.
Options A and E are correct. Partition elimination (A) reduces data scanned by skipping irrelevant partitions. File pruning (E) reduces data scanned by reading only specified files via OPENROWSET. Option B is wrong because result-set caching caches query results but does not reduce the amount of data scanned on the first execution. Option C is wrong because auto-optimize is a feature for Delta Lake tables and is not applicable to serverless SQL pool queries. Option D is wrong because materialized views are not supported in serverless SQL pool, and even if they were, they would not reduce the data scanned by the original query.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Partition the data in the data lake and use partition elimination in queries.
Why this is correct
Partition elimination allows the query to skip irrelevant partitions, reducing data scanned.
- ✗
Enable result-set caching for the serverless SQL pool.
Why it's wrong here
Result-set caching speeds up repeated queries but does not reduce data scanned for the first execution.
- ✗
Enable auto-optimize on the Delta Lake tables.
Why it's wrong here
Auto-optimize is a Delta Lake feature not applicable to serverless SQL pool queries on Parquet/CSV.
- ✗
Create materialized views on the serverless SQL pool.
Why it's wrong here
Materialized views improve performance for repeated queries but do not reduce the amount of data scanned when the view is created.
- ✓
Use file pruning by specifying file paths in the OPENROWSET query.
Why this is correct
File pruning allows the query to read only the necessary files, 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 |
About these practice questions
Courseiva creates original exam-style practice questions with explanations and wrong-answer analysis. It does not publish real exam questions, exam dumps, or protected exam content. Learn why practice questions differ from exam dumps →
Same concept, more angles
1 more way this is tested on DP-203
These questions test the same concept from different angles. Work through them to make sure you can recognise it however the exam phrases it.
Variation 1. 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?
hard- A.Increase the MAXDOP setting
- B.Create materialized views on the external tables
- C.Partition the underlying data by a frequently filtered column
- ✓ D.Add a WHERE clause on the partition column
Why D: 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.
Last reviewed: Jun 21, 2026
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.
Question Discussion
Share a tip, memory trick, or ask about the reasoning behind this question. Do not post real exam questions, leaked content, braindumps, or copyrighted exam material. Comments are moderated and may be removed without notice.
Sign in to join the discussion.