Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Microsoft SQL Server: Programming FAQ

SQL Server Performance Issues

Using Lock Escalation to Improve Update Query Performance by tlbroadbent
Posted: 28 May 01

SQL BOL: "Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead."  

In theory, SQL Server escalates rowlocks and page locks into table locks when transactions exceed a dynamically determined escalation threshold. This threshold is not user configurable.

In practice, we've all seen SQL Server transactions that acquire hundreds or even thousands of page locks during an update transaction without ever seeming to escalate to table locking. If it is known that a transaction will update a large percentage of the rows in a table, we can use hints to escalate to table locking immediately. This will eliminate the need for SQL Server to acquire row or page locks and thus speed processing. The following example shows how to force table locking during an update transaction.

Begin Transaction

Select count(*) From MyTblA With (tablockx holdlock) Where 1=2
/* tablockx forces an exclusive table lock
   Use tablock to do a non-exclusive lock
    holdlock tells SQL Server to hold locks for duration of the transaction */

Update MyTblA Set Col1 = b.Col3
   From MyTblA a Inner Join MyTblB b On a.ID=b.ID
     Where a.Col1 != b.Col3

Commit Transaction

This technique should be used judiciously. Locking a table unnecessarily could have detrimental impact on other processes.

It has been noted that the word WITH is optional and actually caused syntax errors in SQL Server version 6.5.

SQL Books Online for version 7.0 indicate that WITH is optional but encouraged. The following quote is from SQL BOL version 7.0.

The use of the WITH keyword is encouraged although it is not currently required. In future releases of SQL Server, WITH may be a required keyword.

I also found the following in BOL.

The use of commas between table hints is optional but encouraged. Separation of hints by spaces rather than commas is supported for backward compatibility.

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close