This stored procedure should be included in the Reference Manual Volume 3: Procedures.
Allows the System Administrator to modify the density values of a column—or 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,
REMOVE_SKEW_FROM_DENSITY
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.
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
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.
Only a System Administrator can execute sp_modifystats.
Command |
update statistics |
System table |
sysstatistics |
