Parallel IDXCOMPACT performance degradation

When IDXCOMPACT is run in parallel against indexes of the same table, it takes longer than separate index compaction 
When IDXCOMPACT is run separately against indexes of the same table it's faster than running IDXCOMPACT concurrently

When multiple index compacts are run simultaneously,  the performance of the compact degrades when running simultaneous compacts against the same table.   

 

In the following example:

• Running index compact separately for each index  table_a.index_1, index_2 and index_3 take 10 mins, == 30 mins.
• Running an idxcompact against all three in a script in the background together takes nearly 60 mins,  twice as long as running separate idxcompact jobs

Table_a
             Index_1 – field-a
             Index_2 – field-a, field-b
             Index_3 – field-a, field-c, field-d

  

While IDXCOMPACT only locks 3 index blocks at a time and there are no record or table locking during the execution, the less performant parallel IDXCOMPACT runs can be explained by:

1. The contention for latches against the same object (“update” TXE locks). An Index compact uses “update” TXE locks which can be improved by tuning the -TXERetryLimit = 200 may help. Refer to Article   How to change the Commit Lock Skip Limit for the TXE in promon? 
2. How much work needs to be done (on each index) == io disk contention
3. The order of blocks read == buffer-pool hits having to re-read from disk (unless the whole table is in shared memory)

Progress Articles:

  Is unexpected BI growth caused by IDXCOMPACT?   
 Why deleting records in a table leads to index fragmentation ?