Fill factor in SQL server.

Introduction

In this article we will discuss about fill factor, how to decide best value of fill factor.

Backgroud

Fill Factor:

    A page is the basic unit of data storage in SQL server. Its size is 8KB(8192 bytes). Data is stored in the leaf-level pages of Index.  The percentage of space to be filled with data in a leaf level page is decided by fill factor. The remaining space left is used for future growth of data in the page. Fill factor is a number from 1 to 100. Its default value is 0, which is same as 100. So when we say fill factor is 70 means, 70% of space is filled with data and remaining 30% is vacant for future use. So higher the fill factor, more data is stored in the page. Fill factor setting is applied when we create/rebuild index.

Page Split:

Page split occurs, when there is no more space to accommodate data in leaf-level pages. Page split is movement of data from one page to another page, when data changes in the table.

Fill factor and its effect on performance:

From above, we have gathered some idea about fill factor. Now let see how fill factor can performance. From above discussion we can say if we have higher value of fill factor, we can save more data in a page. By storing more data in less space, we can save memory, resource uses(like IO, CPU).

But downside of it is page split. Page split hamper performance.

Now let us discuss about low value of fill factor. By setting low value of fill factor, page split can be avoided. But it will need more memory(although memory is cheap now), more resource uses. Let say we have a table where the data was fit in 50 pages when the fill factor setting was 100 or 0. Now let say we reduced the fill factor to 50. So it will take 100 pages. When we need to read all the rows, the number of read is doubled now.

How to decide fill factor?

What is correct value of fill factor, we need to set for better performance ? There is no specific answer. It depend upon your application. You are the best person to decide its value. Below is the criteria you need to consider while choosing fill factor.

1:For static/look-up table:                                                                                                                                             This type of tables have static data means data changes very rarely in the table. So we can set high value of fill factor means 100 or 0.

2:For Dynamic table:                                                                                                                                                    In this type of table, data get changes(inserted/updated) frequently. So we need to set low fill factor, between 80 to 90.

3:For Table with Clustered Index on Identity Column:                                                                                              Here the data is inserted at the end of the table always. So we can have higher value of fill factor between 95 to 100.

How to set Fill factor ?                                                                                                                                                  We can set fill factor in 2 ways                                                                                                                                          1) Server level: A generic fill factor setting is applied at the server level for all table/index.                              To see what is the current current default fill factor set at the server level, you can use below script.  

​EXEC [sys].[sp_configure] 'fill factor'
GO

   You can set a server level default fill factor by using sp_configure with a parameter as below script. Here are setting a fill factor value of 90.

EXEC sys.sp_configure 'fill factor', 90
GO
RECONFIGURE WITH OVERRIDE
GO

 For details you can have a look on here .                                                                                                                      

      2) At Index/Table level: While creating/rebuilding index we can set a specific fill factor. We can use below script to set fill factor while rebuilding index.

USE YourDatabaseName                                                                                    GO
ALTER INDEX YourIndexName ON [YourSchemaName].[YourTableName]                                           REBUILD WITH (FILLFACTOR = 80);
GO

Points of Interest

Index play an important role to increase the performance of the query. But it is not only the magic pain killer. As fill factor is related to Index, so it has some role on performance improvement. Before setting a fill factor, analyses your requirement, do experiment with different fill  factor value and finally set correct value.

History

Keep a running update of any changes or improvements you've made here.