YOU’RE A SYSTEMS ENGINEER RESPONSIBLE FOR SQL SERVER, AND A CASE H...

10. You’re a Systems Engineer responsible for SQL Server, and a case had been

escalated via the helpdesk where a user reported an exception in an applica-

tion—the error included deadlock victim. You’d like to implement monitoring

to capture details of the deadlocked processes and locks held with minimum

overhead. Should you:

A. Start a SQL Profiler Trace.

B. Capture Lock\Deadlocks per second with Windows System Monitor.

C. Enable trace flag 1222.

D. Review the sys.dm_tran_locks DMV.

The correct answer is C; trace flag 1222 is the most lightweight method to

capture the required information. SQL trace would provide this information,

however there’s a performance impact when running a trace. Answers B and D

are incorrect because neither will provide the required information.