page lock promotion HWM (high-water mark), with page lock promotion LWM (low-water mark) and page lock promotion PCT (percentage), specifies the number of page locks permitted during a single scan session of a page-locked table or index before SAP ASE attempts to escalate from page locks to a table lock.
|
Summary Information |
|
|---|---|
|
Default value |
200 |
|
Range of values |
2–2147483647 |
|
Status |
Dynamic |
|
Display level |
Intermediate |
|
Required role |
System administrator |
|
Configuration groups |
Lock Manager, SQL Server Administration |
When the number of page locks acquired during a scan session exceeds page lock promotion HWM, SAP ASE attempts to acquire a table lock. page lock promotion HWM value cannot be higher than number of locks.
For more detailed information on scan sessions and setting up page lock promotion limits, see Performance and Tuning Series: Locking and Concurrency Control > Locking Configuration and Tuning.
The default value for page lock promotion HWM is appropriate for most applications. To avoid table locking, you may want to increase the value. For example, if you know that there are regular updates to 500 pages of an allpages-locked or datapages-locked table containing thousands of pages, increase concurrency for the tables by setting page lock promotion HWM to 500.
You can also configure lock promotion of page-locked tables and views at the object level. See sp_setrowlockpromote in the Reference Manual: Procedures.
Use sp_sysmon to see how changing page lock promotion HWM affects the number of lock promotions. sp_sysmon reports the ratio of exclusive page to exclusive table lock promotions and the ratio of shared page to shared table lock promotions. See the Performance and Tuning Series: Monitoring SAP Adaptive Server with sp_sysmon.