DP-900 Describe an analytics workload on Azure Practice Question
A financial services company runs large-scale analytical queries on a dedicated SQL pool in Azure Synapse Analytics. They notice that during peak hours, complex aggregations consume excessive resources, causing slower queries from other users. They need to ensure that critical management reports always get enough resources and complete within a guaranteed time, while other less important queries do not starve them. Which feature should they implement?
⚠ Common exam trap
Many candidates confuse performance optimization features (caching, materialized views, indexes) with resource governance, assuming any performance improvement will solve concurrency and starvation issues, but only workload management provides explicit prioritization and resource allocation.
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 resources, and prioritize queries by creating workload groups and classifiers. By configuring a workload group for critical management reports with a higher importance and a guaranteed minimum resource percentage, you ensure those queries always get sufficient resources and complete within a guaranteed time, while less important queries are throttled and cannot starve the critical ones.
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 the output of completed queries so identical future queries can be returned without re-executing the underlying computation. While this reduces latency and compute usage for repeated queries, it does not manage resource allocation or concurrency between different queries. The first execution of a heavy analytical query still contends for resources, and cached results simply bypass the processing engine rather than prioritizing workloads.
When this WOULD be correct
A question where users frequently run the same complex aggregations and want to reduce latency and resource consumption for repeated queries, without needing to manage concurrency or prioritize workloads.
- ✗
Materialized views
Why it's wrong here
Materialized views physically precompute and store aggregated query results, allowing subsequent queries to read small prebuilt outputs instead of scanning and aggregating source tables. This improves query efficiency by reducing compute effort, but a materialized view is a data object, not a resource management mechanism. It does not assign CPU or memory budgets or enforce concurrency policies, so it cannot guarantee that critical queries receive sufficient resources during peak contention.
When this WOULD be correct
A question where the main concern is improving query performance for complex aggregations on large tables, without resource contention issues. For example: 'A company needs to speed up frequently run aggregate queries on a large fact table in Azure Synapse Analytics. Which feature should they implement?'
- ✓
Workload management
Why this is correct
Workload management is the correct choice because it directly governs how compute resources are allocated across queries in services like Azure Synapse Analytics dedicated SQL pools. Workload groups and classifiers let you assign CPU, memory, and concurrency slots to different workloads, so critical analytical queries get predictable performance even when the system is under heavy load. This is resource governance, not just a performance optimization.
- ✗
Columnstore index
Why it's wrong here
A columnstore index is a storage structure that compresses data and enables faster column-wise scans, which can dramatically speed up individual analytical queries. However, it does not arbitrate resources among competing queries or enforce concurrency limits. If multiple large queries run at once, the columnstore index does nothing to prioritize one over another, so it cannot ensure predictable performance for critical workloads.
When this WOULD be correct
A question where a company needs to improve query performance for large analytical workloads without changing resource allocation, such as 'Which feature reduces I/O and speeds up aggregations on large fact tables?'
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 is the correct choice because it directly governs how compute resources are allocated across queries in services like Azure Synapse Analytics dedicated SQL pools. Workload groups and classifiers let you assign CPU, memory, and concurrency slots to different workloads, so critical analytical queries get predictable performance even when the system is under heavy load. This is resource governance, not just a performance optimization.
✗Result-set cachingWrong answer — click to see why▾
Why this is wrong here
Result-set caching stores query results for repeated execution, reducing compute usage for identical queries, but it does not guarantee resource allocation or priority for critical reports during peak concurrency.
★ When this WOULD be the correct answer
A question where users frequently run the same complex aggregations and want to reduce latency and resource consumption for repeated queries, without needing to manage concurrency or prioritize workloads.
Why candidates choose this
Candidates may think caching can speed up critical reports by reusing results, but they overlook that it doesn't address resource contention or provide guaranteed execution time for new queries.
✗Materialized viewsWrong answer — click to see why▾
Why this is wrong here
Materialized views improve query performance by pre-computing aggregations, but they do not guarantee resource allocation or prevent resource contention during peak loads. The question requires a feature that ensures critical queries get sufficient resources, which workload management provides.
★ When this WOULD be the correct answer
A question where the main concern is improving query performance for complex aggregations on large tables, without resource contention issues. For example: 'A company needs to speed up frequently run aggregate queries on a large fact table in Azure Synapse Analytics. Which feature should they implement?'
Why candidates choose this
Candidates may think that materialized views reduce resource consumption by pre-computing results, thus indirectly helping with resource contention. However, they do not provide guaranteed resource allocation or isolation between workloads.
✗Columnstore indexWrong answer — click to see why▾
Why this is wrong here
Columnstore indexes improve query performance through better compression and batch processing, but they do not provide resource governance or prioritization to guarantee that critical reports get sufficient resources during peak loads.
★ When this WOULD be the correct answer
A question where a company needs to improve query performance for large analytical workloads without changing resource allocation, such as 'Which feature reduces I/O and speeds up aggregations on large fact tables?'
Why candidates choose this
Candidates know columnstore indexes boost analytical query speed, so they mistakenly think faster queries alone can solve resource contention, overlooking the need for workload isolation and prioritization.
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
Courseiva writes every DP-900 question from scratch — 820 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or 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-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.