SQL Server Lock Escalation – Friend of Foe?

Sometimes I hear the comment that someone can’t get their application to scale above 50 – 100 users; above a certain number of users there is a sudden ceiling in scalability.

So often I have found this to be a problem with nothing more than SQL Server’s ‘method’ of lock escalation.

What is lock escalation?

Lock escalation is the process of escalating a significant number of row level locks to a single table lock.  This is trick performed by SQL Server as a way of conserving memory and improving performance.  The problem is it works well for smaller systems but can easily throttle performance on larger systems when the activity reaches a certain threshold.

Since locks take up memory and take time to apply you can imaging why instead of taking out thousands of row level locks on a particular table, SQL decides just to take out a single table level lock; well its so much quicker and cheaper.  However there can suddenly come a point when this table level lock becomes a major obstacle to concurrency.  If you find you have processes that keep on waiting for access to a particular table – lock escalation may no longer be your friend and may have become your foe.

The following link takes you to the MS KB article on the subject and describes ways of avoiding the problem as well as pointing out the warning that you need to ensure your system has enough memory to employ a full row level locking strategy.

Resolving blocking problems that are caused by lock escalation in SQL Server

 https://support.microsoft.com/default.aspx?scid=kb;en-us;323630