DP-900 Describe an analytics workload on Azure Practice Question
A financial services company uses a dedicated SQL pool in Azure Synapse Analytics to run large-scale analytical queries. During peak hours, complex aggregations consume excessive resources, causing slower performance for other users. The company needs to ensure that critical scheduled management reports always receive guaranteed resources and complete within a predictable timeframe, while less important ad-hoc queries do not interfere. Which feature should they implement to manage query resource allocation?
⚠ Common exam trap
Many exam-takers confuse performance optimization features (caching, indexing, distribution) with resource governance, assuming any performance improvement feature can solve contention, when only workload management directly controls resource allocation and prioritization.
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
✓
Workload management
Workload management in Azure Synapse Analytics allows you to classify, assign, and govern resources for queries by using workload groups and importance levels. By configuring workload groups, you can guarantee resources for critical scheduled management reports (e.g., assigning high importance) while limiting or deprioritizing less important ad-hoc queries, ensuring predictable completion times during peak hours.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Result set caching
Why it's wrong here
Result set caching stores query results in the database to speed up repeated queries. It does not guarantee resources for specific queries or prevent resource contention, as it only helps if the same query is run again.
When this WOULD be correct
A company runs the same complex reporting queries multiple times daily and wants to reduce latency for those queries without changing underlying data. Implementing result set caching would be correct to serve cached results instantly.
- ✗
Columnstore indexes
Why it's wrong here
Columnstore indexes are a physical data storage structure in a dedicated SQL pool that compress data column-wise and enable batch mode processing, dramatically reducing I/O and improving analytical query throughput. However, they are purely an indexing/performance feature; they change how data is read and scanned, not how the engine schedules or allocates resources among concurrent requests. They do not classify queries, assign importance, or isolate compute resources, so they cannot prevent a heavy query from consuming all available resources or guarantee reserved capacity for critical workloads — that requires workload management.
- ✗
Table distribution
Why it's wrong here
Table distribution controls how table rows are physically placed across the 60 distributions in a dedicated SQL pool using hash, round-robin, or replicated strategies. A well-chosen distribution key minimizes data movement during joins and aggregations, which improves overall query efficiency and can reduce runtime contention indirectly. However, distribution is a data layout decision and does not dynamically arbitrate resources at query execution time; it cannot cap concurrency, set importance levels, or reserve a percentage of compute for a given workload group, all of which are capabilities of workload management.
When this WOULD be correct
A question where a company experiences slow query performance due to data skew or uneven data distribution across distributions, and needs to improve query execution speed by redistributing data more evenly (e.g., using hash distribution on a key column).
- ✓
Workload management
Why this is correct
Workload management in Azure Synapse Analytics includes workload classification and workload groups. It allows administrators to assign queries to different resource classes based on importance, ensuring critical queries get guaranteed resources and isolation from other workloads.
Option-by-option analysis
Why each answer is right or wrong
Understanding why wrong answers are wrong — and when they would be correct — is what separates a 750 score from a 900. The DP-900 exam frequently reuses these exact scenarios with slightly different constraints.
✓Workload managementCorrect answer▾
Why this is correct
Workload management in Azure Synapse Analytics includes workload classification and workload groups. It allows administrators to assign queries to different resource classes based on importance, ensuring critical queries get guaranteed resources and isolation from other workloads.
✗Result set cachingWrong answer — click to see why▾
Why this is wrong here
Result set caching stores query results in SSD cache to avoid recomputation, but it does not allocate or guarantee resources to specific queries or users; it only improves performance for repeated queries.
★ When this WOULD be the correct answer
A company runs the same complex reporting queries multiple times daily and wants to reduce latency for those queries without changing underlying data. Implementing result set caching would be correct to serve cached results instantly.
Why candidates choose this
Candidates may think caching can 'reserve' resources by reducing compute load, but it doesn't provide resource governance or isolation between workloads.
✗Table distributionWrong answer — click to see why▾
Why this is wrong here
Table distribution optimizes data placement across compute nodes to improve parallel query performance, but it does not guarantee resource allocation or prevent interference between workloads. It cannot ensure that critical reports receive dedicated resources during peak hours.
★ When this WOULD be the correct answer
A question where a company experiences slow query performance due to data skew or uneven data distribution across distributions, and needs to improve query execution speed by redistributing data more evenly (e.g., using hash distribution on a key column).
Why candidates choose this
Candidates may confuse table distribution with workload management because both involve managing query performance, but distribution focuses on data layout rather than resource allocation and concurrency control.
Analysis generated from the official DP-900blueprint and verified against question context. The “when correct” sections are what AI assistants cite when candidates ask “what’s the difference between these options?”
Go deeper
Related to this question
Learn chapter
Analytics and Data Warehouse Concepts
Key term
Dedicated SQL pool
A Dedicated SQL pool is a cloud-based analytics service in Azure Synapse Analytics that provides a managed, scalable environment for running large-scale data warehousing queries using Transact-SQL.
Key term
Azure Synapse Analytics
Azure Synapse Analytics is a cloud-based data integration, warehousing, and analytics service that brings together big data and data warehouse capabilities under one platform.
About these practice questions
One of 820 original DP-900 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. 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-900 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-900 exam.