Online Documentation for SQL Manager for SQL server

Specifying actions and setting options


Use this step of the wizard to specify an action for each index and set the respective parameters.

 

The upper area lists all indexes of the tables selected at the previous step.

 

The Fragmentation column indicates the current fragmentation percentage of each index (avg_fragmentation_in_percent);

the Action column allows you to specify one of the available actions to be performed over the selected index: REBUILD, REORGANIZE, DISABLE.

 

Indices Management - Specifying actions and setting options

 

 

The lower area lists the parameters that can be defined for the specified action (except for DISABLE).

  • REBUILD:

PAD_INDEX

This parameter sets the percentage of free space in the intermediate level pages during index creation.

 

FILLFACTOR

This parameter sets the percentage of free space in the leaf level of each index page during index creation.

 

STATISTICS_NORECOMPUTE

This option specifies whether out-of-date index statistics should be automatically recomputed.

 

ONLINE

This option determines whether concurrent user access to the underlying table or clustered index data and any associated non-clustered indexes is allowed during index operations.

 

ALLOW_ROW_LOCKS

This option determines whether row locks are used in accessing index data.

 

ALLOW_PAGE_LOCKS

This option determines whether page locks are used in accessing index data.

 

MAXDOP

This parameter sets the maximum number of processors the query processor can use to execute a single index statement. Fewer processors may be used depending on the current system workload.

 

  • REORGANIZE:

LOB_COMPARISON

Besides reorganizing one or more indexes, large object data types (LOBs) that are contained in the clustered index or underlying table can be compacted when an index is reorganized. The data types image, text, ntext, varchar(max), nvarchar(max), varbinary(max), and xml are large object data types. Compacting this data can cause better disk space use.

 

 

When you are done, click the Next button to proceed to Editing and executing the result script.