You are troubleshooting a blocking issue. You run a query against sys.dm_exec_requests and see that session 55 is blocked by session 42. You then query sys.dm_exec_sessions for session 42 and see that it is running a transaction that has been open for 30 minutes. What is the most appropriate next step?

Rebuild the indexes on the table being modified.

Check the transaction's isolation level and the application that owns the session.

Enable read committed snapshot isolation to eliminate the block.

Kill session 42 immediately to release the block.

You are tuning a database for a reporting application. A frequently executed query filters on a column that has low selectivity and joins to a large fact table. The execution plan shows a clustered index scan on the fact table. You need to improve performance without significantly increasing storage overhead. What should you do?

Create a clustered index on the filter column of the fact table.

Drop all nonclustered indexes on the fact table to reduce maintenance overhead.

Create a nonclustered index on the fact table that includes the filter column and the columns needed by the query.

Add a filtered index on the filter column for the most common value.

Which DMV provides information about missing indexes that could improve query performance?

sys.dm_db_missing_index_details

Microsoft Certified:Azure Database Administrator Associate

Domain 3Objective 2

Monitor and Optimize Query Performance DP-300 Practice Questions (Page 7)

Part of the Monitor, configure, and optimize database resources domain, which accounts for 20–25% of the DP-300 exam. Microsoft does not publish an official question count, but from its 100-minute exam (~40–65 total, ~8–16 in this domain), expect 3–5 from this objective — we provide 42 practice questions to prepare you well beyond it. (estimate)

42questions here

9free pages

10concepts

20–25%of the exam

Questions 31–35

31foundation · easy
Which Query Store report in SQL Server Management Studio shows the execution plans that have changed and the associated performance impact?
Select an answer first
32application · medium
You are troubleshooting a blocking issue. You run a query against sys.dm_exec_requests and see that session 55 is blocked by session 42. You then query sys.dm_exec_sessions for session 42 and see that it is running a transaction that has been open for 30 minutes. What is the most appropriate next step?
Select an answer first
33application · medium
You are tuning a database for a reporting application. A frequently executed query filters on a column that has low selectivity and joins to a large fact table. The execution plan shows a clustered index scan on the fact table. You need to improve performance without significantly increasing storage overhead. What should you do?
Select an answer first
34foundation · easy
Which dynamic management view is used to identify queries that are currently executing and their resource consumption?
Select an answer first
35foundation · easy
Which DMV provides information about missing indexes that could improve query performance?
Select an answer first

Finished these 5 questions?

Review the revealed explanations, or continue through the curriculum.

Next 5 questions →Back to DP-300

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-300” is a trademark of its owner, used for identification only.

Monitor and Optimize Query Performance DP-300 Practice Questions (Page 7)

Questions 31–35

Which Query Store report in SQL Server Management Studio shows the execution plans that have changed and the associated performance impact?

Which dynamic management view is used to identify queries that are currently executing and their resource consumption?

Which DMV provides information about missing indexes that could improve query performance?

Keep studying — Monitor, configure, and optimize database resources