A database administrator is troubleshooting a deadlock that occurred between two transactions. The deadlock graph shows that Transaction A held a lock on Table1 and requested a lock on Table2, while Transaction B held a lock on Table2 and requested a lock on Table1. What is the most effective way to prevent this deadlock from recurring?

Set the deadlock priority of one transaction to LOW.

Ensure all transactions access tables in the same order.

Add indexes on the join columns used by both transactions.

Enable read committed snapshot isolation.

A query is running slowly. The execution plan shows a table scan on a large table, and sys.dm_os_wait_stats shows high PAGEIOLATCH_SH waits. You need to improve performance. Which action is most likely to help?

Enable Query Store to capture future performance data.

Increase the database's MAXDOP to use more CPUs.

Create an index that supports the query's WHERE clause.

Change the database to use SNAPSHOT isolation.

A query is running slowly, and the execution plan shows a warning about a missing index. The database administrator wants to resolve the performance issue with minimal effort. What should the administrator do?

Update statistics on the tables involved.

Force a different execution plan using Query Store.

Rewrite the query to use a different join type.

Create the index suggested in the execution plan.

Microsoft Certified:SQL AI Developer Associate

Domain 2Objective 2

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

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 1–5

1foundation · easy
Which database configuration setting directly controls the amount of memory that SQL Server can use for buffer pool and other memory consumers?
Select an answer first
2application · medium
A database administrator is troubleshooting a deadlock that occurred between two transactions. The deadlock graph shows that Transaction A held a lock on Table1 and requested a lock on Table2, while Transaction B held a lock on Table2 and requested a lock on Table1. What is the most effective way to prevent this deadlock from recurring?
Select an answer first
3expert · hard
A query is running slowly. The execution plan shows a table scan on a large table, and sys.dm_os_wait_stats shows high PAGEIOLATCH_SH waits. You need to improve performance. Which action is most likely to help?
Select an answer first
4foundation · easy
Which dynamic management view (DMV) would you query to see the most expensive queries by total CPU time?
Select an answer first
5application · medium
A query is running slowly, and the execution plan shows a warning about a missing index. The database administrator wants to resolve the performance issue with minimal effort. 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 1)

Questions 1–5

Which database configuration setting directly controls the amount of memory that SQL Server can use for buffer pool and other memory consumers?

A query is running slowly. The execution plan shows a table scan on a large table, and sys.dm_os_wait_stats shows high PAGEIOLATCH_SH waits. You need to improve performance. Which action is most likely to help?

Which dynamic management view (DMV) would you query to see the most expensive queries by total CPU time?

A query is running slowly, and the execution plan shows a warning about a missing index. The database administrator wants to resolve the performance issue with minimal effort. What should the administrator do?

Keep studying — Secure, optimize, and deploy database solutions