sp_setpglockpromote

Description

Sets or changes the lock promotion thresholds for a database, for a table, or for Adaptive 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

Permissions

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

Granular permissions enabled

With granular permissions enabled, you must be a user with manage lock premotion threshold privilege.

Granular permissions 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:

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_dropglockpromote, sp_help, sp_helpdb