Index Fill Factor in SQL Server

Today once again I felt to write something about Index, so open up the list of Index articles I have written, find out the topic which are still missing for concept of Index and thought to write about fill factor.

Generally, when you create an Index, your data get stored in B-Tree format where you can find “Root Page”, may be “Intermediate Page” and “LEAF Page”. “Leaf Page” would have your actual data sorted in order of Index key and each “Leaf Page” would be of 8KB.

When you insert/update/delete data to/from table, it tries to 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 your SSN starts with “2” so it will find what is the last page which has SSN number starts with “2”, SQL Server will insert your new row in that page only. If your 8 KB page is full and don’t have room to accommodate new row whose SSN starts with “2”, it will split page and the data in that page will be shared between two pages so now you have two pages which are half full so your row will be accommodated in that page.

If your page would already had space for accommodating new row, you wouldn’t need to wait for extra I/O overhead and wait until page split gets 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. Suppose you give 10 in FillFactor than your data page will consume only 10% of your 8KB page size and when you exceed this limit of 10%, it keeps 90% page empty and create new page for other records.  Now, when you insert new records, you don’t need to worry about I/O overhead of page split as you would have 90% free space and your record will be accommodate in that space easily. So, if you have lower number of Fillfactor, you can decrease I/O over head generated by Page Split which helps you to write your data faster.

Now, you might be thinking now that why shouldn’t I use low number always like 5% or 10% in Fillfactor? Well, it will decrease your page split but it will increase number of data page in your index so every time you scan your index, you have to read more pages and it is again over head while reading the data and decrease the speed of reading data. Suppose you have 10 records in one table and its size is 8KB, you can fit all 10 records in one page only but if you have fill factor of 50 than those 10 records will be stored in two pages rather than one while creating an index.

Uff, what to do now? if we decrease fillfactor figure, it helps in writing but creates problem in reading and if we increase fillfactor figure may be 100% than helps in reading but creates issues while writing in database?

You have to be reasonable and have to take decision based the load on your database, you have to decide first that you have more writes or more read?

Personally I majority go for figure somewhere between 80 to 90 for fillfactor so that you have some space remain for new records and decrease page split at the same time, we don’t keep too much free space in data pages 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 Index or rebuilding an Index or you can set default value for fill factor via following T-SQL (by default it is 0 in SQL Server).

--turning on advanced configuration option

Sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

--setting up fill 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 but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com

Ask me any SQL Server related question at my “ASK Profile”