You are a database administrator for AIOTestKing.com. You receive alerts reporting that several transactions on your SQL Server 2005 database have terminated due to a deadlock error. You need to find out the causes of the deadlocks. What should you do?
A.
Use System Monitor to trace the Application instance of the Number of Deadlocks/sec counter in the SQL Locks object.
B.
Use the sys.dm_tran_locks dynamic management view (DMV).
C.
Run the Database Engine Tuning Advisor (DTA) and implement the recommendations.
D.
Run SQL Server Profiler and create a trace with the Deadlock graph event group, and extract deadlock events.
Explanation:
To trace deadlock events, add the Deadlock graph event class to a trace. This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server Profiler can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio. You can configure SQL Server Profiler to extract Deadlock graph events to a single file that contains all Deadlock graph events, or to separate files.