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.