You need to identify which transaction causes the deadlock

You are troubleshooting an application that runs a query. The application frequently causes deadlocks.
You need to identify which transaction causes the deadlock.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.

You are troubleshooting an application that runs a query. The application frequently causes deadlocks.
You need to identify which transaction causes the deadlock.
What should you do?
More than one answer choice may achieve the goal. Select the BEST answer.

A.
Query the sys.dm_exec_sessions dynamic management view.

B.
Query the sys.dm_exec_requests dynamic management view.

C.
Create a trace in SQL Server Profiler that contains the Deadlock graph event

D.
Create an extended events session to capture deadlock information.

Explanation:

Troubleshooting deadlocks
You have been receiving reports from users indicating that certain applications are returning
deadlock errors. To maximize the effectiveness of troubleshooting these problems, you
decide to focus on the deadlocks that are hit most frequently. You create an Extended
Events session that:
/ Configures deadlock event tracking for the session.
/ Specifies a target that aggregates based on an identifier for the deadlock.
You run the Extended Events session, and after additional deadlocks are reported you are
able to obtain aggregated deadlock information, along with the complete XML deadlock
graph for each source. Using this information you are able to pin point the most common
deadlocks and start working on a solution.



Leave a Reply 4

Your email address will not be published. Required fields are marked *


Ricky

Ricky

My answer is D.

You can also do it in SQL profiler, but SQL profiler will be soon deprecated. Extended event is the new feature and it is lightweight.

[email protected]

Muraru Adrian

Muraru Adrian

D is right answer.
C. also is a solution, but is not matched with statement “Select the BEST answer”. Microsoft mentioned that Profiler is a deprecated feature since 2012 version.