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.
Best Answer
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.)