DP-300 Practice Question: Monitor, configure, and optimize database resources
You are tuning an Azure SQL Database that uses the General Purpose service tier. You notice that a specific query has a high average CPU time but a low average elapsed time. Query Store shows that the query plan uses a Hash Match (Aggregate) operator. You need to reduce the CPU consumption of this query. What should you do?
⚠ Common exam trap
The trap here is focusing on index or statistics changes when the high CPU is caused by the aggregation operator processing too many rows, not by missing indexes or outdated statistics.
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
✓
Rewrite the query to reduce the number of rows processed before aggregation.
A Hash Match (Aggregate) operator builds a hash table of group values and is CPU-intensive when processing many rows. The most effective way to reduce its CPU cost is to reduce the number of rows that reach the aggregate. This can be done by filtering earlier, pre-aggregating, or using indexed views. While indexes and statistics can influence plan choice, they do not directly reduce the CPU work of the hash aggregate if the row count remains high.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Update statistics on the involved tables.
Why it's wrong here
Updating statistics can improve cardinality estimates and sometimes lead to a better plan, but it does not directly reduce the CPU cost of a Hash Match (Aggregate) operator. If the query inherently requires aggregation over a large unsorted rowset, the hash aggregate will still be used and remain CPU-intensive. Statistics updates are a general maintenance task, not a targeted fix for high CPU due to hash aggregation.
- ✗
Force a plan that uses a Stream Aggregate operator.
Why it's wrong here
Stream Aggregate can be more CPU-efficient than Hash Match when the input is sorted on the grouping columns, but forcing a plan is risky and may lead to regressions. The optimizer chooses Hash Match typically because the input is not sorted or the estimated cost is lower. Without providing sorted input (e.g., via an index), forcing Stream Aggregate may introduce a sort operator that consumes even more CPU. Plan forcing should be a last resort after other optimizations.
- ✗
Create a covering index for the query's join and filter columns.
Why it's wrong here
A covering index can reduce IO and sometimes CPU by avoiding key lookups, but the high CPU time with a Hash Match (Aggregate) indicates that the aggregation itself is CPU-intensive. A covering index may not eliminate the hash aggregate if the query groups by a non-indexed column or if the optimizer still chooses hash aggregate. The primary CPU consumer here is the hash aggregate, not missing indexes, so creating an index is not the most direct fix.
- ✓
Rewrite the query to reduce the number of rows processed before aggregation.
Why this is correct
The high CPU time with a Hash Match (Aggregate) suggests that the aggregation is processing a large number of rows. Reducing the row count earlier in the query (e.g., by adding more selective filters, pre-aggregating in a subquery, or using a indexed view) decreases the work the hash aggregate must perform. This directly lowers CPU consumption. Other options like index changes or plan forcing may help but are less direct and may not address the root cause of excessive rows entering the aggregate.
Go deeper
Related to this question
Learn chapter
Implementing High Availability for Azure SQL Databases
Key term
Azure SQL Performance Tuning
Azure SQL Performance Tuning is the process of optimizing the speed and efficiency of queries and database operations in Microsoft Azure SQL Database or SQL Managed Instance to reduce latency and improve throughput.
Key term
Query Store
Query Store is a built-in SQL Server feature that captures and stores a history of query execution plans and performance data for easy monitoring and troubleshooting.
About these practice questions
Courseiva writes every DP-300 question from scratch — 574 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 and reviewed by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
Last reviewed September 2026 · checked against the official Microsoft exam blueprint
This DP-300 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-300 exam.