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

Name: Update index statistics
Category: Maintenance
Description: This task runs UPDATE STATISTICS on all user-defined and internal tables across all non-system databases ensuring that the most current statistics are available for the SQL query optimizer, leading to potentially better performance and efficiency in database operations.

Purpose

The primary purpose of this job is to ensure that the SQL server’s query optimizer has the most current information regarding the distribution of data within the indexes of the databases. This optimized data availability helps in enhancing the performance of SQL queries by enabling the query optimizer to make more informed decisions about the execution plans for those queries.

Importance

Performance Optimization: Regularly updating statistics ensures that the performance of SQL queries remains optimal, especially under conditions where database content changes frequently.
Query Efficiency: With current statistics, the query optimizer can reduce the time and computational overhead needed to execute queries.
Reduced Latency: Helps in minimizing the decision-making time for the execution of queries, thereby reducing the overall latency and improving user experience.

Frequency and Scheduling

The job is set to perform its duties based on a default schedule specified in its configuration (Daily at 3:15 AM).

Manual Checking

For a manual update of statistics, the following SQL command can be issued in the management studio:

UPDATE STATISTICS <table_or_indexed_view_name> WITH FULLSCAN;

Execution Conditions

It executes against ONLINE databases which have UserAccess set to MULTI_USER and Updateability to READ_WRITE.
Does not include Columnstore indexes in the update.
Ignores databases that are either specified in the ‘ignore databases’ parameter or those set to working hours if ‘disable during working hours’ is enabled.

Parameters Managed (excluding detailed description)

ignore databases
disable during working hours
update statistics for sys schema
update statistics for clustered indexes
update statistics for non clustered indexes
max elap time
continue on next run
with fullscan
collect statistics
history threshold
include column and index statistics

Dependencies

For successful execution, this job depends on multiple database objects like tables and procedures specifically created for managing the update statistics tasks and handling errors.

Handling Changes and Versions

This job has evolved over time with upgrades from versions 1.7 to 2.3: Enhancements include additional parameters like ‘collect statistics’ and modifications in stored procedures to better handle exceptions and ensure comprehensive updates.

Future Considerations and Improvement

Consider the automated adjustment of the frequency of statistics updates based on the rate of database modifications.
Inclusion of AI for predicting table growths and modifications which may help in optimizing the timing of statistics updates.
Enhanced error reporting and diagnostics to provide deeper insights during failures or incomplete operations.

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