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.