Question 220
A database architect reviews two implementation risks. A team raises maximum database connections indiscriminately while each client holds idle sessions and SQL workers become exhausted. A warehouse-style SQL workload repeatedly allocates huge temporary sort results and experiences tempdb contention. What is the most appropriate plan for this situation?
Explanation
Correct Answer
First, unbounded client sessions can consume resources without improving transaction throughput. Second, tempdb pressure can reflect workload design rather than an external network problem.
Incorrect Answers
Fix connection pooling and idle session handling and tune limits only after measuring concurrent capacity; compare the measured demand with the database service tier and scale or optimize according to the actual bottleneck: tempdb pressure can reflect workload design rather than an external network problem.
Load-test representative concurrency and distinguish resource saturation from transaction blocking before choosing a fix; inspect temporary storage usage and query operators, then optimize sort/worktable demand and supported tempdb configuration: unbounded client sessions can consume resources without improving transaction throughput.
Fix connection pooling and idle session handling and tune limits only after measuring concurrent capacity.



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