This blog is part of the blog series named SQL Server Monitoring.
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.
Report
The report has in the upper part 2 line charts, where the left are showing the number of index rebuild, index reorganize and statistics updates, and the right are showing the time in seconds that these operations took, where the lower part shows the information per database and with the possibility to drill down to schema, object and index 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.
Model
The model for the report uses the facts [Index optimize] and [OH errors], and the dimensions [Date] and [Index].
It is the same model that is used by the Ola Hallengren errors 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:
| Name | State | Parameter | Schedule |
|---|---|---|---|
| Index rebuild | Disabled | @Databases = 'USER_DATABASES', @TimeLimit = 1800, @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = 'INDEX_REBUILD_ONLINE, INDEX_REBUILD_OFFLINE', @FragmentationLevel2 = 30, @LogToTable = 'Y' | Once every day between 02:00 and 04:00 |
| Index organize | Disabled | @Databases = 'USER_DATABASES', @TimeLimit = 1800, @FragmentationLow = NULL, @FragmentationMedium = 'INDEX_REORGANIZE, INDEX_REBUILD_ONLINE, INDEX_REBUILD_OFFLINE', @FragmentationHigh = NULL, @FragmentationLevel1 = 5, @LogToTable = 'Y' | Once every day between 00:00 and 02:00 |
| Update all statistics | Disabled | @Databases = 'USER_DATABASES', @TimeLimit = 1800, @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = 'ALL', @LogToTable = 'Y' | Every Sunday between 04:00 and 06:00 |
| Update modified statistics | Disabled | @Databases = 'USER_DATABASES', @TimeLimit = 1800, @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = 'ALL', @OnlyModifiedStatistics = 'Y', @LogToTable = 'Y' | Every day except Sundays between 04:00 and 06:00 |
Retention
The default retention for the table dbo.CommandLog is 30 days.
