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?
Best Answer
Index Hint
You can use an index hint to do that:
The downsides are:
Plan Guides
You can also use a Plan Guide, which sets the plan you want in place.
The downsides are:
Query Store?
Since you're on SQL Server 2017, you may be able to use Query Store to force plans. This is much easier than using a Plan Guide, but you may end up with an unexpected plan.
Downsides:
Alternatives
Without knowing more, I'd probably be more keen on investigating if the index use changes for other reasons, like parameter sniffing.