In my post How to Fix sp_BlitzIndex Aggressive Indexes Warnings, I note that:
Aggressive Over-Indexing: when there’s a lot of blocking on a table with 10+ indexes. Your DUI queries are probably getting held up by acquiring locks across many of these indexes to update ’em.
Over-indexing means your table likely has too many indexes. You probably don't want to add more - you probably want to dig more deeply into the indexes that already exist on the table, and start getting rid of the unused and duplicate indexes.
If you'd like, you can post the output of sp_BlitzIndex for your particular table. Scroll across to the right of your sp_BlitzIndex output, and you can see a More Info query. Run that, and sp_BlitzIndex will list out your existing nonclustered indexes. That's what we'd need to help you reduce the indexing overhead.
Update 2018-10-09: thanks for adding the sp_BlitzIndex output. Based on that, I can see that your clustered index is on 3 separate fields, and it's not a unique clustered index either. Generally speaking, this isn't a great clustered index design because the 3 clustering keys (TREE_ID, LEFT_VISIT, and RIGHT_something) are included in every single nonclustered index as well. (That's what the Secret Columns show.) That makes your nonclustered indexes larger than they need to be.
In terms of the rest of the indexes, it's time to start making some tough decisions: do you really need 4 copied subsets of the table (indexes) that all start with PRJ_ID? Do you really need a 6-key-field index that starts with TREE_ID, LEFT_VISIT (the same two fields that your clustered index starts on)? Those are the questions I'd start asking about the indexes to get down to a lower number.
If you can't reduce the number of indexes, then your options are:
- Tune the locking/blocking queries so they run faster (like shorten their transactions), or
- Throw hardware at it - if the blocking queries are held up by slow server power, then you can get them to finish faster this way, but it's not a great solution
Hope that helps!