Reports information about the amount of empty space within the btrees, garrays and bitmaps in IQ indexes.
dbo.sp_iqindexfragmentation ( ‘target ‘ )
target: table table-name ( indexindex-name (...) )
This procedure is owned by dbo. Users without DBA authority need to be granted execute permission in order to run it.
table-name Target table table-name reports on all non-default indexes in the named table.
index-name Target index index-name reports on the named index within the specified table. You may specify multiple indexes within the table, but 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 5 |
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 pages are 81% - 90% full, and 18 pages 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.
