Sets or changes the lock promotion thresholds for a database, for a table, or for Adaptive Server.
sp_setpglockpromote {"database" | "table"}, objname, new_lwm,
new_hwm, new_pct
sp_setpglockpromote server, NULL, new_lwm, new_hwm, new_pct
sets server-wide values for the lock promotion thresholds.
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.
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.
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.
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.
specifies the value to set for the lock promotion percentage (PCT) threshold. PCT must be between 1 and 100. This parameter can be null.
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
Sets lock promotion thresholds for the master database:
sp_setpglockpromote "database", master, 1000, 1100, 45
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
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
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 Adaptive Server.
Adaptive 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 Adaptive 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, Adaptive Server attempts to escalate to a table lock. When the number of locks on a table is below the LWM, Adaptive 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, Adaptive 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 Adaptive 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. Adaptive 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.
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. |
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 |
|
System procedures sp_configure, sp_dropglockpromote, sp_help, sp_helpdb
