You administer a Microsoft SQL Server 2012 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?
A.
Start a trace using SQL Server Profiler to catch the Lock: Deadlock event.
B.
Use sp_configure to set the blocked process threshold. Start a trace using SQL Server
Profiler to catch the Blocked Process Report event.
C.
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.
D.
Use System Monitor to catch the Lock Waits/sec event.
@see http://blogs.msdn.com/b/axinthefield/archive/2011/06/25/sql-server-lock-escalation-and-blocking.aspx
and a more generalized overview
@see http://www.mssqltips.com/sqlservertip/2429/how-to-identify-blocking-in-sql-server-2005-and-2008/
.. see also hints to Qustion Q73
.. sorry I mean Q37 for sure 🙂