70-465 Question #126: Real Exam Question with Answer & Explanation

Question

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

Options

• AQuery the sys.dm_exec_requests dynamic management view.
• BCreate a trace in SQL Server Profiler that contains the Deadlock graph event.
• CQuery the sys.dm_exec_sessions dynamic management view.
• DEnable trace flag 1222, and then view the SQL Server error log.

Explanation

To identify the isolation level of a query involved in a deadlock, you need to examine session-level metadata. The sys.dm_exec_sessions DMV exposes the transaction_isolation_level column, which directly reports the isolation level for each session.

Common mistakes.

• A. sys.dm_exec_requests shows information about currently executing requests but does not include a transaction_isolation_level column, making it unsuitable for directly identifying the isolation level of a session involved in a deadlock.
• B. The Deadlock graph event in SQL Server Profiler captures the deadlock chain and involved objects/processes but does not explicitly report the transaction isolation level of the participating queries.
• D. Trace flag 1222 writes detailed deadlock information to the SQL Server error log including lock types and involved queries, but the output does not directly expose the transaction isolation level used by the sessions involved in the deadlock.

Concept tested. Identifying session transaction isolation level via DMVs

Reference. https://learn.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-sessions-transact-sql

No community discussion yet for this question.