Question 35

Answer B is correct. In most instances, deadlock scenarios are a result of poor application design. Numerous types of design flaws can cause deadlock scenarios, including accessing objects in an inconsistent order or setting unnecessary locks. To isolate the part of the application that is causing the problem, you need to test the program within its production environment. Although the System Monitor would be able to provide you with information on the numbers of deadlocks, it does not provide much specific information about where they are occurring. The SQL Profiler is specifically designed to closely monitor what is going on within the DBMS and provide detailed information.

For more information, see the following sources:

• Chapter 7, "Monitoring SQL Server Performance," and Chapter 8, "Troubleshooting and Optimizing SQL Server"

• SQL Server 2005 Books Online: "Accessing and Changing Relational Data, Locking"

• SQL Server 2005 Books Online: "Administering SQL Server, Monitoring Server Performance, Monitoring with SQL Profiler"

• "Understanding and Avoiding Blocking," http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1a_4uav.asp

• Within MSDN Library, go to MSDN Home, MSDN Library, Servers and Enterprise Development, SQL Server, SQL Server 2005, Application Design.