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.