Sql-server – SQL Server 2012 Aggressive Over-Indexing when i run sp_BlitzIndex

index-tuningsql server

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

Best Answer

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!

Related Question