Article ID: 151116
Article Last Modified on 2/14/2005
APPLIES TO
- Microsoft SQL Server 6.0 Standard Edition
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q151116
SUMMARY
Microsoft SQL Server versions 6.0 and 6.5 provide tunable lock escalation. This means that when SQL Server has to use more page locks for executing a single statement, it will automatically escalate to a table lock based on the lock escalation configuration parameters.
This article helps explain how many page locks will be acquired before "lock escalation" occurs for a given set of run time configuration values as defined by sp_configure.
MORE INFORMATION
The following is the decision tree which is used to determine when the number of page locks held on a table should be escalated to a TABLE lock.
Abbreviations used are :
LEmax : LE threshold maximum
LEmin : LE threshold minimum
LEpercent : LE threshold percent
TABesc : number of pages when lock escalation occurs
Total : Number of table pages
if ( LEpercent = 0 ) then
TABesc = LEmax -- LE threshold min has no significance
else
TABesc = min { max ( LEmin, LEpercent*Total) , min ( LEmax,
LEpercent*Total) }
NOTES:
In the algorithm discussed above, extent locks do not count toward the "lock escalation."
Also, the above applies to individual statements and not to transactions.
Keywords: kbhowto kbusage KB151116
