SQL Server Monitoring – Index optimize

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.

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.

Sources

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

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.

Service setup

The feature uses the service ‘fhsmSPIndexOptimize’ which can be configured using the stored procedure fhsmSPControl.

Parameter

The 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:

EXEC fhsmSPControl
	@Type = 'Parameter',
	@Command = 'list',
	@Task = 'fhsmSPIndexOptimize',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Parameter',
	@Command = 'set',
	@Task = 'fhsmSPIndexOptimize',
	@Name = 'Index rebuild',
	@Parameter = '@Databases = ''USER_DATABASES, msdb'', @TimeLimit = 1800, @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = ''INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'', @FragmentationLevel2 = 30, @LogToTable = ''Y''';

EXEC fhsmSPControl
	@Type = 'Parameter',
	@Command = 'set',
	@Task = 'fhsmSPIndexOptimize',
	@Name = 'Index reorganize',
	@Parameter = '@Databases = ''USER_DATABASES, msdb'', @TimeLimit = 1800, @FragmentationLow = NULL, @FragmentationMedium = ''INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE'', @FragmentationHigh = NULL, @FragmentationLevel1 = 5, @LogToTable = ''Y''';

EXEC fhsmSPControl
	@Type = 'Parameter',
	@Command = 'set',
	@Task = 'fhsmSPIndexOptimize',
	@Name = 'Update all statistics',
	@Parameter = '@Databases = ''USER_DATABASES, msdb'', @TimeLimit = 1800, @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = ''ALL'', @LogToTable = ''Y''';

EXEC fhsmSPControl
	@Type = 'Parameter',
	@Command = 'set',
	@Task = 'fhsmSPIndexOptimize',
	@Name = 'Update modified statistics',
	@Parameter = '@Databases = ''USER_DATABASES, msdb'', @TimeLimit = 1800, @FragmentationLow = NULL, @FragmentationMedium = NULL, @FragmentationHigh = NULL, @UpdateStatistics = ''ALL'', @OnlyModifiedStatistics = ''Y'', @LogToTable = ''Y''';

Schedule

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'list',
	@Task = 'fhsmSPIndexOptimize',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'set',
	@Task = 'fhsmSPIndexOptimize',
	@Name = 'Index rebuild',
	@ScheduleType = 0,
	@Enabled = 1,
	@ExecutionDelaySec = 43200,
	@FromTime = '02:00:00',
	@ToTime = '04:00:00',
	@Monday = 1,
	@Tuesday = 1,
	@Wednesday = 1,
	@Thursday = 1,
	@Friday = 1,
	@Saturday = 1,
	@Sunday = 1;

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'set',
	@Task = 'fhsmSPIndexOptimize',
	@Name = 'Index reorganize',
	@ScheduleType = 0,
	@Enabled = 1,
	@ExecutionDelaySec = 43200,
	@FromTime = '00:00:00',
	@ToTime = '02:00:00',
	@Monday = 1,
	@Tuesday = 1,
	@Wednesday = 1,
	@Thursday = 1,
	@Friday = 1,
	@Saturday = 1,
	@Sunday = 1;

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'set',
	@Task = 'fhsmSPIndexOptimize',
	@Name = 'Update all statistics',
	@ScheduleType = 0,
	@Enabled = 1,
	@ExecutionDelaySec = 43200,
	@FromTime = '04:00:00',
	@ToTime = '06:00:00',
	@Monday = 0,
	@Tuesday = 0,
	@Wednesday = 0,
	@Thursday = 0,
	@Friday = 0,
	@Saturday = 0,
	@Sunday = 1;

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'set',
	@Task = 'fhsmSPIndexOptimize',
	@Name = 'Update modified statistics',
	@ScheduleType = 0,
	@Enabled = 1,
	@ExecutionDelaySec = 43200,
	@FromTime = '04:00:00',
	@ToTime = '06:00:00',
	@Monday = 1,
	@Tuesday = 1,
	@Wednesday = 1,
	@Thursday = 1,
	@Friday = 1,
	@Saturday = 1,
	@Sunday = 0;

Retention

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

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'list',
	@TableName = 'CommandLog',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'set',
	@TableName = 'CommandLog',
	@Enabled = 1,
	@Sequence = 1,
	@Days = 30;