2

Having run sp_BlitzIndex on my database I've found a number of "Aggressive Indexes" (https://www.brentozar.com/blitzindex/sp_blitzindex-aggressive-indexes/). Not only but I am finding that the number of "Aggressive Indexes" is slowly increasing.

I'm a dev who dabbles in the DBA side of things and I can't work out how to go from the information provided by sp_BlitzIndex about aggressive indexes to improving them.

I'm OK at performance tuning an individual query, but I don't know how to find out what query might be happening when the index gets aggressive, and I am assuming the locks are happening because the server is under load - which wouldn't be the case if I tune an individual query.

Any tips on how to investigate further would be appreciated.

9

We've got a ton of learning resources on that Aggressive Indexes page (I just updated it with more, coincidentally) and I can't even begin to do justice to it overall. Having said that, here goes.

Imagine that you've only got a clustered index on a table. Inserts will be pretty doggone fast. However, when you try to update a row in that table, and you don't specify the clustering key in your where clause, then you'll end up with a lot of table locks (aggression). In that case, the fix is to add indexes for the fields you frequently query on.

On the other hand, imagine that you've got dozens of indexes. Whenever you want to insert or delete rows, you're going to need locks all over the place to get your work done. The high number of indexes will slow you down because nobody can get their insert or delete done quickly - and again, you'll see aggressive index warnings. In that case, the fix is to prune down the number of indexes to a more managable number.

Generally, for transactional tables (as opposed to overnight data warehouse loads), I tell people to aim for:

  • 5 or less nonclustered indexes per table,
  • 5 or less fields per index

That rule of 5 and 5 can be violated - the less write activity you have, and the faster your hardware is, and the better you tune your queries, the more you may be able to get away with more indexes. On the flip side, if you have crappy hardware and crappy queries, you might need to drop those numbers lower.

The 5 and 5 rule stems from the fact that I've gotta start people somewhere, and I have 5 fingers on one hand, and 5 fingers on the other - so the rule is easy to communicate.

  • Thanks Brent, so the worst case tables are clearly because I have a few too many indexes... 18 NC indexes :( and every single one of them has been put in to solve a particular query - because its our most used table and is accessed from many different points in the application. I guess this is the subject for another question? – Dale Burrell Sep 7 '17 at 23:00
  • +1 for the reason for 5 rule. @Dale.Burrel probably so as the current question was directed at the concept of "aggressive" indices. But at least now you are thinking in terms of performance, not just fixes. :) – clifton_h Sep 8 '17 at 2:08
  • Would triggers also come into the equation? Or do they take place after the index updates? – Dale Burrell Sep 11 '17 at 22:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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