coolchaitu (8/30/2015)
Dear Jeff sir,I did not rebuild ALL indexes in the database with fillfactor=90. Only on some tables, index fragmentation was greater than 95%. For these indexes, i rebuilt with fillfactor=90 as it did not work previously with fillfactor=0. The next day, when i checked the fragmentation, for 2 tables it increased to 95%. So, for these2 tables, shall i try rebuilding with fillfactor=80 or 70?
It depends. How big are the indexes and how often are they inserted into? And are they clustered or non-clustered? Are the indexes causing any timeouts during the index page splits? Is just one section of the index being inserted to based on the leading column of the index or is it rather a shotgun hit against all the values of the leading column in the index?
If it's a non-clustered index, you could certainly try 80 and then 70 if 80 doesn't work out. I usually wouldn't go below 70 without doing a serious deep dive on the nature of the table. If it's a clustered index, you might want to consider something else for the clustered index.
--Jeff Moden
Change is inevitable... Change for the better is not.