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.
When all your pages are full you run into a couple of problems:
- 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.
- 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
- Updating index keys, making entries move in the index causes page splits.
- 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.
- 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)
- 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%.
- 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)