Administering Microsoft SQL Server 2012/2014 Databases — Question 10

You administer a Microsoft SQL Server database named Orders.
Users report that during peak usage periods, certain operations are taking more time than expected. Your initial analysis suggests that blocking is the cause.
You need to gather more data to be able to determine which processes are being blocked and to identify the root cause.
What should you do?

Answer options

• A. Schedule a SQL Agent job to run every 60 seconds and insert the results of executing the sys.dm_os_wait_stats DMV into a table.
• B. Use System Monitor to catch the Lock Waits/sec event
• C. Use sp_configure to set the blocked process threshold. Start a trace using SQL Server Profiler to catch the Blocked Process Report event.
• D. Start a Trace using SQL Server Profiler to catch the Lock: Deadlock event

Correct answer: D

Explanation

The correct answer is D, as capturing the Lock: Deadlock event will provide insights into deadlocks occurring in the system, which are a specific type of blocking. Options A and C focus on gathering statistical data or setting thresholds, which may not directly capture the blocking processes. Option B only monitors lock waits without providing details on the specific processes involved.