Microsoft KB Archive/151116

= INF: Tunable Lock Escalation in SQL Server 6.0 and 6.5 =

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

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.