Which trace flag should you use?

You are responsible for managing an instance of SQL Server 2008. You discover that the server is performingvery poorly.
You investigate and discover that deadlocks are causing the problem. You want to identify which processes and resources are causing the problem.
You want to ensure that theinformation you receive about deadlocks is formatted by processes and then by resources. Which trace flag should you use?

A.
trace flag 2528

B.
trace flag 1211

C.
trace flag 1222

D.
trace flag 1224

Explanation:

You should use trace flag 1222. Trace flags can be used to diagnose performance issues or debug storedprocedures. Trace flags allow you to set characteristics of a server on a temporary basis or to turn off specificbehavior. Trace flags 1204 and 1222 provide information about deadlocks that is recorded in the SQL Server2008 error log when deadlocks occur. Trace flag 1222 provides information about resources and types of locksthat are causing deadlocks along with information about the statements affected by deadlocks. The information isformatted by process and then by resource. You should not use trace flag 2528. Trace flag 2528 disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE. This trace flag does not provide information aboutdeadlocks. You should not use trace flag 1211 because this trace flag does not provide information about deadlocks. Traceflag 1211 disables lock escalation based on memory pressure or the number of locks. You should not use trace flag 1224 because this trace flag does not provide information about deadlocks. Traceflag 1224 disables lock escalation based on the number of locks.

Objective:
Monitoring and Troubleshooting SQL Server

Sub-Objective:
Identify concurrency problems.

References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > Trace Flags (Transact-SQL) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Querying and ChangingData (Database Engine) > Accessing and Changing Database Data > Locking and Row Versioning > Locking inthe Database Engine > Deadlocking > Detecting and Ending Deadlocks