A retail company's order-processing database has a stored procedure that inserts new orders and then updates inventory. Recently, the procedure's execution time increased from 2 seconds to 15 seconds. Query Store shows a significant increase in logical reads for the procedure, and the execution plan changed from a loop join to a hash join. The database is running on Azure SQL Database with the default settings. What should you do first to address the performance regression?

Force the previous execution plan using Query Store.

Update statistics on the tables involved in the join.

Review the Query Store plan regression report and force the last known good plan.

Rebuild all indexes on the order and inventory tables.

A database in Azure SQL Database is used for a mix of OLTP and analytical queries. The OLTP queries are latency-sensitive, while analytical queries are long-running and consume significant CPU. You need to ensure that OLTP performance is not impacted by analytical workloads. What should you do?

Use a read replica for analytical queries.

Enable Query Store to monitor both workloads.

Increase the database's MAXDOP to allow analytical queries to use more CPUs.

Set the database to READ COMMITTED SNAPSHOT isolation.

A healthcare application uses a database with the READ COMMITTED isolation level. During peak hours, long-running reporting queries are blocking critical update transactions, causing timeouts. The application cannot change the isolation level for the reporting queries because they are generated by a third-party tool. You need to reduce blocking without changing the application code. What should you do?

Create nonclustered indexes on the tables being reported on.

Change the database compatibility level to a lower version.

Enable the READ_COMMITTED_SNAPSHOT database option.

Set the ALLOW_SNAPSHOT_ISOLATION database option to ON.

What is the primary purpose of analyzing a query execution plan?

To identify which queries are most frequently executed

To enforce security permissions on database objects

To automatically rewrite queries for better performance

To understand how the database engine executes a query and identify performance bottlenecks

Microsoft Certified:SQL AI Developer Associate

Domain 2Objective 2

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

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 21–25

21application · medium
A retail company's order-processing database has a stored procedure that inserts new orders and then updates inventory. Recently, the procedure's execution time increased from 2 seconds to 15 seconds. Query Store shows a significant increase in logical reads for the procedure, and the execution plan changed from a loop join to a hash join. The database is running on Azure SQL Database with the default settings. What should you do first to address the performance regression?
Select an answer first
22application · medium
A database in Azure SQL Database is used for a mix of OLTP and analytical queries. The OLTP queries are latency-sensitive, while analytical queries are long-running and consume significant CPU. You need to ensure that OLTP performance is not impacted by analytical workloads. What should you do?
Select an answer first
23foundation · easy
Which element in a query execution plan indicates that a query is scanning an entire table, which may be a sign of a missing index?
Select an answer first
24expert · hard
A healthcare application uses a database with the READ COMMITTED isolation level. During peak hours, long-running reporting queries are blocking critical update transactions, causing timeouts. The application cannot change the isolation level for the reporting queries because they are generated by a third-party tool. You need to reduce blocking without changing the application code. What should you do?
Select an answer first
25foundation · easy
What is the primary purpose of analyzing a query execution plan?
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 5)

Questions 21–25

A database in Azure SQL Database is used for a mix of OLTP and analytical queries. The OLTP queries are latency-sensitive, while analytical queries are long-running and consume significant CPU. You need to ensure that OLTP performance is not impacted by analytical workloads. What should you do?

Which element in a query execution plan indicates that a query is scanning an entire table, which may be a sign of a missing index?

What is the primary purpose of analyzing a query execution plan?

Keep studying — Secure, optimize, and deploy database solutions