Most tables have one or many indices for speeding up data search. During data processing an index fragmentation occurs and may significantly impact on query performance. A database administrator can use SQL Server maintenance plans and other techniques to maintain performance.

The LUCS database contains a [mgt].[usp_SysMaintenance_Indexes] stored procedure. This stored procedure can be used periodically to rebuild or reorganize (faster process) tables’ indexes based on fragmentation level of an index. If the index has a fragmentation level of more than 30% - (value is adjusted by parameter) -­ it will be rebuilt.

(info) Note that even gathering index statistic may require up to 15 minutes on some systems.

A sample call to show current fragmentation level:

EXEC [mgt].[usp_SysMaintenance_Indexes]
SQL

Stored procedure reports statistic for every table and required action if needed. Statistic is also saved in [mgt].[IndexesStats] table.

Sample call to run index maintenance:

EXEC [mgt].[usp_SysMaintenance_Indexes] @Action = 'REFRESH', @RebuildOnline = 1
SQL


The operation for rebuilding indexes may take several hours, and therefore should be run outside of operation hours!

Among default values stored procedure allows parameters:

@Action 'SELECT' (default) - shows actual information about indexes.

'REFRESH' - performs REBULD or REORGANIZE for indexes.

@Threshold - rebuild index if fragmentation is equal or more than this level in percent.

@RebuildOnline - Rebuild index online (data in table remains available during operation).
SQL
  • By default rebuild operations are done in offline mode (not every version of SQL Server supports online mode). No read or write operation is permitted in OFFLINE mode on the table while the index is being rebuilt.
  • During processing the SQL Server log can grow significantly if e.g. the database is in "Full recovery" mode. The rebuild operation can be logged minimally if the database recovery model is set to either "Bulk-logged" or "Simple".
  • Typically, online index operations will be slower than equivalent offline index operations, regardless of the concurrent update activity level.