Problem Statement

In one of my application, we are seeing intermittent performance issues related with the database calls. The response time is anywhere between the range of 1 sec – 60 sec and has been a constant bottleneck to the system users.

After doing some quick analysis, it seems like we are bumping into a database locking issue. There is an ETL Job which runs every 5 min to update a large number of records in the SQL Database. The job runs for almost 4-5 min itself. This job seems to be locking the tables while updating records. During this time frame, any database call from the User Interface is getting delayed and hence the intermittent performance issues.

This is an interesting problem to research and solve. I am sure there will lot of learning and new findings during this process, hence I thought of starting a blog series as I troubleshoot this performance issue over the next 2 weeks.

To begin with, the problem here seems to be ‘Lock Escalation in SQL Server‘.  As part of the ETL process, there are updates to large number of rows. As a result, SQL Server seems to be applying an exclusive lock on the tables to ensure proper use of system memory and resources. This is blocking individual database calls from the UI and Service layer.

What is Lock Escalation?

Lock Escalation is the process of converting many fine-grained locks such as row/page locks into table locks. Microsoft SQL Server dynamically determines when to perform lock escalation. 

In general, maintaining locks requires memory resources. SQL Server uses row level locking by default, however it is far more optimal for the SQL Engine to convert a large number of row locks into a single table lock, and in the process release memory held by the row locks. 

“Lock Escalation” is an optimization technique used by SQL Server and it is basically how it handles locking for large updates. When SQL Server is going to modify a large number of rows – it is more efficient for the database engine to take fewer, larger locks (table lock) instead of dealing with a large number of individual locks (row locks).

Lock Hierarchy in SQL Server starts at Database level and goes down to Row level —

Database –> Table –> Page –> Row

What is Lock Escalation Threshold?

As per MSDN documentation, Lock escalation is triggered when a Transact-SQL statement acquires at least 5,000 locks on a single reference of a table. 

While the Database Engine checks for possible escalations at every 1250 newly acquired locks, a lock escalation will occur if and only if a Transact-SQL statement has acquired at least 5000 locks on a single reference of a table.

What are Lock Escalation Mode in SQL Server?

You can check the lock escalation mode on the tables in your database —

Lock Escalation Modes –

• Table (default)
• Auto
• Disable

Modifying Lock Escalation —

Disabling the Lock Escalation might not be the best option, and you might bump into high memory consumption issues.

How to prevent Lock Escalations?

You can reduce the locking issues by following the below 3 good practices —

• Keep transactions shorter.
• Reduce lock footprint of expensive queries by doing performance tuning and making them efficient.
• Break up large operations into batches.

Reference –

Lock Escalation (Database Engine)

Categories: Performance Tuning, SQL Server