Altering lock escalation for SQL Server tables

By: Eli Leiba   |   Updated: 2016-07-28   |   Comments   |   Related: > Locking and Blocking

Problem

You want to programmatically change the default mode that SQL Server uses with regard to the lock escalation and you want to do it for specific tables of your choice. In other words, you want to control the trade-off between concurrency and memory needs at the table level. How can this be accomplished?

Solution

SQL Server's locking mechanism uses memory resources to maintain locks. In situations where the number of row or page locks increases to a level that decreases the server's memory resources to a minimal level, SQL Server's locking strategy converts these locks to entire table locks, thus freeing memory held by the many single row or page locks to one table lock. This process is called lock escalation, which frees memory, but reduces table concurrency. The default lock escalation mode is called TABLE, it implements SQL Server's lock escalation on all types of tables whether partitioned or not partitioned.

There are two more lock escalation modes: AUTO and DISABLE.

• The AUTO mode enables lock escalation for partitioned tables only for the locked partition. For non-partitioned tables it works like TABLE.
• The DISABLE mode removes the lock escalation capability for the table and that is important when concurrency issues are more important than memory needs for specific tables.

You can check the lock escalation mode for a table by querying the sys.tables and sys.schemas system views like this:

SELECT s.name as schemaname, object_name (t.object_id) as table_name, t.lock_escalation_desc
FROM sys.tables t, sys.schemas s
WHERE object_name(t.object_id) = 'Products' 
and s.name = 'dbo' 
and s.schema_id = t.schema_id 

In order to control the lock escalation I have created a stored procedure that gets the desired lock escalation mode, the schema name and the table name or partial table name for changing the lock escalation mode on multiple tables with a similar pattern.

So for example if we pass in the value 'order', any table that has order in the name will be changed. As a safeguard, I also added a pattern length parameter. The default is five characters, but this can be overridden if there is a need to search for larger or smaller pattern matches like 'ord'. Lastly, it will only update user tables and not system tables by using the is_ms_shipped column.

Here is the stored procedure T-SQL code. This will print out the ALTER TABLE statements, but if you want to execute them you can uncomment the EXEC (@tsql) line. The @mode value should be AUTO or DISABLE.

CREATE PROCEDURE dbo.usp_ChangeLockEscalationMode 
   (@mode varchar(10),
   @schema varchar(30),
   @tableNamepattern varchar(30),
   @patternLen int = 5)
AS
BEGIN
   DECLARE @tsql varchar(200)
   DECLARE @tablename varchar(60)
 
   DECLARE cur CURSOR FOR
    ( SELECT object_name (t.object_id) as table_name
      FROM sys.tables t , sys.schemas s
      WHERE charindex ( @tableNamepattern , object_name (t.object_id) ,1) > 0 
       and s.schema_id = t.schema_id 
       and s.name = @schema
       and t.is_ms_shipped = 0
       and len(@tableNamepattern) >= @patternLen )
   
   OPEN cur
   FETCH NEXT FROM cur INTO @tablename
 
   WHILE @@FETCH_STATUS = 0
   BEGIN
      SET @tsql = 'ALTER TABLE ' + @schema + '.[' + @tableName + ']'+ ' SET ' + 
      '( LOCK_ESCALATION = ' + @mode + ' )'

      PRINT @tsql 
      --EXEC (@tsql)

      FETCH NEXT FROM cur INTO @tablename
   END

   CLOSE cur
   DEALLOCATE cur
END
GO 

An example for using the procedure

--In the Northwind database, DISABLE lock escalation for both the Orders and [Order Details] tables
USE Northwind
GO

EXEC dbo.usp_ChangeLockEscalationMode @mode = 'DISABLE', @schema = 'dbo', @tableNamepattern = 'Order'
GO

Now check the lock escalation mode:

SELECT s.name as schemaname,object_name (t.object_id) as table_name,t.lock_escalation_desc
FROM sys.tables t, sys.schemas s
WHERE object_name (t.object_id) like '%order%' 
and s.name = 'dbo' 
and s.schema_id = t.schema_id 

Further notes:

• The procedure was tested with SQL Server 2012, 2014 Developer editions.
• The lock escalations discussed in this tip are only between page or row locks to the table or partition level.

Next Steps

• Find the tables with high concurrency needs that exceed memory needs
• Divide the list found in step 1 to two lists: partitioned or non-partitioned tables
• Consider changing the lock escalation mode to AUTO for the partitioned part and DISABLE for the non-partitioned tables
• Read more tips on SQL Server Locking and Blocking

About the author

Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips