Understanding sp_iqindexfragmentation results [CR 474601]

A request was made to clarify how users understand the results of the sp_iqindexfragmentation procedure when using the GARRAY_FILL_FACTOR_PERCENT and GARRAY_PAGE_SPLIT_PAD_PERCENT options. This section contains the edits, which will be available in the next GA release of the Sybase IQ documentation.


sp_iqindexfragmentation procedure

Function

Reports information about the percentage of page space taken up within the btrees, garrays, and bitmap structures in Sybase IQ indexes.

For garrays, the fill percentage calculation does not take into account the reserved space within the garray groups, which is controlled by the GARRAY_FILL_FACTOR_PERCENT option.

Syntax

dbo.sp_iqindexfragmentation ( ‘target ‘ )
target: table table-name ( index index-name (...) )

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission in order to run the stored procedure.

Usage

table-name Target table table-name reports on all nondefault indexes in the named table.

index-name Target index index-name reports on the named index within the specified table. You can specify multiple indexes within the table, but you must repeat the index keyword with each index specified.

Example

The following procedure reports the internal index fragmentation for nonunique HG index cidhg in table customers:

dbo.sp_iqindexfragmentation ( ‘index customers.cidhg ‘ )

Index

Index type

btree node pages

Fill factor percent

dba.customers.cidhg

HG

3

75

SQLCODE

0

Fill Percent

btree pages

garray pages

bitmap pages

0 - 10%

0

0

0

11 - 20%

0

0

0

21 - 30%

0

0

0

31-40%

0

0

22

41 - 50%

0

0

0

51 - 60%

0

0

10

61 - 70%

2

0

120

71 - 80%

138

3

64

81 - 90%

24

122

14

91 - 100%

18

1

0

According to this output, of the 182 btree pages in nonunique HG index cidhg, 2 are between 61% and 70% full, 138 are 71% to 80% full, 24 are 81% - 90% full, and 18 are 91% - 100% full. Usage for garray and bitmap pages is reported in the same manner. All percentages are truncated to the nearest percentage point. HG indexes also display the value of option GARRAY_FILL_FACTOR_PERCENT. Those index types that use a btree also display the number of node (nonleaf) pages. These are HG, LF, WD, DATE, and DTTM.

If an error occurred during execution of the stored procedure for this index, the SQLCODE would be nonzero.

See also

GARRAY_FILL_FACTOR_PERCENT option and GARRAY_PAGE_SPLIT_PAD_PERCENT option in the following sections.


GARRAY_FILL_FACTOR_PERCENT option

Function

Specifies the percent of space on each HG garray pages to reserve for future incremental inserts into existing groups. The garray tries to pad out each group to include a pad of empty space set by the value.This space is used for rows added to existing index groups.

Allowed values

0 – 1000

Default

25

Scope

DBA permissions are not required to set this option. Can be set temporary for an individual connection or for the PUBLIC group. Takes effect immediately.

Description

An HG index can reserve some storage on a per-group basis (where group is defined as a group of rows with equivalent values). Reserving space consumes additional disk space but can help the performance of incremental inserts into the HG index.

If you plan to do future incremental inserts into an HG index, and those new rows have values that are already present in the index, a nonzero value for this option might improve incremental insert performance.

If you do not plan to incrementally update the index, you can reduce the values of this option to save disk space.


GARRAY_PAGE_SPLIT_PAD_PERCENT option

Function

Determines per-page fill factor during page splits on the garray and specifies the percent of space on each HG garray page to reserve for future incremental inserts. Splits of a garray page try to leave that percentage empty. This space is used for rows added to new index groups.

Allowed values

0 – 1000

Default

25

Scope

DBA permissions are not required to set this option. Can be set temporary for an individual connection or for the PUBLIC group. Takes effect immediately.

Description

An HG index can reserve storage at the page level that can be allocated to new groups when additional rows are inserted. Reserving space consumes additional disk space but can help the performance of incremental inserts into the HG index.

If future plans include incremental inserts into an HG index, and the new rows do not have values that are already present in the index, a nonzero value for the GARRAY_PAGE_SPLIT_PAD_PERCENT option could improve incremental insert performance.

If you do not plan to incrementally update the index, you can reduce the values of this option to save disk space.