100% Latest & Updated Microsoft DP-800 Practice Test Questions, Exam Dumps & Verified Answers!
30 Days Free Updates, Instant Download!
Question 510
An accounting process cannot allow dirty reads. The team selects: Use READ COMMITTED or stronger isolation rather than NOLOCK. Which implementation action puts this design into practice?
- Refresh stats or tune cardinality before forcing hints
- Set isolation and hold transaction around read/update
- Inspect WRITELOG waits and log file latency
- Remove NOLOCK hints and choose approved isolation
Explanation
Correct Answer
D:Remove NOLOCK hints and choose approved isolation. This is the concrete setup for use read committed or stronger isolation rather than nolock, and run concurrent uncommitted write and verify reader does not see it supplies a direct confirmation.
Incorrect Answers
A:Refresh stats or tune cardinality before forcing hints. This is relevant when a plan uses a hash join and spills to tempdb. Here, remove NOLOCK hints and choose approved isolation.
B:Set isolation and hold transaction around read/update. This concerns preventing non-repeatable reads during decision. Here, remove NOLOCK hints and choose approved isolation.
C:Inspect WRITELOG waits and log file latency. This concerns a high-write SQL server suffers excessive transaction log latency. Here, remove NOLOCK hints and choose approved isolation.
Question 511
An accounting process cannot allow dirty reads. After implementing the design, which check most directly validates the expected behavior?
- Run concurrent uncommitted write and verify reader does not see it
- Compare reader waits and version-store load
- Check eventual success and no duplicate effects
- Run concurrent update and verify first transaction sees stable row
Explanation
Correct Answer
A:Run concurrent uncommitted write and verify reader does not see it. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that read uncommitted allowed dirty reads.
Incorrect Answers
B:Compare reader waits and version-store load. This concerns avoiding blocking interactive readers under concurrent writes. Instead, run concurrent uncommitted write and verify reader does not see it.
C:Check eventual success and no duplicate effects. This is relevant when a SQL transaction fails with deadlock victim error 1205. Instead, run concurrent uncommitted write and verify reader does not see it.
D:Run concurrent update and verify first transaction sees stable row. This concerns preventing non-repeatable reads during decision. Instead, run concurrent uncommitted write and verify reader does not see it.
Question 512
An accounting process cannot allow dirty reads. A test instead finds: Reports include rows that later roll back. Which underlying issue best explains this finding?
- Available compute or service tier remains undersized
- Isolation level did not protect repeatable reads
- READ UNCOMMITTED allowed dirty reads
- Suitable access path is missing
Explanation
Correct Answer
C:READ UNCOMMITTED allowed dirty reads. It accounts for the specific observed failure: reports include rows that later roll back. The intended solution is use read committed or stronger isolation rather than nolock with the required setup.
Incorrect Answers
A:Available compute or service tier remains undersized. This concerns cpu remains pinned after index tuning. The relevant SQL or system operation is READ UNCOMMITTED allowed dirty reads.
B:Isolation level did not protect repeatable reads. This concerns seat count changes between reads inside one transaction. The relevant SQL or system operation is READ UNCOMMITTED allowed dirty reads.
D:Suitable access path is missing. This concerns cpu is high for low-selectivity table scan. The relevant SQL or system operation is READ UNCOMMITTED allowed dirty reads.
Question 513
A seat-booking transaction must prevent non-repeatable reads during decision. Which THREE listed actions together specify the technical design, its concrete implementation, and a direct acceptance check? (Choose THREE.)
- Run concurrent update and verify first transaction sees stable row
- Use READ COMMITTED or stronger isolation rather than NOLOCK
- Schedule/rescope maintenance and evaluate online index operations
- Set isolation and hold transaction around read/update
- Use REPEATABLE READ or equivalent locks for required scope
- Use SERIALIZABLE range protection where needed
Explanation
Correct Answers
A:This verification checks the intended behavior directly: run concurrent update and verify first transaction sees stable row. It can expose the failure mode isolation level did not protect repeatable reads.
D:This is the concrete configuration of the specified design: set isolation and hold transaction around read/update. The choice makes the implementation testable in the given workload.
E:Use REPEATABLE READ or equivalent locks for required scope. The implementation is set isolation and hold transaction around read/update; success can be checked by run concurrent update and verify first transaction sees stable row. The method addresses the stated data and workload constraint.
Incorrect Answers
B:Use READ COMMITTED or stronger isolation rather than NOLOCK. This is relevant when an accounting process cannot allow dirty reads. Instead, run concurrent update and verify first transaction sees stable row.
C:Schedule/rescope maintenance and evaluate online index operations. This concerns an overnight index rebuild causes daytime blocking. Instead, run concurrent update and verify first transaction sees stable row.
F:Use SERIALIZABLE range protection where needed. This concerns preventing new qualifying rows during an in-flight range check. Instead, run concurrent update and verify first transaction sees stable row.
Question 514
A seat-booking transaction must prevent non-repeatable reads during decision. The team selects: Use REPEATABLE READ or equivalent locks for required scope. Which implementation action puts this design into practice?
- Rewrite CONVERT(date,CreatedAt)=@d to half-open datetime range
- Remove NOLOCK hints and choose approved isolation
- Set serializable isolation and appropriate indexed predicates
- Set isolation and hold transaction around read/update
Explanation
Correct Answer
D:Set isolation and hold transaction around read/update. This is the concrete setup for use repeatable read or equivalent locks for required scope, and run concurrent update and verify first transaction sees stable row supplies a direct confirmation.
Incorrect Answers
A:Rewrite CONVERT(date,CreatedAt)=@d to half-open datetime range. This is relevant when an index on a date column is ignored when where converts the column. Instead, set isolation and hold transaction around read/update.
B:Remove NOLOCK hints and choose approved isolation. This is relevant when an accounting process cannot allow dirty reads. Instead, set isolation and hold transaction around read/update.
C:Set serializable isolation and appropriate indexed predicates. This concerns preventing new qualifying rows during an in-flight range check. Instead, set isolation and hold transaction around read/update.
Question 515
A seat-booking transaction must prevent non-repeatable reads during decision. After implementing the design, which check most directly validates the expected behavior?
- Run concurrent update and verify first transaction sees stable row
- Run concurrent uncommitted write and verify reader does not see it
- Compare P95 transaction commit time and I/O metrics
- Find regressed query runtime and wait category
Explanation
Correct Answer
A:Run concurrent update and verify first transaction sees stable row. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that isolation level did not protect repeatable reads.
Incorrect Answers
B:Run concurrent uncommitted write and verify reader does not see it. This is relevant when an accounting process cannot allow dirty reads. Instead, run concurrent update and verify first transaction sees stable row.
C:Compare P95 transaction commit time and I/O metrics. This concerns a high-write SQL server suffers excessive transaction log latency. Instead, run concurrent update and verify first transaction sees stable row.
D:Find regressed query runtime and wait category. This is relevant when a service has intermittent timeout and unknown query cause. Instead, run concurrent update and verify first transaction sees stable row.
Question 516
A seat-booking transaction must prevent non-repeatable reads during decision. A test instead finds: Seat count changes between reads inside one transaction. Which underlying issue best explains this finding?
- READ UNCOMMITTED allowed dirty reads
- Suitable access path is missing
- Plan changed after schema or index modification
- Isolation level did not protect repeatable reads
Explanation
Correct Answer
D:Isolation level did not protect repeatable reads. It accounts for the specific observed failure: seat count changes between reads inside one transaction. The intended solution is use repeatable read or equivalent locks for required scope with the required setup.
Incorrect Answers
A:READ UNCOMMITTED allowed dirty reads. This concerns reports include rows that later roll back. Here, isolation level did not protect repeatable reads.
B:Suitable access path is missing. This concerns cpu is high for low-selectivity table scan. Here, isolation level did not protect repeatable reads.
C:Plan changed after schema or index modification. This concerns querying time changes abruptly after deployment. Here, isolation level did not protect repeatable reads.
Question 517
A scheduler must prevent new qualifying rows during an in-flight range check. Which database design or engineering approach most directly meets this requirement?
- Use SERIALIZABLE range protection where needed
- Use READ COMMITTED or stronger isolation rather than NOLOCK
- Monitor tempdb space and version-store consumption
- Use REPEATABLE READ or equivalent locks for required scope
Explanation
Correct Answer
A:Use SERIALIZABLE range protection where needed. The implementation is set serializable isolation and appropriate indexed predicates; success can be checked by attempt concurrent qualifying insert and observe range blocking. The method addresses the stated data and workload constraint.
Incorrect Answers
B:Use READ COMMITTED or stronger isolation rather than NOLOCK. This is relevant when an accounting process cannot allow dirty reads. Instead, use SERIALIZABLE range protection where needed.
C:Monitor tempdb space and version-store consumption. This concerns a server experiences severe tempdb waits from row versioning. Instead, use SERIALIZABLE range protection where needed.
D:Use REPEATABLE READ or equivalent locks for required scope. This concerns preventing non-repeatable reads during decision. Instead, use SERIALIZABLE range protection where needed.
Question 518
A scheduler must prevent new qualifying rows during an in-flight range check. The team selects: Use SERIALIZABLE range protection where needed. Which implementation action puts this design into practice?
- Set isolation and hold transaction around read/update
- Set serializable isolation and appropriate indexed predicates
- Use UPDATE STATISTICS on affected index or table
- Rewrite CONVERT(date,CreatedAt)=@d to half-open datetime range
Explanation
Correct Answer
B:Set serializable isolation and appropriate indexed predicates. This is the concrete setup for use serializable range protection where needed, and attempt concurrent qualifying insert and observe range blocking supplies a direct confirmation.
Incorrect Answers
A:Set isolation and hold transaction around read/update. This concerns preventing non-repeatable reads during decision. Instead, set serializable isolation and appropriate indexed predicates.
C:Use UPDATE STATISTICS on affected index or table. This concerns a high-traffic analytics query runs slowly after statistics become stale. Instead, set serializable isolation and appropriate indexed predicates.
D:Rewrite CONVERT(date,CreatedAt)=@d to half-open datetime range. This is relevant when an index on a date column is ignored when where converts the column. Instead, set serializable isolation and appropriate indexed predicates.
Question 519
A scheduler must prevent new qualifying rows during an in-flight range check. After implementing the design, which check most directly validates the expected behavior?
- Run concurrent update and verify first transaction sees stable row
- Check daytime lock waits and maintenance completion
- Compare reader waits and version-store load
- Attempt concurrent qualifying insert and observe range blocking
Explanation
Correct Answer
D:Attempt concurrent qualifying insert and observe range blocking. This verifies the requirement in the case rather than merely confirming that a job ran; an opposing result can reveal that read range was not protected from inserts.
Incorrect Answers
A:Run concurrent update and verify first transaction sees stable row. This concerns preventing non-repeatable reads during decision. Here, attempt concurrent qualifying insert and observe range blocking.
B:Check daytime lock waits and maintenance completion. This concerns an overnight index rebuild causes daytime blocking. Here, attempt concurrent qualifying insert and observe range blocking.
C:Compare reader waits and version-store load. This concerns avoiding blocking interactive readers under concurrent writes. Here, attempt concurrent qualifying insert and observe range blocking.
Question 520
A scheduler must prevent new qualifying rows during an in-flight range check. A test instead finds: Phantom rows appear before commit. Which underlying issue best explains this finding?
- Isolation level did not protect repeatable reads
- Memory grant was insufficient for actual rows
- Read range was not protected from inserts
- READ UNCOMMITTED allowed dirty reads
Explanation
Correct Answer
C:Read range was not protected from inserts. It accounts for the specific observed failure: phantom rows appear before commit. The intended solution is use serializable range protection where needed with the required setup.
Incorrect Answers
A:Isolation level did not protect repeatable reads. This concerns seat count changes between reads inside one transaction. Instead, read range was not protected from inserts.
B:Memory grant was insufficient for actual rows. This concerns hash join writes many workfiles to tempdb. Instead, read range was not protected from inserts.
D:READ UNCOMMITTED allowed dirty reads. This concerns reports include rows that later roll back. Instead, read range was not protected from inserts.
Developing AI-Enabled Database Solutions (DP-800) Practice Tests