Using Ola Hallengren Solution for Index Maintenance

Ola Hallengren's scripts are simple to configure and can be used to optimize SQL Server indexes:

• Index Maintenance: The script performs index defragmentation, index rebuilding and update statistics on all indexes in a database.
• Index Optimization: The script analyzes index fragmentation and recommends indexes to rebuild or reorganize.
• Index Verify: The script performs index verification and reports any corrupted indexes. By using Ola Hallengren's scripts, you can automate and simplify index maintenance tasks, improve database performance, and ensure the integrity of your indexes.

Below is the Configuration of Maintenance Solution:

1. CommandLog.sql: Table to log commands
   • URL to Download: https://ola.hallengren.com/scripts/CommandLog.sql
2. CommandExecute.sql: Stored procedure to execute and log commands
   • URL to Download: https://ola.hallengren.com/scripts/CommandExecute.sql
3. IndexOptimize.sql: Stored procedure to rebuild and reorganize indexes and update statistic
   • URL to Download: https://ola.hallengren.com/scripts/IndexOptimize.sql

Usage for IndexOptimize Proc: Refer https://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html.

One of the Best ways of Index and Statistics Maintenance is having as below:

• Update only Modified Statistics on Daily Basis and Log to Table “CommandLog”.

  EXECUTE master.dbo.IndexOptimize --
                                  @Databases = 'SOADB', --List of Databases can be passed as CSV String
                                  @FragmentationLow = NULL,
                                  @FragmentationMedium = NULL,
                                  @FragmentationHigh = NULL,
                                  @UpdateStatistics = 'ALL',
                                  @OnlyModifiedStatistics = 'Y'
                                  @LogToTable = 'Y'

• Index Re-Build and Re-Organize along on Weekly Basis based on the Fragmentation Percentage.

  EXECUTE dbo.IndexOptimize --
                                 @Databases = 'SOADB',
                                 @FragmentationLow = NULL,
                                 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
                                 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
                                 @FragmentationLevel1 = 5,
                                 @FragmentationLevel2 = 30,
                                 @UpdateStatistics = 'ALL',
                                 @OnlyModifiedStatistics = 'Y',
                                 @LogToTable = 'Y';

We can use master.dbo.CommandLog Table to Analyze the frequency of Indexes and Statistics Updation.