Update Statistics maintenance rule

Purpose:

• To refresh metadata used by the SQL Server query optimizer for making execution plan decisions.

Why It’s Important:

• The SQL Server query optimizer relies on statistics (data distribution and cardinality information) to choose the best query execution plan.
• As data changes over time, statistics become stale or inaccurate, leading to poor execution plans and performance degration.
• Updating statistics ensures queries run efficiently and resource usage is minimized.

Update Statistics Summary Quick Access + Include Exclude

Update Statistics Rule Configuration

1. Update Statistics Rule Summary
   Provides an overview of the current update statistics rule, including key settings and the status of its configuration.
2. Include/Exclude Update Statistics Rule Tab
   Allows customization by including or excluding specific databases or database groups for the update statistics operation.
3. Configure Rule
   Opens the Rule Wizard to access advanced configuration options. for fine- tuning the update statistics settings.

Update Statistics Exclude include rule tab

See common DB Source selection for template and Custom server – include exclude for custom server level.

Update statistics rule wizard step 1 – Main Options

Advanced Options for Index and Statistics Maintenance

1.  Toggle Options

Configure additional parameters to customize index and statistics maintenance:

1. Partition Level: Maintain partitioned indexes at the partition If enabled, fragmentation levels and page counts are checked for each partition, and the appropriate maintenance (reorganize or rebuild) is applied.
2. Sort in TempDB: Use tempdb for sort operations during index rebuilds.
3. Resumable: Enable resumable online index operations to allow interruptions and resumptions.
4. Pad Index: Apply the fill factor’s free space percentage to intermediate-level index pages.
5. LOB Compaction: Compact pages containing large object (LOB) columns during index reorganization.
6. Is Only Modified Statistics: Update statistics only if rows have been modified since the last update.
   – Data is retrieved from modification_counter in dm_db_ stats_properties (SQL Server 2008 R2 SP2+, SQL Server 2012 SP1+).
   – Earlier versions use rowmodctr in sysindexes. For incremental statistics, modification_counter in sys.dm_db incremental_stats_properties is used.

2. Minimum Number of Pages

1. Set a minimum index size (in pages) for Indexes with fewer pages are skipped. Default: 1000 pages, based on Microsoft recommendations.
2. Page size is determined using page_count in sys.dm_db_index_ physical_stats.

3. Maximum Number of Pages

1. Set a maximum index size (in pages) for Indexes larger than this threshold are skipped. Default: No limit.
2. Page size is determined using page_count in dm_db_index_ physical_stats.

4. Statistics Options

1. ALL: Update both index and column statistics.
2. INDEX: Update only index statistics.
3. COLUMNS: Update only column statistics.
4. NULL: Skip statistics maintenance (default).

5. Statistics Modification Level

1. Specify the percentage of modified rows that trigger a statistics update.
2. Updates are also triggered dynamically when the number of modified rows meets a threshold: SQRT(number of rows * 1000).
3. Data Sources:
   – SQL Server 2008 R2 SP2+ and SQL Server 2012 SP1+: Uses modification_counter and rows in dm_db_stats_properties.
   – Earlier Versions: Uses rowmodctr and rowcnt in sysindexes.
   – Incremental Statistics: Uses modification_counter and rows in dm_db_incremental_stats_properties.

6. Statistics Sample

1. Specify the percentage of the table to sample for statistics updates:
   – 100%: Equivalent to a full scan.
   – Unspecified: SQL Server automatically determines the sample size.
2. The StatisticsSample option in IndexOptimize uses SAMPLE and FULLSCAN in the SQL Server UPDATE STATISTICS command.

Update statistics rule wizard step 2 – Advanced Options

Index Maintenance Configuration: Time and Lock Settings

1. Time Limit

1. Specify the maximum time, in seconds, after which no additional commands are executed.
2. Default: No time limit.

2. Time Delay

1. Define a delay, in seconds, between index maintenance commands to reduce resource contention.
2. Default: No delay.

3. Max Degree of Parallelism (MaxDOP)

1. Specify the number of CPUs to use during index rebuild operations.
2. If unspecified, the global maximum degree of parallelism setting is used.
3. The MaxDOP option in IndexOptimize maps to the MAXDOP option in the SQL Server ALTER INDEX command.

4. Fill Factor

1. Define how full the index pages should be when rebuilding indexes, as a percentage.
2. Default: Uses the fill factor specified in sys.indexes.
3. The FillFactor option in IndexOptimize corresponds to the FILLFACTOR option in the SQL Server ALTER INDEX command.

5. Wait at Low Priority Max Duration (Minutes)

1. Specify the time, in minutes, an online index rebuild operation waits for low-priority locks.
2. The WaitAtLowPriorityMaxDuration option in IndexOptimize aligns with the WAIT_AT_LOW_PRIORITY and MAX_DURATION options in the SQL Server ALTER INDEX command.

6. Wait at Low Priority Abort After Wait

1. Define the action to take after an online index rebuild operation has been waiting for low-priority locks:
   

   Value	Description
   NONE	Continue waiting for locks with normal priority.
   SELF	Abort the online index rebuild operation.
   BLOCKERS	Terminate user transactions that block the online index rebuild operation.

2. The WaitAtLowPriorityAbortAfterWait option in IndexOptimize uses the WAIT_AT_LOW_PRIORITY and ABORT_AFTER_WAIT options in the SQL Server ALTER INDEX command.

7. Lock Timeout (Seconds)

1. Specify the maximum time, in seconds, that a command waits for a lock to be released.
2. Default: No time limit.
3. The LockTimeout option in IndexOptimize corresponds to the SET LOCK_TIMEOUT statement in SQL Server.

8. Lock Message Severity

Define the severity level for lock timeout and deadlock messages:

Value	Description
10	Informational message.
16	Error message (default).

9. Execution Order

Define the order in which databases are processed during index maintenance:

Value	Description
NULL	Process databases in the specified order, then sort by database name in ascending order (default).
DATABASE_NAME_ASC	Sort databases by name in ascending order.
DATABASE_NAME_DESC	Sort databases by name in descending order.
DATABASE_SIZE_ASC	Sort databases by size in ascending order.
DATABASE_SIZE_DESC	Sort databases by size in descending order.

Update statistics rule wizard step 3 – Schedule

See Scheduling options in UI for details on how to use the schedule.

Differences between template update statistics rule to custom server update statistics rule

Custom server – Include Exclude update statistics rule tab

Rule Configuration Options

1. Close Dialog and Toggle Rule
   Exit the configuration dialog while toggling the rule to enable or disable it as needed.
2. Navigate Between Rule Summary and Database Selection
   Easily switch between the rule summary view and database selection settings for streamlined configuration.
3. Choose Predefined Database Group Type
   Select a predefined database grouping to quickly apply rules to categorized sets of databases.
4. Select Specific Indexes
   Choose from a list of specific indexes to apply the rule to, enabling granular control over index maintenance.
5. Include or Exclude Databases
   Customize the rule by including or excluding specific databases on the customized server.
6. Final List of Databases
   View the final list of databases based on the chosen database group type, selected indexes, and inclusion/exclusion criteria.