![sp_iqsysmon syntax correction [CR 474858]](arrow-left.gif){kind=icon}
![Change to sp_iqlocks output [CR 472791]](arrow-right.gif){kind=icon}
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.
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.
dbo.sp_iqindexfragmentation ( ‘target ‘ )
target: table table-name ( index index-name (...) )
DBA authority required. Users without DBA authority must be granted EXECUTE permission in order to run the stored procedure.
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.
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.
GARRAY_FILL_FACTOR_PERCENT option and GARRAY_PAGE_SPLIT_PAD_PERCENT option in the following sections.
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.
0 – 1000
25
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.
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.
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.
0 – 1000
25
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.
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.
