You have a query that is used by a reporting dashboard. Users report that the query sometimes takes a long time to run. You need to recommend a solution to identify what is causing the issue. What…

The correct answer is B. Set the blocked process threshold, and then create an alert. Step 1: Turn on the blocked process report. This will look for any blocking taking 20 seconds or longer. --Make sure you don't have any pending changes FROM sys.configurations WHERE value <> value_in_use; exec sp_configure 'show advanced options', 1; exec sp_configure 'blocked…

Submitted by daniela_cl· Mar 5, 2026Design and implement database solutions for SQL Server

Question

You have a query that is used by a reporting dashboard. Users report that the query sometimes takes a long time to run. You need to recommend a solution to identify what is causing the issue. What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.

Options

ASet the blocked process threshold, and then run SQL Server Profiler.
BSet the blocked process threshold, and then create an alert.
CEnable trace flag 1204, and then create an alert.
DCreate a job that queries the sys.dm_os_waiting_tasks dynamic management view.

How the community answered

(34 responses)

A
3% (1)
B
76% (26)
C
9% (3)
D
12% (4)

Explanation

Step 1: Turn on the blocked process report. This will look for any blocking taking 20 seconds or longer. --Make sure you don't have any pending changes FROM sys.configurations WHERE value <> value_in_use; exec sp_configure 'show advanced options', 1; exec sp_configure 'blocked process threshold (s)', 20; Step 2: Set up a trace to capture the blocked process report. Run it as a server side trace.

Topics

#Query performance tuning#blocked processes#SQL Server alerts

Community Discussion

No community discussion yet for this question.

Full 70-465 Practice