sp_setpglockpromote

The sp_setpglockpromote_ptn system procedure sets partition-lock promotion thresholds at the server, database, and table level.

Syntax

The syntax for setting the partition lock promotion threshold at the server level is:

sp_setpglockpromote_ptn "server", null, new_lwm, new_hwm, new_pct

The syntax for setting the partition lock promotion threshold at the database or table level is:

sp_setpglockpromote_ptn "database | table", objname, new_lwm, new_hwm, new_pct

server – sets server-wide values for the lock promotion thresholds.
"database" | "table" – specifies whether to set the lock promotion thresholds for a database or table. These are Transact-SQL keywords and therefore, require quotes.
objname – is either the name of the partition, table, or database for which you are setting the lock promotion thresholds, or null, if you are setting server-wide values. If you are setting partition-wide values, use the format table_name.partition_name for the objname.
new_lwm – specifies a minimum number of page locks that must be acquired before SAP ASE acquires a partition lock.
new_hwm – specifies a maximum number of page locks allowed on the object before SAP ASE attempts to escalate to a partition lock.
new_pct – specifies the percentage of locked pages (based on the table size) above which SAP ASE attempts to acquire a partition lock when the number of locks is between the new_hwm and new_lwm lock promotions.

Example 1 – Sets the server-wide partition lock promotion threshold values LWM to 200, the HWM to 300, and the PCT to 50:
```
sp_setpglockpromote_ptn "server", NULL, 200, 300, 50
```
Example 2 – Sets partition lock promotion thresholds for the master database:
```
sp_setpglockpromote_ptn "database", master, 1000, 1100, 45
```
Example 3 – Sets partition lock promotion thresholds for the titles table in the pubs2 database. This command must be issued from the pubs2 database:
```
sp_setpglockpromote_ptn "table", "pubs2..titles", 500, 700, 10
```

Any user can execute sp_setpglockpromote_ptn.