I'm finding issues in one of our databases that houses over 100GB of data where queries are using wrong indexes... where they could use a better index designed specifically for it's query.  

For example: I have a table of leads with field names i.e., Name, Email, Source (And about 50 other fields with lead details).  There are several reports (let's call it a dozen) that utilize a 10+ or so of the fields.  Originally we designed multiple indexes to serve each report individually, but several fields were needed across the indexes... wasting a good chunk of ram. Eventually aggregation was falling behind too because it was automatically picking indexes not best meant for it (i.e. it would pick one index for the date range, but it ended up doing a COLLSCAN for a second part of the query that was in another index.  It could have picked up the correct index that had the combination, but it didn't).  Thus, we removed all multi indexes and created one single index with all the key fields used across the reports.   Memory was reduced quickly and initially the index worked perfectly, but as our data grows we see this index starting to move VERY slowly.  

Is it possible to create an index that is used for hint only, where aggregation and other query/cursors do not pull from?