patternsqlMinor
Indexes getting highly fragmented during normal usage of system
Viewed 0 times
duringsystemhighlygettingindexesnormalfragmentedusage
Problem
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.
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.
Solution
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:
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
Context
StackExchange Database Administrators Q#241387, answer score: 6
Revisions (0)
No revisions yet.