Maintaining indexes using Sprockit

In OLTP systems, tables may receive small updates more-or-less continuously, so that there may be no “best” time at which to perform index maintenance. Data in tables updated by ETL processes is typically changed much less frequently, often only once a day, but in bulk – so the best time at which to maintain their indexes is immediately after update.

Sprockit's resource dependency model makes it easy to know when this is: when a process finishes running successfully, index maintenance can be performed on its output resources. Sprockit can be configured to perform index maintenance automatically for output resources at a per-resource or system-wide level.

Index maintenance behaviour is controlled by two features: rebuild mode and fragmentation threshold.

Rebuild mode indicates the ALTER INDEX option used to maintain indexes. Available values are:

• 2 – use ALTER INDEX…REBUILD
• 1 – use ALTER INDEX…REORGANIZE
• 0 – don't perform index maintenance.

Default behaviour is specified using the DefaultIndexRebuildMode property. Use sprockit.usp_SetProperty to change this, e.g.:

-- set rebuild mode to REBUILD
EXEC sprockit.usp_SetProperty 'DefaultIndexRebuildMode', 2  

The default behaviour can be overridden for individual output resources by specifying a value in sprockit.Resource's [IndexRebuildMode] field, e.g.:

UPDATE sprockit.Resource
SET IndexRebuildMode = 0  -- don't maintain indexes
WHERE DbName = 'MyDb'
AND SchemaName = 'MySchema'
AND ResourceName = 'MyOutputResource'
AND IsInput = 0

Fragmentation threshold indicates when index maintenance should be performed. Before invoking the maintenance behaviour specified by rebuild mode, Sprockit checks the degree to which an index is fragmented. Maintenance is only performed on indexes with a percentage fragmentation equal to or greater than the fragmentation threshold.

Default behaviour is specified using the DefaultIndexRebuildFragmentationThreshold property. Use sprockit.usp_SetProperty to change this, e.g.:

-- only rebuild indexes with fragmentation >= 30%
EXEC sprockit.usp_SetProperty 'DefaultIndexRebuildFragmentationThreshold', 30  

The default behaviour can be overridden for individual output resources by specifying a value in sprockit.Resource's [IndexRebuildFragmentationThreshold] field, e.g.:

UPDATE sprockit.Resource
SET IndexRebuildFragmentationThreshold = 10  -- rebuild indexes with fragmentation >= 10%
WHERE DbName = 'MyDb'
AND SchemaName = 'MySchema'
AND ResourceName = 'MyOutputResource'
AND IsInput = 0

Sprockit reports index maintenance actions in sprockit.IndexMaintenanceTask. Entries in the table record the actual fragmentation of the index when inspected, along with the rebuild mode and fragmentation threshold configured for the resource at that time. Also recorded is the action taken as a result of inspecting the index, including (where applicable) the TSQL code used to maintain the index.

Sprockit's IndexMaintenanceLogLevel property controls how much index maintenance information is logged. Available values are:

• 0 – don't record anything
• 1 – only record information when an ALTER INDEX command is executed
• 2 – record the results of all index inspections

Use sprockit.usp_SetProperty to change the logging level, e.g.:

-- log everything
EXEC sprockit.usp_SetProperty 'IndexMaintenanceLogLevel', 2  

Log records that are older (in days) than Sprockit's LogRetentionPeriod property are deleted automatically at the next ETL run.