6

I'm part of the database team at my company, and I'm currently facing a dilemma regarding query optimization and performance. Whenever some of my colleagues come across a slow query, their default solution is to create an index to speed it up. This has resulted in some tables having more than 70 indexes!

Personally, I tend to approach this issue differently. Instead of creating new indexes each time, I often find myself modifying existing indexes, such as adding an additional included column etc., to speed up a query. However, not all team members adopt this approach, leading to an increasing number of indexes in our database.

Despite these efforts, I've noticed a slowdown in other DML operations as the number of indexes increases. It seems like a catch-22: without the index, the query is slow, but with the index, everything else slows down a little bit.

I'm looking for advice on how to navigate this situation. How many indexes are generally considered "too many"? Are there any best practices or strategies for balancing the need for fast queries with overall database performance?

Any insights or resources would be greatly appreciated.

Thank you!

Fajela Tajkiya
  • 1,239
  • 9
  • 23

2 Answers2

7

How many indexes are generally considered "too many"?

I typically like to follow a 5x5 guideline. Five indexes per table, with no more than five fields per index. This is a guideline though, not a hard limit. Sometimes I find a use case that warrants seven fields in my index or sometimes a table that needs ten indexes, etc. Regardless, the point of the matter is that anything loosely around that guideline is likely to be reasonable, usually.

Over 70 indexes on a single table, sounds unreasonable, probably by most peoples standards. And if you're finding it's impacting the write speed to that table, then that's probably a direct sign from your system that you have too many. Consider this, are there even 70 columns in the table?...if not, then there's pretty much more indexes than would be necessary to partially cover every possible predicate you could think of.

To me it sounds like there's likely a lot of overlap of the existing indexes and they should be re-evaluated. I wouldn't doubt some can be consolidated together into a single index while others might not even be used so much anymore. A good tool to detect which indexes have low use is sp_BlitzIndex.

Are there any best practices or strategies for balancing the need for fast queries with overall database performance?

  • Being more cognizant with index design to not create redundant or unused indexes would be helpful.

  • Query tuning instead of index tuning as a first go-to would help sometimes too. Not every performance problem can only be solved by an index. Sometimes there's a more efficient way to re-write the query.

  • There's also other tools for materializing data such as Persisted Computed Columns and Indexed Views.

  • Have the application write the data staged in the shape you need it so that you can minimize the complexity of the queries.

  • Re-architect the table structure to be more efficient. For example, perhaps a wide table can be split into multiple smaller tables, especially if the data between those columns don't need to be transactionally consistent with each other. Then the number of indexes on a single table can be reduced.

  • If you do need to add another index to a table, consider if it's possible to make it a Filtered Index, such that it only applies to a subset of the data, making writes to the table quicker.

J.D.
  • 40,776
  • 12
  • 62
  • 141
1

Materialized Views are a good all purpose solution if there are particular slow queries that people want faster. They just calculate the answer when the dependent tables are updated so that when you want the results, they're already ready.

Another specific architecture change would be partitioned tables. An example of a scenario where they're helpful is let's say everyday you get data on 1000 things. You have a table with, let's say, date, thing_id, attr1, attr2, attr3. Since new data comes in daily, when you insert the data in the table they're chunked up physically by day. Now let's say that when you query the data, you usually want to query just 1 of the things. When you do that query, even if it's well indexed, even if the storage is on an SSD*, it will have to get results physically from the drive that it then discards which means it has to read more. Partitioned tables make it so that there are (probably the wrong nomenclature) child tables for each of the partition keys. In that way when you do an insert it puts each new data in its proper partition so that like data is physically near other like data. When you do a query filtering by that partition, it can get that data much quicker than if it weren't partitioned.

*Even though SSDs don't have a physical head that moves around to read data, they still have a minimum chunk size to read.

Dean MacGregor
  • 739
  • 2
  • 8
  • 21