Force SQL Server to use fragmented indexes?

Question

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?

Answer 1

Index Hint

You can use an index hint to do that:

SELECT *
FROM dbo.Users AS u WITH (INDEX = ix_definitely_an_index)
WHERE u.Reputation = 2;

The downsides are:

• Potentially changing a lot of code
• If you rename an index, this breaks
• If you change an index definition, it might not be the best index to use anymore

Plan Guides

You can also use a Plan Guide, which sets the plan you want in place.

The downsides are:

• It's fickle; if your query isn't exactly the same, your plan might not get used
• It's tough to set up; you have to get everything exactly right
• Also the same stuff as an index hint, so uh... That's rough.

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:

• Same stuff as above
• Having to enable Query Store, if you're not already using it
• Having to query Query Store to see if forced plans are failing

Alternatives

Without knowing more, I'd probably be more keen on investigating if the index use changes for other reasons, like parameter sniffing.

Answer 2

Next time SQL Server ignores the index try to update statistics with FULL SCAN on that index or on the whole table. If that works then fragmentation is not a problem.

Also SQL Server doesn't care about fragmentation when choosing a plan, but it may ignore the index if it becomes too big because of the "internal fragmentaton". Please check the empty space inside the pages of your index.