Administering a SQL Database Infrastructure — Question 237

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.

Answer options

• A. Query the sys.dm_exec_requests dynamic management view.
• B. Create a trace in SQL Server Profiler that contains the Deadlock graph event.
• C. Query the sys.dm_exec_sessions dynamic management view.
• D. Enable trace flag 1222, and then view the SQL Server error log.

Correct answer: C

Explanation

The correct answer is C because querying the sys.dm_exec_sessions dynamic management view allows you to see session-level information, including isolation levels. Options A and B provide useful information but do not directly reveal the isolation level. Option D gives insights into deadlocks but is not the most straightforward method to find the isolation level.