Database Reference
In-Depth Information
Unfortunately, SQL Server 2005 does not support this option, and the only way to disable lock escalation in this
version is by using documented trace flags
T1211
or
T1224
at the instance or session level.
T1211
disables lock escalation, regardless of the memory conditions.
T1224
disables lock escalation based on the number of locks threshold, although lock
escalation can still be triggered in the case of the memory pressure.
■
You can use
T1211
and
T1224
trace flags at the session level to prevent lock escalation in a batch operation that
affects a large number of rows.
Tip
■
You can read more about trace flags
T1211
and
T1224
in Books online at:
Note
As with the other blocking issues, you should find the root cause as to why lock escalation occurs. You should
also think about the pros and cons of disabling lock escalation on particular objects in the system. Although it could
reduce the blocking in the system, SQL Server would use more memory to store lock information. And, of course, you
can consider code refactoring as another option.
In case lock escalation is triggered by the writers, you can reduce the batches to the point at which they are
acquiring less than 5,000 row-and page-level locks per object. You can still process multiple batches in the same
transaction - the 5,000 locks threshold is per statement. At the same time, you should remember that smaller batches
are usually less effective than large ones. You need to fine-tune the batch sizes and find the optimal values. It is
normal to have lock escalation triggered as long as object-level locks are not held for an excessive period of time
and/or it does not affect the other sessions.
As for the lock escalation triggered by the readers, you should avoid situations in which many shared (S) locks
are held. One example is scans due to non-optimized or reporting queries in REPEATABLE READ or SERIALIZABLE
transaction isolation levels when queries held the shared (S) locks until the end of transaction. The example shown in
Figure
20-8
runs the select from the
Orders
table using the REPEATABLE READ isolation level.
