Microsoft KB Archive/151116

From BetaArchive Wiki
< Microsoft KB Archive
Revision as of 10:11, 21 July 2020 by X010 (talk | contribs) (Text replacement - """ to """)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Knowledge Base


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