Administering a SQL Database Infrastructure — Question 243

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.

Answer options

• A. Set the blocked process threshold, and then run SQL Server Profiler.
• B. Set the blocked process threshold, and then create an alert.
• C. Enable trace flag 1204, and then create an alert.
• D. Create a job that queries the sys.dm_os_waiting_tasks dynamic management view.

Correct answer: B

Explanation

The best recommendation is to set the blocked process threshold and then create an alert, as this approach allows for proactive monitoring of blocked processes, helping to identify performance issues. While the other options may provide some insights, they do not offer the same level of direct monitoring and notification capabilities as option B.