sp_modifystats

Description

Allows the System Administrator, or any user with permission to execute the procedure and update statistics on the target table, to modify the density values of columns in sysstatistics.

Syntax

sp_modifystats [database].[owner].table_name,
	{"column_group" | "all"},
	MODIFY_DENSITY,
	{range | total},
	{absolute | factor},
	"value"

Or:

sp_modifystats [database].[owner].table_name, column_name,
	REMOVE_SKEW_FROM_DENSITY

Parameters

table_name

is the name of the table to change. Specify the database name if the table is in another database, and specify the owner’s name if more than one table of that name exists in the database. The default value for owner is the current user, and the default value for database is the current database.

column_group

an ordered list of column names. To change a statistic for multiple columns (such as a density value), list the columns in the order used to create the statistic. Separate the column names with commas. For example, if your table has a density statistic on columns a1, a2, a3, a4:

  • a1” modifies column a1.

  • a1,a2,a3” modifies the column group a1,a2,a3,

  • You can also use a wildcard character , %, with the column_group parameter to represent a range of characters. For example, “a1,%,a3” modifies the groups a1,a2,a3 and a1, a4, a3, and so on; “a1,%” modifies the groups a1,a2 and a1,a2,a3, and so on, but not a1; “a1%” modifies the groups a1,a2 and a1,a2,a3, and so on, as well as a1.

all

modifies all column group for this table. Because “all” is a keyword, it requires quotes.

MODIFY_DENSITY

allows you to modify either the range or total density of a column or column group to the granularity specified in the value parameter. Range cell density represents the average number of duplicates of all values that are represented by range cells in a histogram. See the Performance and Tuning Guide for more information.

range

modifies the range cell density.

total

modifies the total cell density.

absolute

ignore the current value and use the number specified by the value parameter.

factor

multiply the current statistical value by the value parameter.

value

is either the specified density value or a multiple for the current density. Must be between zero and one, inclusive, if absolute is specified.

column_name

is the name of a column in that table.

REMOVE_SKEW_FROM_DENSITY

allows the System Administrator to change the total density of a column to be equal to the range density, which is useful when data skew is present. Total density represents the average number of duplicates for all values, those in both frequency and range cells. Total density is used to estimate the number of matching rows for joins and for search arguments whose value is not known when the query is optimized. See the Performance and Tuning Guide for more information.

REMOVE_SKEW_FROM_DENSITY also updates the total density of any composite column statistics for which this column is the leading attribute. Most commonly, a composite index for which this column is the leading attribute would produce these composite column statistics, but they can also be produced when you issue a composite update statistics command.

Examples

Example 1

Changes the range density for column group c00, c01 in table tab_1 to 0.50000000:

sp_modifystats  "tab_1", "c00, c01", MODIFY_DENSITY, range, absolute, "0.5"

Example 2

The total density for column group c00, c01 in tab_1 is multiplied by .5. That is, divided in half:

sp_modifystats  "tab_1", "c00,c01", MODIFY_DENSITY, total, factor, "0.5"

Example 3

The total density for all the columns in table tab_1 is multiplied by .5.

sp_modifystats  "tab_1", "all", MODIFY_DENSITY, total, factor, "0.5"

Example 4

Total density for all column groups starting with c12 is changed to equal the range density.

sp_modifystats "tab_1", "c12" REMOVE_SKEW_FROM_DENSITY

Usage

Permissions

No one has default use of sp_modifystats. A person with sso_role must specify the permissions on sp_modifystats.

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

Tables used

sysstatistics

See also

Command update statistics