Fill Factor- Part II

Again as another reader requested, I decided to write a fill factor part-2, mainly concentrating on how to set a fill factor. Setting a fill factor is tricky thingy with quite a few factors involved. In this post, let me narrate them and Perhaps come up with a technique to figure out the fill factor percentage to be set.

Setting a fill factor varies from table to table depending upon its usage. Assume that your table has a clustered index on auto identity column then your fill factor can be happily set to 100% as there is almost no chance of having a insert in between as all inserts would happen at the tail of the index. So, it becomes diffcult ( and interesting as well ) only when you have random inserted values on your clustered index key. Let us see how can one go about setting a fill factor in such a case.

The main idea behind setting a fill factor is to keep page splits as minimum as possible. Reducing Page splits can be done by reserving some space for the random inserts that are expected to happen, which is achieved by setting the appropriate fill factor. For setting a fill factor one needs to gather the following details.

1) How frequently one can rebuild the index? (Weekly once/ Daily /). Let me call this frequency to be maintenance window.Maintenance window detail is needed because at every Index rebuild we can get our tables rearranged as per the fill factor.

2) Estimate of how many inserts/updates/deletes is expected with in a maintainence window. Most important is how many random inserts.Other than inserts, updates to varchar,nvarchar columns can cause the pages to split. Its bit difficult at times to find these numbers. But it does matter a lot.

3) Find the Average row size and number of pages in your table( DBCC Showcontig and
sys.dm_db_index_physical_stats can help ) .

After finding the above-mentioned details, one needs to find the Average Random rows to be inserted per page. This can be found by dividing the total number of pages ( showcontig ) by the random inserts estimated. Note that by dividng so we assume that all the random inserts are evenly distributed across the table which in reality may not be the case.So we can do a bit of adjustment to cover the same. Dividing so provides us Random inserts per page.

Total Number of pages/ Estimated random inserts = Random inserts per page.

After that we need to find how much of space needs to be reserved at each page for accomodating random inserts.That would be

Random inserts per page * Avg Row size = Size to be reserved for random inserts.

Fill factor would be

(( 8196 - 96 - Size to be Reserved for random inserts ) / 8196 ) * 100 +/- 5%

96 bytes is the size of page header and hence the adjustment. 5% approximation is meant for the likely skewed distribution across the table for random inserts, varchar updates and deletes if any. Its kind of unusual to have a fill factor below 65%. If your calculations reflect less than 65% then perhaps you may look at shorter maintenance windows(if possible of course) as an option.

After setting the fill factor the responsibility of the DBA is not over. One needs to monitor the Avg page Density and Scan density values ( or Logical fragmentation and Percentage used in my script ) regularly and note the fluctuations and act accordingly.

The story is only half complete. There are more things to be written on the same topic. So will be doing part-3. On Part-3 we would be looking at couple of things.
1) Few more things that are to be monitored after setting the fill factor initially.
2) More often than not one doesnt have a clear idea on number of random inserts expected. In such case what are options we are left with.
So, Watch out for Part-3.