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

Only a System Administrator can execute sp_setpglockpromote.

See also

System procedures sp_configure, sp_dropglockpromote, sp_help, sp_helpdb