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.