I am using SQL Server 2012, from few days I have noticed that fragmentation of some indexes are growing very rapidly. I have read different article and apply the fill factor.
First I have change the fill factor to 95 and rebuild, after one day fragmentation was about 50%. So I decrease the fill factor to 90 and then 80 but after one day fragmentation again reach to 50%.
I need some help to find out the reason for growing fragmentation and solution to fix it.
FYI, I am applying fill factor on index level, only 4-5 indexes are having this issue I have applied fill factor to other indexes as well they are working fine.
Thanks in advance.
There are many things which causes index fragmentation..some of them are below
1.Insert and Update operations causing Page splits
2.Delete operations
3.Initial allocation of pages from mixed extents
4.Large row size
SQL Server only uses fillfactor when you’re creating, rebuilding, or reorganizing an index,so even if you specify a fill factor of 70, you may still get page splits.. and further Index fragmentation is an “expected” and “unavoidable” characteristic of any OLTP environment.
So with your fill factor setting, sql server leaves some space when index is rebuilt and this helps in first scenario only and this is also subjected to your workload
So i recommend not worrying about fragmentation much unless your workload does a lot of range scans..below are some links which helps you
further you can track Pagesplits/deletes which are some of the causes for fragmentation using Perfmon counters/extended events and also using transaction log
https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/
References :
Notes - SQL Server Index Fragmentation, Types and Solutions