Will the Query Optimizer Ignore A Fragmented Index?
AFAIK the optimizer is not aware of index fragmentation. This can be a problem if it picks a plan that scans a fragmented index.

The optimizer is aware of the allocated data size, though. If the index pages have a lot of free space (possibly due to internal fragmentation) this makes the index less likely to be used. 50% empty space means twice the amount of IO to scan. For random access that should not matter to any significant extent, though.

This is not a huge effect, though. It might explain what you are seeing.

If this small effect flips the query plan to not use the index then the index was never super great in the first place in the eyes of the query optimizer. This might be a hint that you can improve it.

Also, the optimizer seems to have a guess for how much of the index is cached in the buffer pool. There are some references to that in the XML execution plans. I have no detailed knowledge of that.


  I'm beginning to think that adding an index to this table won't help at all


I wouldn't go that far. Maybe all you need is a rebuild or a drop-DML-create sequence in the right places? Or, maybe this is just a query tuning problem (ask a new question with the actual execution plan included).