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

Comments

• Anonymous
  January 31, 2005
  Cool link and article... I learned something new today!!!
• Anonymous
  January 31, 2005
  Is there a way to force SQL Server to use row locks only?
• Anonymous
  January 31, 2005
  Yes - the methods described in the article will force SQL to only use Row locks