100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 550
A report performs thousands of per-row scalar function calls. The team selects: Refactor expensive row-by-row logic set-wise when possible. Which implementation action puts this design into practice?
- Query sys.dm_exec_requests and wait information
- Replace per-row scalar UDF with join/apply or inline form
- Join on full composite business key
- Inspect WRITELOG waits and log file latency
Explanation
Correct Answer
B:Replace per-row scalar UDF with join/apply or inline form. This is the concrete setup for refactor expensive row-by-row logic set-wise when possible, and compare cpu and elapsed time under matched workload supplies a direct confirmation.
Incorrect Answers
A:Query sys.dm_exec_requests and wait information. This concerns two transactions update the same stock row and block each other. Here, replace per-row scalar UDF with join/apply or inline form.
C:Join on full composite business key. This concerns a join unexpectedly multiplies report rows. Here, replace per-row scalar UDF with join/apply or inline form.
D:Inspect WRITELOG waits and log file latency. This concerns a high-write SQL server suffers excessive transaction log latency. Here, replace per-row scalar UDF with join/apply or inline form.
Question 551
A report performs thousands of per-row scalar function calls. After implementing the design, which check most directly validates the expected behavior?
- Measure compression, elapsed time and batch mode
- Compare CPU and latency before/after scaling
- Run concurrent update and verify first transaction sees stable row
- Compare CPU and elapsed time under matched workload
Explanation
Correct Answer
D:Compare CPU and elapsed time under matched workload. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that row-by-row udf evaluation dominates cost.
Incorrect Answers
A:Measure compression, elapsed time and batch mode. This is relevant when an aggregate report over a huge fact table must improve throughput. Instead, compare CPU and elapsed time under matched workload.
B:Compare CPU and latency before/after scaling. This concerns more resources for predictable query load. Instead, compare CPU and elapsed time under matched workload.
C:Run concurrent update and verify first transaction sees stable row. This concerns preventing non-repeatable reads during decision. Instead, compare CPU and elapsed time under matched workload.
Question 552
A report performs thousands of per-row scalar function calls. A test instead finds: Plan shows heavy CPU per input row. Which underlying issue best explains this finding?
- Row-by-row UDF evaluation dominates cost
- Blocking session chain was not investigated
- Row-versioned isolation was not enabled or hints override it
- Available compute or service tier remains undersized
Explanation
Correct Answer
A:Row-by-row UDF evaluation dominates cost. It accounts for the specific observed failure: plan shows heavy cpu per input row. The intended solution is refactor expensive row-by-row logic set-wise when possible with the required setup.
Incorrect Answers
B:Blocking session chain was not investigated. This is relevant when an unrelated index rebuild is blamed for row lock waits. Here, row-by-row UDF evaluation dominates cost.
C:Row-versioned isolation was not enabled or hints override it. This concerns readers still wait on writer locks under normal select. Here, row-by-row UDF evaluation dominates cost.
D:Available compute or service tier remains undersized. This concerns cpu remains pinned after index tuning. Here, row-by-row UDF evaluation dominates cost.
Question 553
An Azure SQL database needs more resources for predictable query load. Which database design or engineering approach most directly meets this requirement?
- Enable Query Store and capture representative waits
- Evaluate service tier or compute scaling
- Update relevant statistics after evidence of drift
- Provide index ordering that supports ORDER BY
Explanation
Correct Answer
B:Evaluate service tier or compute scaling. The implementation is select supported vcore compute tier based on measured bottlenecks; success can be checked by compare cpu and latency before/after scaling. The method addresses the stated data and workload constraint.
Incorrect Answers
A:Enable Query Store and capture representative waits. This is relevant when a service has intermittent timeout and unknown query cause. Instead, evaluate service tier or compute scaling.
C:Update relevant statistics after evidence of drift. This concerns a high-traffic analytics query runs slowly after statistics become stale. Instead, evaluate service tier or compute scaling.
D:Provide index ordering that supports ORDER BY. This concerns a t-SQL query sorts millions of rows unnecessarily. Instead, evaluate service tier or compute scaling.
Question 554
An Azure SQL database needs more resources for predictable query load. The team selects: Evaluate service tier or compute scaling. Which implementation action puts this design into practice?
- Create key order matching WHERE and ORDER BY
- Select supported vCore compute tier based on measured bottlenecks
- Replace per-row scalar UDF with join/apply or inline form
- Force a known-good Query Store plan when supported
Explanation
Correct Answer
B:Select supported vCore compute tier based on measured bottlenecks. This is the concrete setup for evaluate service tier or compute scaling, and compare cpu and latency before/after scaling supplies a direct confirmation.
Incorrect Answers
A:Create key order matching WHERE and ORDER BY. This concerns a t-SQL query sorts millions of rows unnecessarily. Instead, select supported vCore compute tier based on measured bottlenecks.
C:Replace per-row scalar UDF with join/apply or inline form. This concerns a report performs thousands of per-row scalar function calls. Instead, select supported vCore compute tier based on measured bottlenecks.
D:Force a known-good Query Store plan when supported. This concerns reusing last month's stable query plan after regression. Instead, select supported vCore compute tier based on measured bottlenecks.
Question 555
An Azure SQL database needs more resources for predictable query load. After implementing the design, which check most directly validates the expected behavior?
- Compare P95 transaction commit time and I/O metrics
- Compare CPU and latency before/after scaling
- Correlate oldest active snapshot with version store growth
- Compare CPU and elapsed time under matched workload
Explanation
Correct Answer
B:Compare CPU and latency before/after scaling. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that available compute or service tier remains undersized.
Incorrect Answers
A:Compare P95 transaction commit time and I/O metrics. This concerns a high-write SQL server suffers excessive transaction log latency. Instead, compare CPU and latency before/after scaling.
C:Correlate oldest active snapshot with version store growth. This concerns a server experiences severe tempdb waits from row versioning. Instead, compare CPU and latency before/after scaling.
D:Compare CPU and elapsed time under matched workload. This concerns a report performs thousands of per-row scalar function calls. Instead, compare CPU and latency before/after scaling.
Question 556
An Azure SQL database needs more resources for predictable query load. A test instead finds: CPU remains pinned after index tuning. Which underlying issue best explains this finding?
- Row-versioned isolation was not enabled or hints override it
- Query performance history was not captured
- No ordered access path supports requested sort
- Available compute or service tier remains undersized
Explanation
Correct Answer
D:Available compute or service tier remains undersized. It accounts for the specific observed failure: cpu remains pinned after index tuning. The intended solution is evaluate service tier or compute scaling with the required setup.
Incorrect Answers
A:Row-versioned isolation was not enabled or hints override it. This concerns readers still wait on writer locks under normal select. Here, available compute or service tier remains undersized.
B:Query performance history was not captured. This concerns no historical plans exist for the incident window. Here, available compute or service tier remains undersized.
C:No ordered access path supports requested sort. This concerns tempdb spills occur during final sorting. Here, available compute or service tier remains undersized.
Question 557
A service has intermittent timeout and unknown query cause. Which database design or engineering approach most directly meets this requirement?
- Enable Query Store and capture representative waits
- Use REPEATABLE READ or equivalent locks for required scope
- Inspect Query Store plan history and regressions
- Use Query Store plan forcing cautiously with monitoring
Explanation
Correct Answer
A:Enable Query Store and capture representative waits. The implementation is configure query store capture policy; success can be checked by find regressed query runtime and wait category. The method addresses the stated data and workload constraint.
Incorrect Answers
B:Use REPEATABLE READ or equivalent locks for required scope. This concerns preventing non-repeatable reads during decision. Instead, enable Query Store and capture representative waits.
C:Inspect Query Store plan history and regressions. This is relevant when an API has latency spikes after an index disappears. Instead, enable Query Store and capture representative waits.
D:Use Query Store plan forcing cautiously with monitoring. This concerns reusing last month's stable query plan after regression. Instead, enable Query Store and capture representative waits.
Question 558
A service has intermittent timeout and unknown query cause. The team selects: Enable Query Store and capture representative waits. Which implementation action puts this design into practice?
- Force a known-good Query Store plan when supported
- Enable RCSI where application semantics permit
- Use Query Store to compare prior and current execution plans
- Configure Query Store capture policy
Explanation
Correct Answer
D:Configure Query Store capture policy. This is the concrete setup for enable query store and capture representative waits, and find regressed query runtime and wait category supplies a direct confirmation.
Incorrect Answers
A:Force a known-good Query Store plan when supported. This concerns reusing last month's stable query plan after regression. Instead, configure Query Store capture policy.
B:Enable RCSI where application semantics permit. This concerns avoiding blocking interactive readers under concurrent writes. Instead, configure Query Store capture policy.
C:Use Query Store to compare prior and current execution plans. This is relevant when an API has latency spikes after an index disappears. Instead, configure Query Store capture policy.
Question 559
A service has intermittent timeout and unknown query cause. After implementing the design, which check most directly validates the expected behavior?
- Inspect plan for Sort operator removal
- Compare CPU and latency before/after scaling
- Find regressed query runtime and wait category
- Identify blocking_session_id and elapsed wait time
Explanation
Correct Answer
C:Find regressed query runtime and wait category. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that query performance history was not captured.
Incorrect Answers
A:Inspect plan for Sort operator removal. This concerns a t-SQL query sorts millions of rows unnecessarily. Instead, find regressed query runtime and wait category.
B:Compare CPU and latency before/after scaling. This concerns more resources for predictable query load. Instead, find regressed query runtime and wait category.
D:Identify blocking_session_id and elapsed wait time. This concerns two transactions update the same stock row and block each other. Instead, find regressed query runtime and wait category.
Question 560
A service has intermittent timeout and unknown query cause. A test instead finds: No historical plans exist for the incident window. Which underlying issue best explains this finding?
- Plan forcing conflicts with changed query or schema shape
- Query performance history was not captured
- Available compute or service tier remains undersized
- Relevant filtered cardinality information is absent
Explanation
Correct Answer
B:Query performance history was not captured. It accounts for the specific observed failure: no historical plans exist for the incident window. The intended solution is enable query store and capture representative waits with the required setup.
Incorrect Answers
A:Plan forcing conflicts with changed query or schema shape. This concerns forced plan fails to apply after schema change. Instead, query performance history was not captured.
C:Available compute or service tier remains undersized. This concerns cpu remains pinned after index tuning. Instead, query performance history was not captured.
D:Relevant filtered cardinality information is absent. This concerns optimizer assumes uniform distribution for rare status. Instead, query performance history was not captured.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests