Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Using Lock Escalation to Improve Update Query Performance
SQL Server Performance Issues
SQL BOL: "Lock escalation is the process of converting many fine-grain locks into fewer coarse-grain locks, reducing system overhead."
IIn 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.
[color maroon]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.[/color]
I also found the following in BOL.
[color maroon]The use of commas between table hints is optional but encouraged. Separation of hints by spaces rather than commas is supported for backward compatibility.[/color]