You work as the database administrator at Domain.com. All servers on the Domain.com network run Windows Server 2003 and all database servers run SQL Server 2005. Six month after implementing a Web application that access a Domain.com database; Web site users complain about poor response times from the Web site. You need to determine if the problem is caused by deadlocks. If the problem is caused by deadlocks, you want to determine which objects are involved in the deadlocks.
What should you do?
A.
Use SQL Server Profiler to run a trace.
B.
Run the sp_lock stored procedure.
C.
Query the sys.dm_tran_database_transactions dynamic management view.
D.
Query the sys.dm_tranlocks dynamic management view.
Explanation:
You can use SQL Server Profiler to monitor locks and deadlocks.
Incorrect Answers:
B, D: The sp_lock stored procedure has been deprecated in SQL Server 2005 in favor of the sys.dm_tranlocks dynamic management view. However, the sys.dm_tranlocks dynamic management view allows you to view current locks and deadlocks. It does not allow you to view a history of deadlocks
C: The sys.dm_tran_database_transactions dynamic management view provides information about transactions rather than locks and deadlocks.