Problem solve Get help with specific problems with your technologies, process and projects.

Bedeviled by SQL Server lock escalation? Trace flags can help

SQL Server lock escalation can save on memory when working with a table strewn with locks, but it also can lead to blocking. Use SQL Server trace flags to disable lock escalation.

Lock escalation in SQL Server is the mechanism that trades up smaller, fine-grained locks for bigger, coarser ones...

for efficiency’s sake.

For instance, if a given table has many row-level locks, there comes a point where adding more locks uses up a ton of memory, and it makes more sense for SQL Server to lock a larger parent object instead. Sometimes, the lock-escalation mechanism can cause database blocking, and you may need a set of SQL Server trace flags to find out what’s wrong.

Two scenarios cause SQL Server lock escalation. The first is when there are a large number of locks on a given object. If a single Transact-SQL statement creates more than 5,000 locks on a single table, for instance, that will trigger a lock escalation.

The second scenario is when the number of locks exceeds a preconfigured memory threshold -- this is called memory pressure condition. The typical threshold for memory pressure is when the amount of memory used by lock objects is more than a certain percentage -- typically 40%, but it can be adjusted -- of the entire allotment of memory for the SQL Server database engine.

Under conventional conditions, the default escalation behaviors should work fine. For fine-grained modification of the way locks work, there are lock hints and LOCK_ESCALATION options for tables. But there may be circumstances in which you’ll want to manually change lock escalation throughout SQL Server. To that end, two SQL Server trace flags can be used to change the way lock escalation works.

SQL Server trace flag 1224 disables lock escalation based on the number of locks on a given resource. The most common example of this is when you have many row locks or page locks on a given table; those are then bumped up to a single table lock. Note that if you use flag 1224, memory pressure -- the 40% threshold described earlier -- can still cause an escalation.

SQL Server trace flag 1211 does the same thing as flag 1224 but disables escalation based on the number of locks and memory pressure. If you use flags 1224 and 1211 together, 1211 overrides 1224. Use this flag only if you have a reason to prevent escalation due to memory pressure. You might, for instance, be performing a test specifically designed  for low memory conditions.

So what are some of the circumstances in which you might want to use trace flags?

  • Troubleshooting blocking caused by SQL Server lock escalation. If many lock escalations are taking place, they can create situations in which queries from different users are blocked entirely. For instance, if in a given query numerous row locks are elevated to a table lock, that table lock could block another query, especially if the first query runs for a long time. If such blocking is endemic throughout your database, the underlying design of the application may be the real problem. Disabling lock escalation may allow the application to creak along until you can come up with a permanent fix or a more elegant interim solution.
  • Running a specific instance of SQL Server in a controlled way. This might be done as a way to do controlled tests on the memory consumption of a specific application, which you’re running in a separate instance or even on a separate machine.
  • Bug checking. Adam Machanic ran across a bug in SQL Server 2008 (it’s being fixed) that involved lock escalation behavior under heavy data loads. If you’re dealing with large data sets in which many locks are acquired and you want to make sure some of the behaviors you’re seeing are not due to bugs in SQL Server itself, trace flags can help you gather some additional perspective.

In every case, though, using trace flags to manage lock escalation should be seen as an interim measure, not as a solution to problematic lock escalations. Use them, but find out why you’re having lock problems and solve that.

Dig Deeper on Microsoft SQL Server Performance Monitoring and Tuning