sp_setpglockpromote

Sets or changes the lock promotion thresholds for a database, for a table, or for the SAP ASE server.

Syntax

sp_setpglockpromote {"database" | "table"}, objname, new_lwm, 
	new_hwm, new_pct

sp_setpglockpromote server, NULL, new_lwm, new_hwm, new_pct

Parameters

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. “database” and “table” are Transact-SQL keywords, so the quotes are required.
objname – is either the name of the table or database for which you are setting the lock promotion thresholds or null, if you are setting server-wide values.
new_lwm – specifies the value to set for the low watermark (LWM) threshold. The LWM must be less than or equal to the high watermark (HWM). The minimum value for LWM is 2. This parameter can be null.
new_hwm – specifies the value to set for the lock promotion HWM threshold. The HWM must be greater than or equal to the LWM. The maximum HWM is 2,147,483,647. This parameter can be null.
new_pct – specifies the value to set for the lock promotion percentage (PCT) threshold. PCT must be between 1 and 100. This parameter can be null.

Examples

Example 1 – Sets the server-wide lock promotion LWM to 200, the HWM to 300, and the PCT to 50:
```
sp_setpglockpromote "server", NULL, 200, 300, 50
```
Example 2 – Sets lock promotion thresholds for the master database:
```
sp_setpglockpromote "database", master, 1000, 1100, 45
```
Example 3 – Sets lock promotion thresholds for the titles table in the pubs2 database. This command must be issued from the pubs2 database:
```
sp_setpglockpromote "table", "pubs2..titles", 500, 700, 10
```
Example 4 – Changes the HWM threshold to 1600 for the master database. The thresholds were previously set with sp_setpglockpromote. This command must be issued from the master database:
```
sp_setpglockpromote "database", master, @new_hwm=1600
```

Usage

There are additional considerations when using sp_setpglockpromote:

You can display database-level lock promotions using sp_helpdb dbname and table-level locks using sp_helpdb tablename.
sp_setpglockpromote configures the lock promotion values for a table, for a database, or for the SAP ASE server.

The SAP ASE server acquires page locks on a table until the number of locks exceeds the lock promotion threshold. sp_setpglockpromote changes the lock promotion thresholds for an object, a database, or the server. If the SAP ASE server is successful in acquiring a table lock, the page locks are released.

When the number of locks on a table exceeds the HWM threshold, the SAP ASE server attempts to escalate to a table lock. When the number of locks on a table is below the LWM, the SAP ASE server does not attempt to escalate to a table lock. When the number of locks on a table is between the HWM and LWM and the number of locks exceeds the PCT threshold, the SAP ASE server attempts to escalate to a table lock.
Lock promotion thresholds for a table override the database or server-wide settings. Lock promotion thresholds for a database override the server-wide settings.
Lock promotion thresholds for the SAP ASE server do not need initialization, but you must initialize database and table lock promotion thresholds by specifying LWM, HWM, and PCT with sp_setpglockpromote, which creates a row for the object in sysattributes when it is first run for a database or table. Once the thresholds have been initialized, then they can be modified individually, as in Example 4.
For a table or a database, sp_setpglockpromote sets LWM, HWM, and PCT in a single transaction. If sp_setpglockpromote encounters an error while updating any of the values, then all changes are aborted and the transaction is rolled back. For server-wide changes, one or more thresholds may fail to be updated while others are successfully updated. The SAP ASE server returns an error message if any values fail to be updated.
To view the server-wide settings for the lock promotion thresholds, use sp_configure "lock promotion" to see all three threshold values. To view lock promotion settings for a database, use sp_helpdb. To view lock promotion settings for a table, use sp_help.

Permissions

The permission checks for sp_setpglockpromote differ based on your granular permissions settings.

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage lock premotion threshold` privilege.
Disabled	With granular permissions disabled, you must be a user with sa_role.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Information	Values
Event	38
Audit option	exec_procedure
Command or access audited	Execution of a procedure
Information in `extrainfo`	Roles – Current active roles Keywords or options – NULL Previous value – NULL Current value – NULL Other information – All input parameters Proxy information – Original login name, if set proxy in effect