mediumMultiple Select
DP-203 Practice Question: A company uses Azure Synapse Analytics dedicated…
A company uses Azure Synapse Analytics dedicated SQL pool for a data warehouse. They notice that some queries are using more memory than expected, causing resource contention. Which TWO actions should they take to diagnose and optimize memory usage?
⚠ Common exam trap
Many candidates confuse scaling up the DWU (Option C) as a diagnostic action, but it is a reactive scaling measure that does not help identify which queries are causing the memory issue, whereas querying the DMV and adjusting resource classes are targeted diagnostic and optimization steps.
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
✓
Increase the resource class for the users running the heavy queries.
Option B is correct because in a dedicated SQL pool, the resource class assigned to a user directly controls the memory grant (and concurrency slots) available to their queries; raising the resource class for users running memory-heavy queries gives those queries a larger memory allocation, reducing memory pressure and contention. Option D is correct because sys.dm_pdw_exec_requests is the DMV that exposes per-request execution details, including resource allocation and memory grant information, so querying it lets administrators identify exactly which queries are consuming excessive memory before tuning them. Option A is not appropriate because result-set caching is a feature of serverless SQL pools and does not address memory grants in a dedicated SQL pool. Option C is not the right first step because scaling DWU changes overall compute capacity and cost rather than diagnosing or right-sizing per-query memory grants. Option E is not relevant because rebuilding clustered columnstore indexes addresses data compression and segment quality, not query memory grant consumption.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Enable result-set caching.
Why it's wrong here
Result-set caching stores query output to speed repeated identical queries; it does not expose memory grants, spills, or skew, so it cannot diagnose contention. It is tempting because caching reduces repeated query cost, and would be correct for improving performance of frequently rerun identical reports rather than investigating memory pressure.
- ✓
Increase the resource class for the users running the heavy queries.
Why this is correct
Increasing the resource class allocates more memory per query, directly relieving contention caused by heavy queries exceeding their default allocation. This satisfies the memory-pressure constraint by giving those queries greater dedicated memory within the dedicated SQL pool.
- ✗
Scale up the DWU setting.
Why it's wrong here
Scaling up increases overall resources but does not target specific query memory issues.
- ✓
Query the sys.dm_pdw_exec_requests DMV to identify queries with high memory grants.
Why this is correct
Querying sys.dm_pdw_exec_requests reveals per-request memory grant figures, exposing which queries request excessive grants and drive contention in the dedicated SQL pool. This directly satisfies the need to diagnose memory usage before tuning, since the DMV reports granted versus used memory per active query.
- ✗
Rebuild clustered columnstore indexes.
Why it's wrong here
Rebuilding clustered columnstore indexes improves compression and scan efficiency but does not expose per-query memory grants or spills, which is what diagnosing contention requires. It is tempting because index health affects query performance, and it would be correct when fragmentation or poor rowgroup quality is the measured cause.
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 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.