Is it bad to have index space larger than data space?

Question

Often I need to run queries against large tables that don't have the right index. So I ask the DBA to create such index. The first thing he does is look at the table statistics and see the index space size.

Often he would tell me to find an alternative solution because "the index is already larger than the table". He feels the index has to be smaller than the data, because, he told me "have you ever seen the index in a book? It's much smaller than the book itself, and that's how a table index should be".

I don't feel his philosophy is correct, but I can't challenge him because he's a lead DBA and I'm a developer. I feel if a query needs an index, the index should just be created, instead of finding "workarounds" that just make unreadable and unmaintainable SPs.

I'm selecting only the required columns. The problem is I'm filtering by date so the engine will necessarily do a table scan to match the columns. The query runs once a day, at night, to gather statistics, but it takes 15 minutes to run (we have another hard and fast rule: No procedure should take over 3 minutes).

The DBA showed me the index statistics. There were about 10 indexes on that table, of which only 6 were used (stats showed zero hits to 4 of them). This is a large system with over 20 developers participating. The indexes were created for whatever reason, and probably no longer used.

We are required to support SQL Server 2008, since that's what the testing DBs run on. But the clients are all on 2014 and 2016.

Answer 1

Think of index design like a sliding switch. You can move this red triangle switch knob anywhere along the line that you want:

I don't usually measure it in terms of size - I usually think of it in terms of index quantity, but size would be fine as well.

It sounds like your DBA thinks the switch is too far over to the right - that you've added too many indexes, and deletes/updates/inserts are performing too slowly.

Rather than arguing about where the switch is, try asking him about the performance problems you're having due to the high number of indexes. Maybe your users are complaining about delete/update/insert speed, or he's seeing lock waits, or he's having a tough time backing up the database due to its size.

My starting point is usually 5 and 5: around 5 indexes per table, with around 5 or less fields per index. There's nothing magical about that number - it just comes from the fact that I have 5 fingers on each hand, so it's easy to hold my hands up and explain the rule.

You may need to have many LESS indexes than 5 when your workload is heavily biased toward delete/update/insert operations, and you don't have enough hardware horsepower to keep up.

You may be able to have many MORE indexes when your workload is mostly read-only, or when you heavily invest in hardware (like cache the entire database in memory, and have all solid state storage underneath it.)

Answer 2

I like Brents answer and I upvoted it. I would like to add another perspective though. I have worked as a user, a developer and a DBA and feel that opinions are not relevant. I believe it is up to the user (or stakeholder) to decide how a query performs and how long it takes to get results. It is then up to the developer and DBA to work together to make it happen.

If the DBA position at your company is 'in charge' of this topic they can analyze your query and make suggestions on better query design or else answer for the performance.

If the query and / or data structure can not be modified to achieve the goal then I think it comes down to three choices.

1. Slow data retrieval
2. Slow data updating
3. More hardware resources $$$$

Of course every situation has many variables depending on multiple business and technology factors, but I believe the three options apply to most if not all cases.

Answer 3

Also the desire to have more than "The Ozar 5" indexes on a table probably indicates that you have lots of different kinds of read-heavy queries on the table.

Which probably indicates that you could benefit from a clustered or non-clustered columnstore index on the table.

Instead of having the optimal index for each of N different access paths, a columnstore gives you super-fast scanning and the ability to skip unneeded columns, and row segments. So you can have a small number of B-Tree indexes for super-critical transactions, and fall back to the columnstore for everything else.

Columnstore indexes are designed to work in OLTP-heavy workloads with SQL Server 2016+. See the documentation for Real-time operational analytics.

Answer 4

Seems too strict to forbid indexes > table. If your table rarely changes (or changes at night when there isn't much competition for resources) and it is queried a lot in many different ways, many big indexes can be justified. DBAs also should be careful not to stick their noses where it doesn't belong. If he gives you/your system a limit on gigabytes, he shouldn't care too much how that space is used. If he's overworked, this might be why.

However there are many things to consider:

• Lots of indexes makes inserts/updates/deletes slower. So if your table changes a lot, be careful not to make too many of them.
• Space can be a problem too. Not just because gigabytes cost money (not much nowadays), but also time since backup will be slower (depending on how the backup is done).
• Most serious databases can be monitored to find indexes that's rarely or never used. Consider dropping some of them.
• Sometimes you think you need an index, but when you examine your query more closely it can be tuned and rewritten differently with the same result and without the need for the index. Use explain plan to see if the index is used or not.
• Sometimes the last column(s) can be dropped from a multi-column index without much performance hit. And sometimes this can even make queries faster because the index storage space is smaller and more of the index will be held/cached in memory at any given time.
• Function based indexes can replace normal ones to save more space. Example: instead of querying for the full surname, query for the first two letters also (where substr(surname, 1, 2) = substr(<userinput>, 1, 2) and surname=<userinput>) and create index i on customers(substr(surname,1,2)). This might be fast enough and your index will be smaller.
• Databases supports different types of indexes. Some types uses less space than others. Maybe some of your indexes can be converted to a less space consuming type? Be sure to first understand the different index types and what situations they are good and bad for.
• If an infrequent batch job is the only thing that needs a specific index, consider creating that index only for that batch job and drop it afterwards.

Answer 5

Another answer to the question: "Why are your indexes larger than your actual data" is :

my table had a clustered columnstore index that compressed the table. When I added a nonclustered index to this table with most of the columns, the size of noncluster index was 10 times bigger than columnstore index table.