collect_statistics_on_dml_updates option [database]

Controls the gathering of statistics during the execution of data-altering DML statements such as INSERT, DELETE, and UPDATE.

Allowed values

On, Off

Default

On

Remarks

The database server updates statistics during normal statement execution and uses the gathered statistics to self-tune the column statistics. Set the collect_statistics_on_dml_updates option to Off to disable the updating of statistics during the execution of data-altering DML statements such as INSERT, DELETE, and UPDATE.

Under normal circumstances, it should not be necessary to turn this option off. However, in environments where significantly large amounts of data are frequently changing, setting this option to Off may improve performance—assuming update_statistics is also set to On.

The difference between the collect_statistics_on_dml_updates option and the update_statistics option is that the update_statistics option compares the actual number of rows that satisfy a predicate with the number of rows that are estimated to satisfy the predicate, and then updates the estimates accordingly. The collect_statistics_on_dml_updates option modifies the column statistics based on the values of the specific rows that are inserted, updated, or deleted.

See also