Fill factor and indexes

Question

I have a confusion. If i set fill factor to 50% then sql engine will leave half the space empty for future growth, so data will store in the datepages upto 4KB (approx) because page max size is 8KB. Also fill factor applies only rebuilding indexes. please clear my doubt on the above scenario. Any help would be appreciated.

Thanks

Answer 1

DBAs and developers often read that lowering the fillfactor improves performance by reducing page splits. Perhaps they’re trying to fix a performance problem, or perhaps they’re feeling paranoid. They either lower fillfactor too much on some indexes, or apply a fillfactor change to all indexes.

Here’s the scoop: it’s true that the default fillfactor of 100% isn’t always good. If I fill my pages to the brim, and then go back and need to insert a row onto that page, it won’t fit. To make the data fit and preserve the logical structure of the index, SQL Server will have to do a bunch of complicated things (a “bad” type of page split), including:

1)Add a new page.

2)Move about half the data to the new page.

3)Mark the data that was moved on the old page so it’s not valid anymore.

4)Update page link pointers on existing pages to point to the new page And yep, that’s a lot of work.

It generates log records and causes extra IO. And yes, if you have this happen a lot, you might want to lower the fillfactor in that index a bit to help make it happen less often.

BEST PRACTICES FOR SETTING FILLFACTOR

Here’s some simple advice on how to set fillfactor safely:

1)Don’t set the system wide value for fillfactor. It’s very unlikely that this will help your performance more than it hurts.

2)Get a good index maintenance solution that checks index fragmentation and only acts on indexes that are fairly heavily fragmented. Have the solution log to a table. Look for indexes that are frequently fragmented. Consider lowering the fillfactor gradually on those individual indexes using a planned change to rebuild the index. When you first lower fillfactor, consider just going to 95 and reassessing the index after a week or two of maintenance running again. (Depending on your version and edition of SQL Server, the rebuild may need to be done offline. Reorganize can’t be used to set a new fillfactor.)

This second option may sound nitpicky, but in most environments it only takes a few minutes to figure out where you need to make a change. You can do it once a month. And it’s worth it– because nobody wants their database performance to slow down and realize that they’ve been causing extra IO by leaving many gigabytes of space in memory needlessly empty.