Why does idxfix ALL option 3 online seem to grind to a halt?

This Article discusses running IDXFIX online as follows:

 

Select Option 3:  

to 1 (Scan records for missing index entries) and 2 (Scan indexes for invalid index entries) above.

 

When these IDXFIX Options are selected, the entire database is scanned once (as typically done by index rebuild) as well as each index being traversed and the record associated with each index entry being retrieved.

 

There are m a n y more reads going on in this scenario than for example, in an index rebuild (IDXBUILD), as the same record is retrieved once for each index on the table.  In addition, because the utility can be run online, these reads are performed with share-lock, so there is probably also a great deal of lock queueing / waiting going on.

 

There is no sorting used during IDXFIX, therefore adding the speed sort parameters -TB and -TM will not speed the process up.  It is merely a process that reads records in tables, to check that there are indexes for the records and a read of indexes to check that there are associated fields in records. Note that records will not be fixed during an idxfix operation, merely reported when they cannot be read.

 

Similarly, if Private Buffers (-Bp) are used when running IDXFIX online, other online users may benefit from it but not idxfix.  Running with -Bp is similar in terms of "nice'ing" a process on UNIX.  It causes more accurate utilisation of the database buffer pool LRU algorithm.  This in turn could improve performance for other user processes accessing data out of the buffer pool by increasing their buffer hit ratio, if they are also accessing the "interesting" blocks in the buffer pool.

 

When IDXFIX is used online to build indexes, then the time to activate the index is usually very small (related to how many additional index changes may have occurred after the first idxfix started).  The time to build the index entries however, for example with word indexes, is directly related to how complex the index is and how many records are involved. It may take a long time if there are a lot of records.

 

Disabled indexes will not be enabled when idxfix is run online, but the utility will check them.  It will build all indexes but not activate disabled indexes.  Completing index activation will require an idxfix offline (or idxbuild) of the same index, which will activate the index if no corruption is found.

 

The performance of IDXFIX online is further affected by other online users or transactions. The process of an online idxfix operation runs SHARE-LOCK, it will constantly wait, grab, and release locks. If there are other transactions requiring same locks, idxfix would have to compete against these and this would cause a performance hit.

 

The above taken into account, it is worth investigating the following:

 

1.) What does promon show for status of the index fix connection?

2.) What is the CPU utilitisation show?  Is there any activity? Has the operation hung or just processing a lot of data?

3.) If the idxfix operation has hung, could it be waiting on a lock that has yet to be granted?

 

To ensure the data integrity ending the process will not mean that idxfix will be ended immediately. It will backout the last idxfix transaction which, should be small. If CTRL+C is issued when idxfix is waiting on a lock for example, the process will not end before idxfix gets that lock. Idxfix would then "seem to grind to a halt".

In Summary: When running IDXFIX online
First run without locking to report corruption: IDXFIX -NL -Bp 32 -cpinternal [db codepage]
Then verify specifically on errors from no-lock: IDXFIX -lkwtmo 10 -Bp 32 -cpinternal [db codepage]

Other Considerations:

1. Run an IDXCOMPACT before running IDXFIX when index fragmentation is high this will improve the runtime execution.
2. Exclude WORD indexes where possible as these will always take a lot longer.
3. Refine the IDXFIX choice to 'some/by table/by area' as opposed to 'all' to split the operation up and then run idxfix concurrently
4. An enhancement request has been submitted as an Idea on the Progress Community. Weigh-in with your suggestions and promote the Idea to assist with prioritisation during review by selecting the link provided in the following Article: "IDXFIX All" with option 2 or 3 is slower when compare to "IDXFIX by table" with option 2 or 3

Run idxcheck on a restored copy of the production database as a 'workaround' once a known sub-set is identified or when it  is necessary to investigate all indexes. Then then feed the results of idxcheck (iow: a subset where index corruption is reported) into an online idxfix, to 'cut down' the workload of the online idxfix operation.
 

Although idxcheck is much slower because of the nature of the checking it does, it has been known to be more thorough in reporting index corruption because it was designed to detect index corruption as sensitively as possible at both record and index block level.