Question 590
A database architect reviews two implementation risks. A developer optimizes a JOIN by adding indexes without checking whether the actual execution plan spends time on sorting and spilling. A data export uses a hash join spilling heavily to tempdb because the granted memory is much smaller than the required working set. Which course of action addresses the stated issues?
Explanation
Correct Answer
First, query tuning should follow measured bottlenecks rather than assuming every slowdown is a missing index. Second, sort and hash spills often indicate memory-grant or cardinality problems that can dominate latency.
Incorrect Answers
Review the plan's spills, cardinality and memory grant behavior and optimize or resource-plan the measured cause; do not Capture an actual execution plan and runtime metrics before targeting the demonstrated operators: capture an actual execution plan and runtime metrics before targeting the demonstrated operators.
Capture an actual execution plan and runtime metrics before targeting the demonstrated operators; inspect statistics freshness and cardinality estimates, then update relevant statistics under a controlled performance test: sort and hash spills often indicate memory-grant or cardinality problems that can dominate latency.
Compare representative plans with and without the hint and remove it if it forces an unsuitable access path; review the plan's spills, cardinality and memory grant behavior and optimize or resource-plan the measured cause: query tuning should follow measured bottlenecks rather than assuming every slowdown is a missing index.



Developing AI-Enabled Database Solutions (DP-800) Practice Tests