IN_DB_UTIL_SP_INDEX_MAINTENANCE - Perceptive Content Database IN_DB_UTIL Package 5.0 for SQL Server - Foundation 24.1 - Foundation 24.1 - Ready - Perceptive Content - external

The IN_DB_UTIL_SP_INDEX_MAINTENANCE stored procedure can be used to perform basic index maintenance including reorganizing and rebuilding indexes to reduce fragmentation or to implement various index option changes to specified tables or indexes.

Note that index maintenance should only be conducted after hours or on the weekends when application usage is very minimal.

Note that in many cases database performance can be improved by simply ensuring statistics are updated frequently using large sample sizes, such as FULLSCAN, rather than rebuilding indexes.

Examples

The following example is the default and will reorganize any index that is between 5 and 30 percent fragmented and rebuild any index that is greater than 30 percent fragmented. With MAX_MINUTES set to 0 (no time limit) the procedure will continue running until all qualifying indexes have been reorganized or rebuilt.

EXEC IN_INDEX_MAINTENANCE   
   @MAX_MINUTES       = 0,   
   @TABLE_NAME        = NULL,   
   @INDEX_NAME        = NULL,   
   @FRAGPCT_REORG     = 5,   
   @FRAGPCT_REBUILD   = 30,   
   @SORTINTEMPDB      = N'ON',   
   @FILLFACTOR        = 0,   
   @PADINDEX          = N'OFF',   
   @DATACOMPRESSION   = N'NONE',   
   @NORECOMPUTESTATS  = N'OFF',   
   @MAXDOP            = 0,   
   @MODE              = N'LIMITED',   
   @ONLINE            = N'ON',   
   @ONLINE_WAIT_MINS  = 0,   
   @ONLINE_WAIT_ABORT = N'NONE',   
   @INCLUDE_DISABLED  = N'NO',   
   @CHECK_ONLY        = N'NO',   
   @SHOW_SQL          = N'NO',   
   @DEBUG             = N'NO'; 

The following example is equivalent to the example above.

EXEC IN_INDEX_MAINTENANCE; 

The following will force an index rebuild for all indexes on the IN_DOC table and rebuild the indexes using a fillfactor of 95 percent.

EXEC IN_INDEX_MAINTENANCE   
  @MAX_MINUTES       = 0,   
  @TABLE_NAME        = 'IN_DOC',   
  @FRAGPCT_REORG     = -1,   
  @FRAGPCT_REBUILD   = 0,   
  @FILLFACTOR        = 95; 

The following will force an index rebuild for all indexes on the IN_INSTANCE_PROP table and rebuild the indexes using page level compression.

EXEC IN_INDEX_MAINTENANCE   
  @MAX_MINUTES       = 0,   
  @TABLE_NAME        = 'IN_INSTANCE_PROP',   
  @FRAGPCT_REORG     = -1,   
  @FRAGPCT_REBUILD   = 0,   
  @DATACOMPRESSION   = 'PAGE'; 

The following will build an index fragmentation report and display the generated SQL to reorganize or rebuild the indexes without executing the SQL to reorganize or rebuild any of indexes

EXEC IN_INDEX_MAINTENANCE @CHECK_ONLY = 'YES', @SHOW_SQL='YES'