Best value for fill factor in index

Question

I use SQL Server 2008 R2 and want to set value for fill factor property in each index. I look for recipe to calculate best value for this property (have better performance when insert record) . Also how I can meter query performance by set deference value to this property.

Thanks a lot in advance.

Answer 1

There is no one answer for all with fill factor. It largely depends on how your data modifications take place database-wide or in particular indexes. The default, out-of-the-box is 0 (100) which leaves virtually no space in a page. What this means is an insert into the middle of a page will cause a page split.

Monitor your Page Splits/sec counter and if you are seeing a high rate, then you may want to reconsider your fill factor. But don't just blindly set that low, as you will be using more disk space with a lower fill factor. And naturally, it will cause higher IO reads, as there is more pages to house the data.

I recommend you look at my answer here regarding fill factor. It's a different type question, but it should give you a better idea about fill factor and how it affects you.

Answer 2

The basic idea is that you want your pages as full as possible. The more rows are packed into a data page, the less pages you need to read and keep in your buffer pool.

So ultimately you want all your pages 100% full with data.

However:

When all your pages are full you run into a couple of problems:

1. Out of index order inserts. Those entry have to be placed in between data. Since the page is full, other records are forced of to a new page.
2. you have a table schema with a large part of the row being variable data type. Updates altering the row size could cause page splits
3. Updating index keys, making entries move in the index causes page splits.
4. Snapshot versioning adds a 14byte tag to the row, this could cause page splits.

There are other causes for page splits but these are the biggest ones.

All of the above will cause fragmentation. Now that's bad, but in a strange way good. Because that is about the only thing that can properly help you decide on the proper fill factor. I'll explain why.

What you do is:

See if any of the above apply. For the indexes that do, do the following.

1. Start with a fill factor of 90% (we have to start somewhere)
   In your index maintenance procedures, build in that you keep track of how fast your indexes build up fragmentation. (say 30% in a week)
2. Now on your next rebuild routine, lower the fill factor slightly. say 80%. See if in the next 7 days, your fragmentation is less then 30%.
3. Continue like this, untill either fragmentation doesn't go down, or the fragmentation has become very low.

Keep in mind, that you should evaluate this over time. Your workload, (data mod) might change over time.

Also keep in mind what your current problems are. Page plits cause extra load on the log file (potentially a lot). Extra writes to the data files (higher checkpoint peaks). And read ahead reads become less effective because of fragmentation. However, low fill factors, eat up buffer space, increase read load.

One thing to be very careful about is that the pagesplits/sec counters Aren't really reliable. They keep track of all page splits. (sql 2012 is better) What I mean by all is that when you insert a record at the very end of an index, effectively you need a new page added to the index to continue. The adding of the new data page is counted as a pagesplit as well. But in reality nothing is split. So in order inserts on a cluster index will still have page splits when you look at the pagesplit/sec counter, but they aren't bad. So only look at this counter in relation with other facts. For example: Unexplainable high trans log load together with high out of order inserts together with high page splits/sec.

One last word of warning. Especially on large tables. Always check what the current page density is of your index and calculate what the size impact would be when you rebuild the index with a different fill factor. (Rebuilding an index with a 100% page density with a new 50% fill factor will effectively double the size of the index)