sp_iqindexfragmentation procedure

Function

Reports information about the percentage of page space taken up within the B-trees, 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 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. Each index-name is a qualified index name. You can specify multiple indexes within the table, but you must repeat the index keyword with each index specified.

Example

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	GARRAY_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 B-tree 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 B-tree 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.