Reindex maintenance rule

Purpose:

• To rebuild or reorganize indexes for improved query performance.

Why It’s Important:

• Over time, indexes become fragmented due to frequent INSERT, UPDATE, and DELETE Fragmented indexes slow down queries.
• Reindexing reduces fragmentation, improves data access speed, and optimizes storage.

It ensures SQL Server can efficiently use indexes to speed up query execution and maintain overall database performance.

Reindex Summary Quick Access + Include Exclude

Reindex Rule Configuration

1. Enable/Disable Reindex Rule
   Toggle the reindex rule to activate or deactivate it for the associated servers or databases.
2. Include/Exclude Reindex Rule Tab
   Configure specific databases or database groups to include or exclude from the integrity checks associated with the reindex rule.
3. Configure Rule
   Opens the Reindex Rule Wizard for advanced configuration and fine- tuning of reindexing settings.
4. Reindex Rule Summary s Quick Access Links
   Provides an overview of the reindex rule configuration, along with quick access links to key settings for faster navigation and updates.

Reindex Include Exclude rule tab

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

Reindex rule wizard step 1 – Main Options

Index Maintenance Configuration

1. Fragmentation Level Thresholds
   1. Lower Limit: Set the minimum fragmentation level (in percentage) to trigger index maintenance. Default is 5%, as recommended by Microsoft.
   2. Upper Limit: Set the maximum fragmentation level (in percentage) to determine when a rebuild or reorganization is needed. Default is 30%, based on Microsoft recommendations in Books Online.
   3. Fragmentation levels are determined using avg_fragmentation_in_ percent from sys.dm_db_index_physical_stats.
2. Index Rebuild Options
   1. INDEX_REBUILD_ONLINE: Rebuild the index online to minimize If the MSSQL edition does not support this option, it will be demote to INDEX_REBUILD_OFFLINE.
   2. INDEX_REBUILD_OFFLINE: Rebuild the index offline.
   3. INDEX_REORGANIZE: Reorganize the index to reduce fragmentation.
3. Toggle Options
   1. Partition Level: Maintain partitioned indexes at the partition When enabled, fragmentation levels and page counts are checked for each partition, and appropriate maintenance (reorganize or rebuild) is performed.
   2. Sort in TempDB: Use tempdb for sort operations during index rebuilds.
   3. Resumable: Enable resumable online index operaions.
   4. Pad Index: Apply the fill factor’s specified percentage of free space to intermediate-level index pages.
   5. LOB Compaction: Compact pages containing large object (LOB) columns when reorganizing indexes.
   6. Update Statistics: Automatically update statistics after index rebuild operations.
4. Minimum Number of Pages
   1. Define a minimum index size, in pages, for Indexes smaller than this threshold are skipped. Default: 1000 pages, as recommended by Microsoft.
   2. page_count is checked in dm_db_index_physical_stats.
5. Maximum Number of Pages
   1. Define a maximum index size, in pages, for Indexes larger than this threshold are skipped. Default: No Limit.
   2. page_count is checked in dm_db_index_physical_stats.
6. 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).
7. Statistics Modification Level
   1. Specify the percentage of modified rows required to trigger statistics updates.
   2. Updates are also performed dynamically based on a decreasing threshold: SQRT(number of rows * 1000).
   3. Data Sources:
      – SQL Server 2008 R2 SP2+ and SQL Server 2012 SP1+: modification_ counter and rows from sys.dm_db_stats_properties.
      – Earlier Versions: rowmodctr and rowcnt from sysindexes.
      – Incremental Statistics: modification_counter and rows from dm_ db_incremental_stats_properties.
8. Statistics Sampling
   1. Specify the percentage of the table to sample when updating statistics:
      – 100%: Full scan (equivalent to FULLSCAN option).
      – Unspecified: SQL Server automatically calculates the required sample size.
   2. The StatisticsSample option uses SAMPLE and FULLSCAN options in the SQL Server UPDATE STATISTICS command.

Reindex rule wizard step 2 – Advanced Options

Advanced Index Maintenance Options

1.  Time Limit

1. Set a 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 for index rebuild operations.
2. If unspecified, the global maximum degree of parallelism setting is used.
3. The MaxDOP option in IndexOptimize corresponds to the MAXDOP option in the SQL Server ALTER INDEX command.

4. Fill actor

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

5. Wait at Low Priority Max Duration (Minutes)

1. Specify the time, in minutes, that an online index rebuild operation will wait for low-priority locks.
2. The WaitAtLowPriorityMaxDuration option in IndexOptimize uses 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 aligns with the WAIT_AT_LOW_PRIORITY and ABORT_AFTER_WAIT options in the SQL Server ALTER INDEX command.

7.  Lock Timeout (Seconds)

1. Set the 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:

9. Execution Order

Specify the order in which databases are processed:

Reindex rule wizard step 3 – Schedule

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

Differences between template reindex rule to custom server reindex rule

Custom server – Include Exclude reindex rule tab

Index Rule Configuration Options

1. Close Dialog
   Exit the configuration dialog, discarding any unsaved changes.
2. Enable/Disable Rule
   Toggle the rule to activate or deactivate it for the associated server or databases.
3. Select Predefined Database Grouping
   Choose from a predefined grouping of databases for streamlined configuration.
4. Select Availability Database Groups
   Choose from a list of availability groups on the server to apply the rule.
5. Select Specific Indexes
   Choose from a list of specific indexes to include in the rule configuration.
6. Include/Exclude Databases
   Customize the rule by including or excluding specific databases on the customized server.
7. Final Database List
   A comprehensive list of databases is generated based on the selected groupings, availability groups, and inclusion/exclusion criteria, providing clarity on the databases affected by the rule.