I have a quite large table (>10M rows) with frequent crud operations. It has proper indexes, but they get fragmented quickly. Without a periodic index reorganize/rebuild maintenance plan, the index fragmentation can easily go over 90%.
Right now I've addressed this by reorganizing the indexes on a daily basis and a rebuild on a weekly basis. I've also played around with fill factors etc to keep the fragmentation lower.
My main problem is that when the indexes gets too fragmented, SQL Server ignores the indexes and performs full table scans instead. When that happens, it almost kills the application since repeatedly scans of such large table is really heavy. I'm quite sure using a fragmented index would be faster in those cases.
Is there a way to let SQL Server use the index regardless of its fragmentation?