Which concurrency control method uses a version of the data at the start of a transaction, allowing readers to see a consistent snapshot without blocking writers?

Pessimistic concurrency control

An e-commerce platform uses optimistic concurrency for updating product inventory. When two users attempt to purchase the last item simultaneously, one update succeeds and the other fails with a conflict. The application needs to handle this conflict gracefully. What should the application do?

Refresh the product data and retry the update.

Switch to pessimistic concurrency for all inventory updates.

Retry the failed update immediately without refreshing the data.

Ignore the conflict and show a success message.

A database administrator is troubleshooting a query that has become slower over the past month. Query Store shows that the query has two plans: Plan A (used for the first three weeks) and Plan B (used for the last week). Plan B has higher duration and CPU time. The administrator wants to restore performance immediately while investigating why Plan B is worse. What should the administrator do?

Rebuild the indexes on the tables used by the query.

Force Plan A using Query Store.

Update statistics on the tables used by the query.

Clear the Query Store cache to remove Plan B.

Microsoft Certified:SQL AI Developer Associate

Domain 2Objective 2

Optimize Database Performance DP-800 Practice Questions (Page 4)

Part of the Secure, optimize, and deploy database solutions domain, which accounts for 35–40% of the DP-800 exam. Microsoft does not publish an official question count, but from its 120-minute exam (~50–80 total, ~18–32 in this domain), expect 5–8 from this objective — we provide 32 practice questions to prepare you well beyond it. (estimate)

32questions here

7free pages

9concepts

35–40%of the exam

Questions 16–20

16foundation · easy
Which concurrency control method uses a version of the data at the start of a transaction, allowing readers to see a consistent snapshot without blocking writers?
Select an answer first
17foundation · easy
Which isolation level provides the highest level of data consistency by preventing dirty reads, non-repeatable reads, and phantom reads, but typically reduces concurrency the most?
Select an answer first
18foundation · easy
Which DMV or system view can be used to identify blocking chains in SQL Server?
Select an answer first
19application · medium
An e-commerce platform uses optimistic concurrency for updating product inventory. When two users attempt to purchase the last item simultaneously, one update succeeds and the other fails with a conflict. The application needs to handle this conflict gracefully. What should the application do?
Select an answer first
20expert · hard
A database administrator is troubleshooting a query that has become slower over the past month. Query Store shows that the query has two plans: Plan A (used for the first three weeks) and Plan B (used for the last week). Plan B has higher duration and CPU time. The administrator wants to restore performance immediately while investigating why Plan B is worse. What should the administrator do?
Select an answer first

Finished these 5 questions?

Review the revealed explanations, or continue through the curriculum.

Next 5 questions →Back to DP-800

Free Basic Practice is a study aid with revealable answers — not a scored exam. Examers.io is independent and not affiliated with or endorsed by Microsoft. “DP-800” is a trademark of its owner, used for identification only.

Optimize Database Performance DP-800 Practice Questions (Page 4)

Questions 16–20

Which concurrency control method uses a version of the data at the start of a transaction, allowing readers to see a consistent snapshot without blocking writers?

Which isolation level provides the highest level of data consistency by preventing dirty reads, non-repeatable reads, and phantom reads, but typically reduces concurrency the most?

Which DMV or system view can be used to identify blocking chains in SQL Server?

An e-commerce platform uses optimistic concurrency for updating product inventory. When two users attempt to purchase the last item simultaneously, one update succeeds and the other fails with a conflict. The application needs to handle this conflict gracefully. What should the application do?

Keep studying — Secure, optimize, and deploy database solutions