sp_chgattribute

Description

Changes the max_rows_per_page, fillfactor, reservepagegap, or exp_row_size value for future space allocations of a table or an index; sets the concurrency_opt_threshold for a table. Provides the user interface for optimistic index locking.

Syntax

sp_chgattribute objname,
	{"max_rows_per_page" | "fillfactor" | "reservepagegap" | 
	"exp_row_size" | "concurrency_opt_threshold" | 
	“optimistic_index_lock” | “identity_burn_max” | "plldegree"
	"ptn_locking"}, value,  optvalue
sp_chgattribute objname,
	{"identity_gap", set_number |
	“dealloc_first_txtpg”, value}

Parameters

objname

is the name of the table or index for which you want to change attributes.

max_rows_per_page

specifies the row size. Use this for tables with variable-length columns.

fillfactor

specifies how full Adaptive Server will make each page when it is re-creating an index or copying table pages as a result of a reorg rebuild command or an alter table command to change the locking scheme. The fillfactor percentage is relevant only at the time the index is rebuilt. Valid values are 0–100.

reservepagegap

specifies the ratio of filled pages to empty pages that are to be left during extent I/O allocation operations. For each specified num_pages, an empty page is left for future expansion of the table. Valid values are 0–255. The default value is 0.

exp_row_size

reserves a specified amount of space for the rows in data-only locked tables. Use this option to reduce the number of rows being forwarded, which can be expensive during updates. Valid values are 0, 1, and any value between the minimum and maximum row length for the table. 0 means a server-wide setting is applied, and 1 means to fully pack the rows on the data pages.

concurrency_opt_threshold

specifies the table size, in pages, at which access to a data-only-locked table should begin optimizing for reducing I/O, rather than for concurrency. If the table is smaller than the number of pages specified by concurrency_opt_threshold, the query is optimized for concurrency by always using available indexes; if the table is larger than the number of pages specified by concurrency_opt_threshold, the query is optimized for I/O instead. Valid values are -1 to 32767. Setting the value to 0 disables concurrency optimization. Use -1 to enforce concurrency optimization for tables larger than 32767 pages. The default is 15 pages.

optimistic_index_lock

enables a performance optimization that eliminates contention on the root page of an index. If the root page must change because of index splits, an exclusive table is acquired. For this reason, optimistic_index_lock is appropriate for tables where the number of modifications is relatively small. Valid values are 1 to turn on optimistic index locking or 0 to turn off optimistic index locking which is the default.

identity_burn_max

allows you to set the identity burn max value of a table. This parameter uses a varchar datatype.

identity_gap

indicates that you want to change the identity gap.

value

is the numeric input value for the various options you specify in the sp_chgattribute.

optvalue

is the new value. Valid values and default values depend on which parameter is specified. This parameter is only used by the identity_burn_max parameter. For other parameters, this value is NULL.

set_number

is the new size of the identity gap.

dealloc_first_txtpg

updates a text or image column to null. Sets the corresponding text pointer to null after deallocating the previously referenced text or image pages. This result in reduced space allocation for null text/images columns. Valid values are default 0, which does not deallocate text or image pages on null update, and 1, which sets the deallocation on.

plldegree

specifies the maximum number of threads the query optimizer can use.

ptn_locking

enables or disables locking at the partition level.

Examples

Example 1

Sets the max_rows_per_page to 1 for the authors table for all future space allocations:

sp_chgattribute authors, "max_rows_per_page", 1

Example 2

Sets the max_rows_per_page to 4 for the titleidind index for all future space allocations:

sp_chgattribute "titles.titleidind", "max_rows_per_page", 4

Example 3

Specifies a fillfactor of 90 percent for pages in title_ix:

sp_chgattribute "titles.title_ix", "fillfactor", 90

Example 4

Sets the exp_row_size to 120 for the authors table for all future space allocations:

sp_chgattribute "authors", "exp_row_size", 120

Example 5

Sets the reservepagegap to 16 for the titleidind index for all future space allocations:

sp_chgattribute "titles.titleidind", "reservepagegap", 16

Example 6

Turns off concurrency optimization for the titles table:

sp_chgattribute "titles", "concurrency_opt_threshold", 0

Example 7

Sets the identity gap for mytable to 20:

sp_chgattribute "mytable", "identity_gap", 20

Example 8

Changes mytable to use the identity burning set factor setting instead of the identity_gap setting:

sp_chgattribute "mytable", "identity_gap", 0

Example 9

Sets the value of sp_chgattribute to 1, turning the optimistic index locking feature on.

sp_chgattribute "mytable", "optimistic_index_lock", 1

Example 10

Sets the value of sp_chgattribute to 0, turning the optimistic index locking feature off.

sp_chgattribute "mytable", "optimistic_index_lock", 0

Example 11

Switches the deallocation for text and image space on using dealloc_first_txtpg:

sp_chgattribute "mytable", "dealloc_first_txtpg", 1

To switch the feature off:

sp_chgattribute "mytable", "dealloc_first_txtpg", 0

Example 12

Changes the identity_burn_max value for the authors table to 5:

sp_chgattribute "authors", "identity_burn_max", 0, 5

Example 13

Tells the query optimizer to use a maximum of four threads:

sp_chgattribute my_table, "plldegree", 4

The query optimizer may choose less than four threads if it does not find enough resources. The same mechanism can be applied to an index. For example, the following example uses an index called auth_ind exists on authors to use two threads to access it:

sp_chgattribute "authors.auth_ind", "plldegree", 4

You must run sp_chgatttribute from the current database.

Usage

Permissions

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

Granular permissions enabled

With granular permissions enabled, you must be the object owner.

Granular permissions disabled

With granular permissions disabled, you must be the object owner.

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

Commands alter table, create index, create table

System procedures sp_helpindex