sp_setrowlockpromote

Sets or changes row-lock promotion thresholds for a datarows-locked table, for all datarows-locked tables in a database, or for all datarows-locked tables on a server.

Syntax

sp_setrowlockpromote "server", NULL, new_lwm, new_hwm, new_pct

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

Parameters

server – sets server-wide values for the row lock promotion thresholds.
"database" | "table" – specifies whether to set the row-lock promotion thresholds for a database or table.
objname – is either the name of the table or database for which you are setting the row-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 high watermark (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 row lock promotion values for all datarows-locked tables in the engdb database:
```
sp_setrowlockpromote "database", engdb, 400, 400,95
```
Example 2 – Sets row lock promotion values for the sales table:
```
sp_setrowlockpromote "table", sales, 250, 250, 100
```

Usage

There are additional considerations when using sp_setrowlockpromote:

You can display database-level lock promotions using sp_helpdb dbname and table-level locks using sp_helpdb tablename.
sp_setrowlockpromote sets or changes row-lock promotion thresholds for a table, a database, or the SAP ASE server.

The SAP ASE server acquires row locks on a datarows-locked table until the number of locks exceeds the lock promotion threshold. If the SAP ASE server is successful in acquiring a table lock, the row locks are released.

When the number of row locks on a table exceeds the HWM, the SAP ASE server attempts to escalate to a table lock. When the number of row 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 row locks on a table is between the HWM and LWM, and the number of row locks exceeds the PCT threshold as a percentage of the number of rows in a table, the SAP ASE server attempts to escalate to a table lock.
Lock promotion is always two-tiered, that is, row locks are promoted to table locks. The SAP ASE server does not promote from row locks to page locks.
Lock promotion thresholds for a table override the database or server-wide settings. Lock promotion thresholds for a database override the server-wide settings.
To change the lock promotion thresholds for a database, you must be using the master database. To change the lock promotion thresholds for a table in a database, you must be using the database where the table resides.
Server-wide row lock promotion thresholds can also be set with sp_configure. When you use sp_setrowlockpromote to change the values server-wide, it changes the configuration parameters, and saves the configuration file. When you first install SAP ASE, the server-wide row lock promotion thresholds set by the configuration parameters are:
- row lock promotion HWM – 200
- row lock promotion LWM – 200
- row lock promotion PCT – 100
See the System Administration Guide for more information.
The system procedure sp_sysmon reports on row lock promotions.
Database-level row lock promotion thresholds are stored in the master..sysattributes table. If you dump a database, and load it only another server, you must set the row lock promotion thresholds on the new server. Object-level row lock promotion thresholds are stored in the sysattributes table in the user database, and are included in the dump.

Permissions

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

Setting	Description
Enabled	With granular permissions enabled, you must be a user with `manage lock promotion 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