Monitor SQL Server locks

A typical performance issue in a high-activity SQL Server database is when multiple processes are running at the same time and each process "creates a lock" on a table that conflicts with the other process.

Ways to monitor if you have an issue with resource locking:

To monitor locks over a period of time:

* Create a SQL Server trace using SQL Server profiler and use the "Deadlock graph" event

* Execute the DBCC TRACEON (1204,1205) command, which will turn on trace tracking of lock events for the entire server. This type of trace is logged into the SQL Server error log. You must execute the DBCC TRACEOFF command to stop the logging.

To monitor locks that exist right this moment:

* Execute the stored procedure sp_lock to see locks currently held (older method)

* Query the "sys.dm_tran_locks" dynamic management view to see locks currently held (newer method)