SQL Server Monitoring – Index Optimize

Updated August 29, 2021.

Purpose

To provide a wrapper around the SQL Server Index and Statistics Management solution made by Ola Hallengren and deliver reports showing the result of index rebuild, index reorganize and statistics update.

Sources

This feature queries the dbo.CommandLog table where results are stored by the SQL Server Index and Statistics Management solution.

Reports

The report page Index Optimize has 2 line charts, one showing the number of index rebuild, index reorganize and statistics updates, and the other the time in seconds that these operations took.

The x-axis for the charts is at the day level.

The report has 4 buttons to select the level where the tooltips are showing data. For this feature Database, Schema, Object and Index can be selected.

When hovering over the charts tooltips are showing detailed insight by showing the top 5 databases, schemas, objects or indexes (depending on the tooltip button selections) with regards of number of index rebuild, index reorganize and statistics update operations, and the duration for these operations.

Number of operations tooltip

Database
Schema
Object
Index

Duration of operations tooltip

Database
Schema
Object
Index

Model

The model for the report uses the fact [Index optimize] and the dimensions [Date] and [Index].

The fact view [Index optimize] in the database contains all values from the table dbo.CommandLog, but as good practice dictates it, it is only the values used that are loaded into Power BI report.

Configuration

The configuration parameters for the feature are exactly as the parameters you would be using if you executed the SQL Server Index and Statistics Management solution directly.

The default parameters for the 4 tasks are:

1) Index rebuild

@Databases = ‘USER_DATABASES’, @TimeLimit = 1800, @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = ‘INDEX_REBUILD_ONLINE, INDEX_REBUILD_OFFLINE’, @FragmentationLevel2 = 30, @LogToTable = ‘Y’

2) Index reorganize

@Databases = ‘USER_DATABASES’, @TimeLimit = 1800, @FragmentationLow = NULL, @FragmentationMedium = ‘INDEX_REORGANIZE, INDEX_REBUILD_ONLINE, INDEX_REBUILD_OFFLINE’, @FragmentationHigh = NULL, @FragmentationLevel1 = 5, @LogToTable = ‘Y’

3) Update all statistics

@Databases = ‘USER_DATABASES’, @TimeLimit = 1800, @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = ‘ALL’, @LogToTable = ‘Y’

4) Update modified statistics

@Databases = ‘USER_DATABASES’, @TimeLimit = 1800, @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = ‘ALL’, @OnlyModifiedStatistics = ‘Y’, @LogToTable = ‘Y’

Scheduling

The default schedules for the 4 tasks, which are disabled by default, are:

1) Index rebuild

Once every day between 02:00 and 04:00

2) Index reorganize

Once every day between 00:00 and 02:00

3) Update all statistics

Every Sunday between 04:00 and 06:00

4) Update modified statistics

Every day except Sundays between 04:00 and 06:00

Retention

The default retention for the table dbo.CommandLog is 30 days.