How to Determine Whether Lock Escalation Is Causing BlockingLock escalation does not cause most blocking problems. To determine whether lock escalation is occurring around the time when you experience blocking issues, start a SQL Profiler trace that includes the Lock:Escalation event. If you do not see any Lock:Escalation events, lock escalation is not occurring on your server and the information in this article does not apply to your situation.
If lock escalation is occurring, verify that the escalated table lock is blocking other users.
For more information about how to identify the head blocker and how to identify the lock resource held by the head blocker that is blocking other server process IDs (SPIDs), click the following article number to view the article in the Microsoft Knowledge Base:
How to Prevent Lock EscalationThe simplest and safest way to prevent lock escalation is to keep transactions short and to reduce the lock footprint of expensive queries so that the lock escalation thresholds are not exceeded. There are several ways to obtain this goal, many of which are listed:
- Break up large batch operations into several smaller operations. For example, suppose you ran the following query to remove several hundred thousand old records from an audit table, and then you found that it caused a lock escalation that blocked other users: By removing these records a few hundred at a time, you can dramatically reduce the number of locks that accumulate per transaction and prevent lock escalation. For example:
DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
SET ROWCOUNT 500
DELETE FROM LogMessages WHERE LogDate < '2/1/2002'
IF @@ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
- Reduce the query's lock footprint by making the query as efficient as possible. Large scans or large numbers of Bookmark Lookups may increase the chance of lock escalation; additionally, it increases the chance of deadlocks, and generally adversely affects concurrency and performance. After you find the query that causes lock escalation, look for opportunities to create new indexes or to add columns to an existing index to remove index or table scans and to maximize the efficiency of index seeks. Consider pasting the query into a Query Analyzer query window to perform an automatic index analysis on it. To do so, on the Query menu, click Index Tuning Wizard in SQL Server 2000, or click Perform Index Analysis in SQL Server 7.0.
One goal of this optimization is to make index seeks return as few rows as possible to minimize the cost of Bookmark Lookups (maximize the selectivity of the index for the particular query). If SQL Server estimates that a Bookmark Lookup logical operator may return many rows, it may use a PREFETCH to perform the bookmark lookup. If SQL Server does use PREFETCH for a bookmark lookup, it must increase the transaction isolation level of a portion of the query to repeatable read for a portion of the query. This means that what may look similar to a SELECT statement at a read-committed isolation level may acquire many thousands of key locks (on both the clustered index and one nonclustered index), which can cause such a query to exceed the lock escalation thresholds. This is especially important if you find that the escalated lock is a shared table lock, which, however, is not commonly seen at the default read-committed isolation level. If a Bookmark Lookup WITH PREFETCH clause is causing the escalation, consider adding additional columns to the nonclustered index that appears in the Index Seek or the Index Scan logical operator below the Bookmark Lookup logical operator in the query plan. It may be possible to create a covering index (an index that includes all columns in a table that were used in the query), or at least an index that covers the columns that were used for join criteria or in the WHERE clause if including everything in the select column list is impractical.
A Nested Loop join may also use PREFETCH, and this causes the same locking behavior.
For more information, click the following article number to view the article in the Microsoft Knowledge Base:260652 Nested loop join that uses a "BOOKMARK LOOKUP ...WITH PREFETCH" may hold locks longer
- Lock escalation cannot occur if a different SPID is currently holding an incompatible table lock. Lock escalation always escalates to a table lock, and never to page locks. Additionally, if a lock escalation attempt fails because another SPID holds an incompatible TAB lock, the query that attempted escalation does not block while waiting for a TAB lock. Instead, it continues to acquire locks at its original, more granular level (row, key, or page), periodically making additional escalation attempts. Therefore, one method to prevent lock escalation on a particular table is to acquire and to hold a lock on a different connection that is not compatible with the escalated lock type. An IX (intent exclusive) lock at the table level does not lock any rows or pages, but it is still not compatible with an escalated S (shared) or X (exclusive) TAB lock. For example, assume that you must run a batch job that modifies a large number of rows in the mytable table and that has caused blocking that occurs because of lock escalation. If this job always completes in less than an hour, you might create a Transact-SQL job that contains the following code, and schedule the new job to start several minutes before the batch job's start time: This query acquires and holds an IX lock on mytable for one hour, which prevents lock escalation on the table during that time. This batch does not modify any data or block other queries (unless the other query forces a table lock with the TABLOCK hint or if an administrator has disabled page or row locks by using an sp_indexoption stored procedure).
SELECT * FROM mytable (UPDLOCK, HOLDLOCK) WHERE 1=0
WAITFOR DELAY '1:00:00'
Error: 1204, Severity: 19, State: 1
The SQL Server cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users or ask the system administrator to check the SQL Server lock and memory configuration.
Using a lock hint such as ROWLOCK only alters the initial lock plan. Lock hints do not prevent lock escalation.
The other methods of preventing lock escalation that are discussed earlier in this article are better options than enabling the trace flag. Additionally, the other methods generally result in better performance for the query than disabling lock escalation for the whole instance. Microsoft recommends enabling this trace flag only to mitigate severe blocking that is caused by lock escalation while other options, such as those discussed earlier in this article, are being investigated. To enable a trace flag so that it is turned on whenever SQL Server is started, add it as a server startup parameter.
To add a server startup parameter, right-click the server in SQL Enterprise Manager, click Properties, and then on the General tab, click Startup Parameters, and then add the following parameter (exactly as shown):
DBCC TRACEON (1211, -1)