sp_setrowlockpromote

Description

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

Permissions

Only a System Administrator can execute sp_setrowlockpromote.

Auditing

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

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • 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

See also

System procedures sp_configure, sp_droprowlockpromote, sp_helpdb, sp_sysmon