Summary information |
|
|---|---|
Default value |
200 |
Range of values |
2–2147483647 |
Status |
Dynamic |
Display level |
Intermediate |
Required role |
System Administrator |
page lock promotion HWM (high-water mark), together with the 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 Adaptive Server attempts to escalate from page locks to a table lock.
page lock promotion HWM sets a maximum number of page locks allowed on a table before Adaptive Server attempts to escalate to a table lock. When the number of page locks acquired during a scan session exceeds page lock promotion HWM, Adaptive Server attempts to acquire a table lock. The page lock promotion HWM value cannot be higher than number of locks value.
For more detailed information on scan sessions and setting up page lock promotion limits, see “Configuring locks and lock promotion thresholds” on page 286 in the Performance and Tuning Guide.
The default value for page lock promotion HWM is appropriate for most applications. You might want to raise the value to avoid table locking. 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, you can increase concurrency for the tables by setting page lock promotion HWM to 500 so that lock promotion does not occur at the default setting of 200.
You can also configure lock promotion of page-locked tables and views at the per-object level. See sp_setrowlockpromote in the Reference Manual.
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 “Lock promotions” on page 1005 in the Performance and Tuning Guide.
