You work as a Database Administrator (DBA) at ABC.com. All databases are hosted on Windows
Server 2012 servers running SQL Server 2012.
Company developers are testing a custom application. The developers report that deadlocks
often occur when the application runs a query. The developers have asked you to help identify
which transaction is causing the deadlocks.
How should you troubleshoot the issue?
A.
You should configure a deadlock graph event trace in in SQL Server Profiler.
B.
You should use a dynamic management view querying sys.dm_exec_query_stats.
C.
You should use a dynamic management view querying sys.dm_exec_connections.
D.
You should capture deadlock information in an extended events session.
Explanation:
According this site : http://blogs.technet.com/b/mspfe/archive/2012/06/28/how_2d00_to_2d00_monitor_2d00_deadlocks_2d00_in_2d00_sql_2d00_server.aspx
* You should configure a deadlock graph event trace in in SQL Server Profiler
**Yes – cf site
* You should use a dynamic management view querying sys.dm_exec_query_stats.
** No. sys.dm_exec_query_stats Returns aggregate performance statistics for cached query plans in SQL Server. The view contains one row per query statement within the cached plan, and the lifetime of the rows are tied to the plan itself. When a plan is removed from the cache, the corresponding rows are eliminated from this view.
* You should use a dynamic management view querying sys.dm_exec_connections
**No. Returns information about the connections established to this instance of SQL Server and the details of each connection.
* You should capture deadlock information in an extended events session.
** Yes too but … Cf site