A company runs a database on Azure SQL Database with the General Purpose tier. The database is used for both OLTP and reporting. During the day, OLTP queries are slow, and at night, reporting queries are slow. The company wants to improve performance for both workloads without changing the application code. What should you do?

Create a read replica and route reporting queries to it.

Move the database to an elastic pool with a higher eDTU.

Switch to the Business Critical tier.

Enable auto-scaling for the database.

A database administrator wants to improve the performance of a SQL Server database that experiences heavy read workloads. Which configuration option is most directly aimed at improving read performance?

Enabling read-committed snapshot isolation

Creating a columnstore index on the large fact table

Enabling the 'optimize for ad hoc workloads' option

Increasing the MAXDOP setting to 0

What is the primary purpose of Query Store in SQL Server?

To store query text for auditing purposes

To capture and track query performance over time, enabling identification of regressions

To provide a backup of all executed queries

To automatically rewrite poorly performing queries

A retail company's order-processing application has become slower over the past week. The database administrator suspects a specific stored procedure that was recently modified. You need to confirm the regression and identify the exact statement within the procedure that is causing the performance drop. What should you do first?

Use Query Store to view the regressed queries report and compare the runtime statistics of individual statements in the procedure across the last week.

Enable 'FORCE LEGACY CARDINALITY ESTIMATOR' on the database to revert to older behavior and see if performance improves.

Run sp_recompile on the stored procedure to clear the cached plan and force a fresh compilation.

Query sys.dm_exec_query_stats to compare total worker time for the procedure before and after the change.

Query Store is enabled on a database. A query's performance has degraded, and you want to force the previous plan. However, the previous plan is no longer in Query Store because it was purged due to the MAX_STORAGE_SIZE_MB setting. What should you do to recover the plan?

Increase MAX_STORAGE_SIZE_MB and wait for the plan to be recaptured.

Use the Query Store catalog view sys.query_store_plan to find the plan ID and force it.

Restore the database from a backup that contains the plan in Query Store.

Recreate the query's execution plan by running the query with the FORCEPLAN hint.

Microsoft Certified:SQL AI Developer Associate

Domain 2Objective 2

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

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 26–30

26expert · hard
A company runs a database on Azure SQL Database with the General Purpose tier. The database is used for both OLTP and reporting. During the day, OLTP queries are slow, and at night, reporting queries are slow. The company wants to improve performance for both workloads without changing the application code. What should you do?
Select an answer first
27foundation · easy
A database administrator wants to improve the performance of a SQL Server database that experiences heavy read workloads. Which configuration option is most directly aimed at improving read performance?
Select an answer first
28foundation · easy
What is the primary purpose of Query Store in SQL Server?
Select an answer first
29application · medium
A retail company's order-processing application has become slower over the past week. The database administrator suspects a specific stored procedure that was recently modified. You need to confirm the regression and identify the exact statement within the procedure that is causing the performance drop. What should you do first?
Select an answer first
30expert · hard
Query Store is enabled on a database. A query's performance has degraded, and you want to force the previous plan. However, the previous plan is no longer in Query Store because it was purged due to the MAX_STORAGE_SIZE_MB setting. What should you do to recover the 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 6)

Questions 26–30

A database administrator wants to improve the performance of a SQL Server database that experiences heavy read workloads. Which configuration option is most directly aimed at improving read performance?

What is the primary purpose of Query Store in SQL Server?

Query Store is enabled on a database. A query's performance has degraded, and you want to force the previous plan. However, the previous plan is no longer in Query Store because it was purged due to the MAX_STORAGE_SIZE_MB setting. What should you do to recover the plan?

Keep studying — Secure, optimize, and deploy database solutions