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.
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 | null, REMOVE_SKEW_FROM_DENSITY REMOVE_STICKINESS
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.
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.
modifies all column group for this table. Because “all” is a keyword, it requires quotes.
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.
removes the stickiness associated with the specified column. Specify null to remove the stickiness from all columns in the table.
“Stickiness” occurs when Adaptive Server retains the memory for these update statistics parameters:
using step values
sampling
histogram_tuning_factor
hashing
no_hashing
partial_hashing
Once a phrase is “sticky,” Adaptive Server retains its behavior for that column on subsequent update statistics commands, even if you do not explicitly specify the parameters.
modifies the range cell density.
modifies the total cell density.
ignore the current value and use the number specified by the value parameter.
multiply the current statistical value by the value parameter.
is either the specified density value or a multiple for the current density. Must be between zero and one, inclusive, if absolute is specified.
is the name of a column in that table.
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.
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"
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"
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"
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
Allows the system administrator to modify the density values of a column—or columns—in sysstatistics.
Use optdiag to view a table’s statistics. See the Performance and Tuning Guide for more information about table density and using optdiag.
Any modification you make to the statistics with sp_modifystats is overwritten when you run update statistics. To make sure you are using the most recent statistical modifications, you should run sp_modifystats after you run update statistics.
Because sp_modifystats modifies information stored in the sysstatistics table, you should make a backup of statistics before execute running sp_modifystats in a production system.
The permission checks for sp_modifystats differ based on your granular permissions settings.
Granular permissions enabled |
With granular permissions enabled, you must be a user with update statistics on the object or with manage any statistics privilege. You must have execute permission on the procedure. |
Granular permissions disabled |
With granular permissions disabled, you must be a user with with update statistics on the object or sa_role. You must have execute permission on the procedure. |
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 |
|
sysstatistics
