This table accounts for 85% of the total DB size (11GB data, 46.8GB index, 63.8 MM rows). No stored procedures are used, all queries are dynamic SQL.
I dropped all of the single column indexes on my test system, and created 6 indexes that covers most of the individual column indexes. I think I will just add indexes for any singular columns that are not currently covering FK's.
I analyzed a week's worth of profiler trace data (ran 2 hours a day, anything > 100ms) and used DTA on any queries that referenced this table and took longer than half a second to run to identify these indexes. Index size dropped 1/3 and performance went from about 8 minutes duration for the workload to 4 minutes.
I am going to use table partitioning to hopefully alleviate a lot of their db maintenance problems this week. They are doing a purge every 30 minutes deleting any data older than 7 days, then they have a DB reindex job running daily and a t-log backup every 15 minutes which is getting some large file sizes due to the purge and reindex.
Here is the index coverage of any columns that have an FK on it:
Rows idx1 idx2 idx3 idx4 idx5 idx6
Agent_ID 155 x x x
f_Success_ID 960000 x x x x x x
MetaTagMap_ID 238000 x
Metric_ID 19000 x x x x x
Plan_ID 68 x
Test_ID 1000 x x x x
Transaction_ID 1500 x x
Rows refers to the number of rows in the FK table.