Indexes getting highly fragmented during normal usage of system

Question

I am using MS SQL Server 2014 Standard Edition in my project and recently I have encountered an issue with index fragmentation getting high during normal usage of the system by the users.

Currently indexes are rebuild once per day during maintenance window, but after certain operations some of the indexes can get quite high (above 30%). Other issue is that in case there is big amount of data to process, the fragmentation occurs during single process, so we end up with process that starts rather quickly, but as soon as fragmentation kicks in its performance drops drastically.

What can I do in such situation? It is rather impossible for me to get additional maintenance window(s). I was thinking about rebuilding only some of indexes periodically, but I am afraid that it will end up with locks or even deadlocks on the database.

Answer 1

If you can properly confirm that fragmentation really is a problem, for example because read performance drops measurably when read-ahead becomes less effective, or because the system is struggling to keep up with the number of page splits, you might look at gently adjusting the fill factor on the most critical indexes. This will improve write performance, at a cost to reads and the number of pages that can be cached (in the limited buffer pool available with Standard Edition).

Don't go crazy with low fill factors to start with. Pick a manageable number of the most problematic indexes, make an estimate of the amount of free space needed to avoid too many splits during the normal workload between maintenance windows, and test the effects of setting the lower fill factor (normally no lower than about 90% for the first test). Do not just blindly set a lower fill factor for all indexes.

Bear in mind that nine times out of ten, the system has other, bigger, problems than those directly attributable to sub-optimally set fill factors. You should be monitoring the important performance aspects of the system, and be able to make the case for any changes based on a sound technical analysis of real historical data.

Reading material:

• Specify Fill Factor for an Index in the product documentation
• 5 Things About Fillfactor by Kendra Little
• Does index fill factor affect fragmentation? by Erik Darling
• Stop Worrying About SQL Server Fragmentation by Brent Ozar
• What is the Best Value for Fill Factor in SQL Server by Eric Blinn
• SQL Server Index and Statistics Maintenance by Ola Hallengren

Answer 2

Some options:

Just update statistics

You mentioned that fragmentation was causing performance issues. A lot of times it seems like fragmentation is the issue, when in reality the problem is bad statistics (due to how much data has changed in the table).

You might be better off just updating statistics on the tables involved:

• immediately after these large operations, or
• on a schedule (weekly, daily, or even a couple times a day - depending on how often issues crop up)

Rebuilding indexes causes statistics to be updated, which can reinforce the perception that fragmentation is the problem. These operations are generally far less disruptive and resource intensive than an index rebuild.

UPDATE STATISTICS dbo.YourTableName WITH FULLSCAN;

You could also play around with "sample percent" to avoid scanning the whole table:

UPDATE STATISTICS dbo.YourTableName WITH SAMPLE 50 PERCENT;

Take care when testing different sample rates, as there is a tipping point where sampled stats can take longer than FULLSCAN. This depends on multiple factors (such as your data, and resources available). Erin Stellato has a great post on the subject here: Sample Size and the Duration of UPDATE STATISTICS: Does It Matter?

Avoid common causes of fragmentation

You may be able to change your schema / workload to avoid common causes of fragmentation, and thus slow or stop the problem before it starts. The go-to example would be to avoid having a uniqueidentifier primary key being filled via NEWID().

More generally, a good clustered index should be narrow, unique, static, and ever-increasing.

Rebuild indexes online

If rebuilding indexes during normal user activity is extremely important, you may have to try to justify upgrading to the Enterprise Edition of SQL Server. Then you could specify the index rebuild to occur "ONLINE":

ALTER INDEX [IX_YourIndex] REBUILD WITH (ONLINE = ON);

This can allow user activity to continue during the index operation, other than at the very end (which should be brief). Of course, the rebuild still uses server resources, so it could impact overall performance.

I put this last because it has a big price tag on it. You should definitely explore the other options before going this direction, and test to make sure REBUILDs will actually fix the problem.