Setting server-wide lock promotion thresholds

The following command sets the server-wide page lock promotion LWM to 100, the page lock promotion HWM to 2000, and the page lock promotion PCT to 50 for all datapages-locked and allpages-locked tables:

sp_setpglockpromote "server", null, 100, 2000, 50

In this example, the task does not attempt to promote to a table lock unless the number of locks on the table is between 100 and 2000.

If a command requires more than 100 but less than 2000 locks, Adaptive Server compares the number of locks to the percentage of locks on the table.

If the number of locks is greater than the number of pages resulting from the percentage calculation, Adaptive Server attempts to issue a table lock.

sp_setrowlockpromote sets the configuration parameters for all datarows-locked tables:

sp_setrowlockpromote "server", null, 300, 500, 50

The default values for lock promotion configuration parameters are likely to be appropriate for most applications.