1

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.

Techleadz Team
  • 198
  • 2
  • 15

1 Answers1

2

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://dba.stackexchange.com/questions/115943/index-fragmentation-am-i-interpreting-the-results-correctly

https://www.brentozar.com/archive/2012/08/sql-server-index-fragmentation/

References :
Notes - SQL Server Index Fragmentation, Types and Solutions

TheGameiswar
  • 27,855
  • 8
  • 56
  • 94
  • I have checked that, yes there is are large number number of insert/update and delete transaction on the table. But I am still unable to find the solution. How I can fix it? @TheGameiswar – Techleadz Team Jul 15 '17 at 15:56
  • if the queries using this table are not within acceptable performance limits,i would try to optimize those queries first and see how they are performing with fragmentation and with out fragmentation,other than this ,only options i could think of are defrag – TheGameiswar Jul 15 '17 at 16:57