Update statistics

Job details

Parameters

Job Summary

• Purpose: The purpose of this job is to update statistics on all user-defined and internal tables across non-system databases in instances of SQL Server newer than version 2008 or Azure SQL Managed Instances. It is designed to improve query performance by ensuring the statistics are frequently updated which assists in optimized query planning.

• Why: This job is crucial because maintaining fresh statistics helps the SQL Server query optimizer to create more efficient query execution plans. Over time, as data modifications occur in the databases, statistics can become outdated and lead to suboptimal query performance. By performing updates outside the default behavior, this job proactively manages the performance and efficiency of database operations.

• Manual checking: To manually check the state of statistics updates, the following SQL command can be issued in the respective database:

EXEC sp_updatestats;

Operation Details

• The stored procedure sp_updatestats is executed across all selected databases (excluding any databases listed specifically to be ignored or during blackout periods defined by working hours).
• The process mainly includes:
  • Filtering out system databases and any that are listed to be disregarded.
  • Running the update statistics command inside each valid database.
  • Collecting and logging updates made into a history table for further analysis or rollback if necessary.

Key Operations and Logic Utilization

• Ignore Databases: Any databases included in the parameter ‘ignore databases’ are excluded from the update statistics operation.
• Disable During Working Hours: Statistics updates can be configured not to run during specified working hours, thus reducing potential performance impact during peak operational times.

Scheduled Execution

• The job is scheduled to run every day at 10:15 PM but can be customized based on environmental needs or preferences.

Dependencies and Post-implementation Details

• Task Dependence: The procedure depends on a reliable completion of update statistics commands and logs details in the history table named update_tabs_histr_tab.
• Post-implementation: Upon successful completion or failure, the system will log details such as the number of databases and tables for which the statistics have been updated, thus providing visibility into the process outcomes.

Versioning and Upgrades

• Current Version: 1.2
• Previous Version: 1.1
• The upgrade from version 1.1 involves ensuring updates align with the newer operational protocols and maintaining backward compatibility.

Reporting

• Presentation Layer: The results from the update_tabs_histr_tab are visualized in the report section of dbWatch, showing top 100 databases based on the number of tables analyzed.
• Data Fields: Database, Tables Analyzed, Start and End Time of Analysis, Rows in Tables, Rows Sampled, Rows Modifications.

Report Table Structure

This detailing aids in proactive management and scheduling of database maintenance tasks, essential for maintaining high levels of performance and efficiency.