Systems Engineering and RDBMS

Locking trace flags in SQL Server

Posted by decipherinfosys on February 1, 2007

There are a few trace flags that can help you fine tune your locking and find deadlock problems. Please use them with extreme caution as they can generate a lot of overhead in your system (depends upon the nature of the application) – and do remember to turn these flags off after you have collected the data that you needed.

1200: Shows all locks acquired by all connections. This provides a huge output and so our recommendation would be to use this in a controlled test environment, where only a single connection is working at the same time.

1204 & 3605: Outputs a lot of information about the participants in a deadlock. These are the most commonly used locking trace options and are very helpful in troubleshooting deadlock and deadlock chain issues.

1211: Disables all lock escalation. This trace flag forces the lock manager not to escalate locks, even if it’s running out of locks. There are other better ways to prevent lock escalation and we will cover them either in a new blog post or a whitepaper.

1224: This is a SQL Server 2005 specific trace flag. It disables lock escalation of individual statements, and specifies that the engine operates as if the sp_configure’s “locks” option is set to 0. The engine only escalates row or page locks to table locks if the amount of memory used by the locked resources exceed 40% of the memory used by the engine (exclusive of AWE (Address Windows Extension) memory).

Note: If both trace flag 1211 and 1224 are set, 1224 takes precedence over 1211.
Use of this trace flag can generate excessive numbers of locks. This can slow the performance of the engine, or cause 1204 errors (unable to allocate lock resource) due to insufficient memory so use it with extreme caution.  The most common ones that we have used are 1204 and 3605.

One Response to “Locking trace flags in SQL Server”

  1. […] have talked about lock escalation in some of our previous posts – you can read more on it here and here. In SQL Server 2008, there is an enhancement to the ALTER TABLE statement that allows a new […]

Sorry, the comment form is closed at this time.

%d bloggers like this: