Fill Factor

This post assumes that you know about indexes, clustered index, non clustered indexes
but not about fillfactors :)

If you dont have background info on indexes then run thro these links

http://msdn.microsoft.com/en-us/library/aa933129(SQL.80).aspx

http://msdn.microsoft.com/en-us/library/ms177443(SQL.90).aspx

http://msdn.microsoft.com/en-us/library/ms177484(SQL.90).aspx

If you already know about indexes and also fill factors then dont waste your time and run away from this post :)

ok..so lets get started with fill factors..

When designing indexes one would have noticed a small option to set fill factor percentage. So what exactly is fill factor? Fill factor specifies the percentage up to which leaf pages( clustered indexed table's data pages and non clustered index's leaf pages ) should be filled with. To add more details, when a table is created certain number of pages are allotted to the table. As the rows in the table increases table grows into more number of pages. If the table has a clustered index and if fill factor is set then data pages would be filled up to percentage specified by fill factor.

A sample data page when fill factor is set to 80%. Values mentioned (100,101,115..) are the values of the clustered indexed column.



One needs to reserve space on each data page using fill factor to support the extra space required when random inserts happen on a clustered indexed table. For example if an insert with value 117 (on clustered indexed column) for above-mentioned data page occurs then the other rows (from 120 to 239) need to move to the next row. Another insert say with a value118 would cause another shift. When the data page becomes full SQL Server triggers a 50-50 split. 50-50 split would mean that a new page will be added to the table and the current page and new page would share rows evenly. The problems with 50:50 split are as follows.

• Half the page will be empty which means more pages needs to be read when rows are to be selected. This results in high IOs
  
• 50-50 split takes extra time, which would result in slower inserts.
  

When the tables have a number of pages which are 50% full then the it would longer for the engine retrieve the rows in the table as it has to scan more pages than when the pages are completely full (or close to full). So Ideally, we would like to keep 50:50 splits as low as possible. So keeping the fill factor to 100% is not a great option if table can expect a number of random inserts (Ex: if your clustered index is on email id column many random inserts are definitely possible). Also, having a low fill factor say 50% would result in major portion of the page remain unused for most of the time. Imagine the case like a table of size 800 KB should fit in 100 pages ( aprroximately) but occupies 1200 pages, would result in query consuming 50% more than it was actually supposed to.So, Picking a wrong fill factor can lead to heavily fragmented data pages, which can cause a serious performance dip on all queries fired to the table. So a fill factor should be picked with the nature of inserts (and updates/deletes) in mind.

After many cycles of random inserts and page splits, it is common for tables to have irregular free space on pages. This type of irregularity is called an internal fragmentation, which can be fixed using DBCC ReIndex/Index frag command. Many DBA’s make Reindex command as a part of their maintenance script for the above-mentioned reason.

In SQL 2000 Internal Fragmentation can be checked using DBCC showcontig command. The Average Page density on the resultset of Showcontig displays the same. For more details on checking fragmentation refer to my other post.