4

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!

4
  • 1
    Indexes are only one tool in the toolkit, albeit an important one. Partitions are another tool that can yield massive performance benefits. But before implementing something like partitions (or more indexes) make sure you are reading and understanding your execution plans on problematic queries. It's also helpful to have an automated way to profile all your important queries at once, so that fixing one thing doesn't slow down another (this seems very likely, with that many indexes!)
    – Tim M.
    Commented Oct 6, 2023 at 16:42
  • Do you have any successful experiences with partitioning that significantly boosted performance? Kimberly Tripp stated that 'Partitioning is Not About Performance', suggesting it's more about manageability. However, since I don't have extensive experience with it, I can't elaborate much on this topic. I plan to explore it further later to see if it might be a solution for my issue. sqlskills.com/blogs/kimberly/sqlskills-sql101-partitioning Commented Oct 6, 2023 at 17:08
  • 1
    I have had successful experiences, and even that article states, "Partitioning is not really directly tied to performance but indirectly it can be extremely beneficial." To be clear, I'm talking about table partitioning, e.g. defining a partition for each month, or business unit, or customer, or some other logical divider. Correctly used, you will see in the execution plan which partitions were used. If only a few partitions must be accessed, the number of candidate records decreases, and performance usually follows.
    – Tim M.
    Commented Oct 6, 2023 at 17:17
  • The stupid magic bullet approach is to add non-clustered columnstore indices. This doesn't address OP's question at all. However, one NCCI (which is the max allowed) might be enough to curtail many instances of index creation, assuming the problem is primarily OLAP rather than OLTP. The insert penalty for an NCCI is not very offensive, since NCCIs are optimized in the background.
    – Brian
    Commented Oct 6, 2023 at 18:30

2 Answers 2

6

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.

0

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.

9
  • 1
    Do you have any successful experiences with partitioning that significantly boosted performance? Kimberly Tripp stated that 'Partitioning is Not About Performance', suggesting it's more about manageability. However, since I don't have extensive experience with it, I can't elaborate much on this topic. I plan to explore it further later to see if it might be a solution for my issue. sqlskills.com/blogs/kimberly/sqlskills-sql101-partitioning Commented Oct 6, 2023 at 17:10
  • My experience is with postgres partitioned tables. Maybe there's a difference between sql server and postgres that makes that true but for the reasons I outlined above, it is definitely a performance enhancer under those specific set of insert and subsequent usage patterns. IMO, it doesn't make it more manageable, if anything it's less manageable because the user (at least when I did it in postgres many versions ago) has to create each child table or the insert fails (a trigger can do this though). Commented Oct 6, 2023 at 17:22
  • actually, I do have this old question where I say that partition pruning gave me 5x benefit vs not having it. dba.stackexchange.com/questions/305297/… Commented Oct 6, 2023 at 17:24
  • Here is the postgres page on partitioned tables where it says "Query performance can be improved dramatically in certain situations, particularly when most of the heavily accessed rows of the table are in a single partition or a small number of partitions. Partitioning effectively substitutes for the upper tree levels of indexes, making it more likely that the heavily-used parts of the indexes fit in memory." Commented Oct 6, 2023 at 17:30
  • Thanks Dean for the suggestions and URLs! I'll take a look. Commented Oct 6, 2023 at 17:53

Your Answer

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

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