0

enter image description hereI observed blocking and time outs in one of our production server and there is prod outage to one of the application continuously,

When i run sp_BlitzIndex i got Aggressive Over-Indexing, ran script in more info script it didn't find any missing indexes,

Aggressive Over-Indexing: Total lock wait time > 5 minutes (row + page) with long average waits

The Create Table script contains below ALTER TABLE script..

ALTER TABLE [schema].[table] ADD CONSTRAINT [Primary_Key_Index] PRIMARY KEY ( [ID] ) WITH (FILLFACTOR=90, ONLINE=?, SORT_IN_TEMPDB=?, DATA_COMPRESSION=?);

In this table Primary Key is non clustered Index and Non_Clustered_Index is primary key

Can anyone suggest how do i clear this over indexing?

Thanks a ton in Advance enter image description here

enter image description here

enter image description here

Improve this question
4
  • You shouldn't be looking for missing indexes, sounds like your problem is too many indexes.
    – Aaron Bertrand
    Oct 8, 2018 at 21:14
  • Timeouts + sp_BlitzIndex output is not sufficient to determine the root cause. Assuming the database is over-indexed, that might have nothing to do with the timeouts.
    – David Browne - Microsoft
    Oct 8, 2018 at 21:16
  • I dropped unused indexes and later i didn't find any unused indexes, how do i find more unused indexes or can suggest any other work around?
    – Sunny
    Oct 8, 2018 at 21:25
  • Brent, Added the result of sp_BlitzIndex for that particular table, please take a look
    – Sunny
    Oct 8, 2018 at 21:42

1 Answer 1

Reset to default
2

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!

Improve this answer
4
  • Brent, Added the result of sp_BlitzIndex for that particular table, please take a look , Thanks
    – Sunny
    Oct 8, 2018 at 21:52
  • Thank you very much Brent, I reduced indexes, now I can see Aggressive Indexes "Indexes: Total lock wait time > 5 minutes (row + page) with long average waits", adding the image of "More Info" script result,
    – Sunny
    Oct 9, 2018 at 13:27
  • @Sunny great, glad I was able to help. However, this is now getting into a repeated back-and-forth - this is where things like consulting make more sense. I can only do so much work here in Q&A. Other folks may be willing to step in if they have free time though. Hope that helps!
    – Brent Ozar
    Oct 9, 2018 at 14:46
  • Worked with dev team and dropped unused indexes, now the database is stable
    – Sunny
    Nov 8, 2018 at 18:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.