By: Eli Leiba | Updated: 2016-07-28 | Comments | Locking and Blocking
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?
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
- 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.
- 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
Last Updated: 2016-07-28
About the author
View all my tips