Index Fill Factor in SQL Server

Today once again I felt to write something about Index, soopen up the listof Index articles I have written, find out the topic which are stillmissing for concept of Index and thought to write about fill factor.
Generally, when you create an Index, your data get stored inB-Tree format where you can find “Root Page”, may be “Intermediate Page” and “LEAFPage”. “Leaf Page” would have your actual data sorted in order of Index key andeach “Leaf Page” would be of 8KB.
When you insert/update/delete data to/from table, it triesto insert in the proper data page according to the record you are inserting.For example, you have Index on SSN number you are inserting new row with SSN number,SQL Server tries to insert that record in the appropriate page, suppose yourSSN starts with “2” so it will find what is the last page which has SSN numberstarts with “2”, SQL Server will insert your new row in that page only. If your8 KB page is full and don’t have room to accommodate new row whose SSN startswith “2”, it will split page and the data in that page will be shared betweentwo pages so now you have two pages which are half full so your row will be accommodatedin that page.
If your page would already had space for accommodating newrow, you wouldn’t need to wait for extra I/O overhead and wait until page splitgets finish and space for your row would become available.
This is the time when FillFactor comes into the picture.Fill Factor decides how much your page would be filled up initially. Supposeyou give 10 in FillFactor than your data page will consume only 10% of your 8KBpage size and when you exceed this limit of 10%, it keeps 90% page empty andcreate new page for other records.  Now,when you insert new records, you don’t need to worry about I/O overhead of pagesplit as you would have 90% free space and your record will be accommodate inthat space easily. So, if you have lower number of Fillfactor, you can decreaseI/O over head generated by Page Split which helps you to write your datafaster.
Now, you might be thinking now that why shouldn’t I use lownumber always like 5% or 10% in Fillfactor? Well, it will decrease your pagesplit but it will increase number of data page in your index so every time youscan your index, you have to read more pages and it is again over head whilereading the data and decrease the speed of reading data. Suppose you have 10records in one table and its size is 8KB, you can fit all 10 records in onepage only but if you have fill factor of 50 than those 10 records will bestored in two pages rather than one while creating an index.
Uff, what to do now? if we decrease fillfactor figure, ithelps in writing but creates problem in reading and if we increase fillfactorfigure may be 100% than helps in reading but creates issues while writing indatabase?
You have to be reasonable and have to take decision basedthe load on your database, you have to decide first that you have more writesor more read?
Personally I majority go for figure somewhere between 80 to90 for fillfactor so that you have some space remain for new records anddecrease page split at the same time, we don’t keep too much free space in datapages so number of pages doesn’t get increased. Even, it is not final figure I use for all my databases, if my database is read only or very rare insert/update, I would go even for 100% Fillfactor and if my database is write intensive, I would have even 50% Fillfactor.
BTW, you can define Fill Factor while creating an Indexor rebuildingan Index or you can set default value for fill factor via following T-SQL (bydefault it is 0 in SQL Server).
–turning onadvanced configuration option
Sp_configure ‘show advanced options’,1
GO
RECONFIGURE
GO
–setting upfill factor
sp_configure ‘fill factor’, 90
GO
RECONFIGURE
GO
Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile