Update index statistics


Job details

Name: Update index statistics
Platform: Sqlserver
Category: Maintenance
Premium package: Maintenance package
Description: Update statistics in all (non-system) databases.
Long description: Runs UPDATE STATISTICS against all user-defined and internal tables in all (non-system) database. By default, the query optimizer already updates statistics as necessary to improve the query plan. In some cases you can improve query performance by updating statistics more frequently than the default updates.
Version: 2.32
Default schedule: 15 3 * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version > ‘2005′ & hasengine=‘YES’]/.[eng_inst_priv = 0 & (engine_edition = ‘Microsoft SQL Server’ engine_edition = ‘Azure SQL Managed Instance’)]

Parameters

Name Default value Description
ignore databases List of databases (separated by comma) which will be ignored. You can use % (percent sign) to represent wild card characters.
disable during working hours YES This parameter disables the task to be scheduled, or to be run manually, during working hours (between 7am and 5pm).
update statistics for sys schema YES Runs UPDATE STATISTICS on each index of the system tables.
update statistics for clustered indexes YES Runs UPDATE STATISTICS for Clustered indexes.
update statistics for non clustered indexes YES Runs UPDATE STATISTICS for Non Clustered indexes.
max elap time 30 The maximum number of minutes before the dbWatch task stops running update statistics command for the remaining databases.
continue on next run YES If set to “YES“ (default) the update statistics command will continue where it left off.
with fullscan NO If set to “YES“ the UPDATE STATISTICS [table_or_indexed_view_name] is executed with “WITH FULLSCAN“ option.
collect statistics NO If set to “YES“, the procedure will save statistics (per database) for each execution. If set to “NO”, only statistics from the last execution are saved.
history threshod 30 The maximum number of days to to keep historical statistics per database. Parameter “collect statistics” must be set to “YES“.
include column and index statistics NO This parameter controls whether both column statistics and index statistics should be updated during the execution of the UPDATE STATISTICS operation. IF set to “YES“ both column and index statistics are updated.

Job Summary for dbWatch Control Center Monitoring Job

Job Details

Purpose

Importance

Frequency and Scheduling

Manual Checking

UPDATE STATISTICS <table_or_indexed_view_name> WITH FULLSCAN;

Execution Conditions

Parameters Managed (excluding detailed description)

Dependencies

Handling Changes and Versions

Future Considerations and Improvement

This comprehensive set of operations and configurations ensures the databases remain optimized for query operations, significantly enhancing performance and reliability.