We have a screen which displays all records relevant to a particulart screen.
We also only show records where the status = 1.
The DB has never been archived so I tried an index on cola, colb,include col c,d,e, where status = 1.
Each query on (Cola and colb) returns about 150 records.
The index total size is 3 Megs (400Megs Data) since there are only 80000 active records from a table of 6.5 million rows.
This does not work for all the queries but makes another index 80% redundant.
The io generated from using this index was much lower than the io generated from the full table index.
I did find that there are still a couple of queries not using this index due to poor query design. (isnull and coalesce on columns)
All in all, this article was a great place to start.
Catch-all queries done right Gail Shaw's Performance Blog