Click here to Skip to main content
15,121,818 members
Articles / Database Development / SQL Server / SQL Server 2014
Tip/Trick
Posted 22 Oct 2014

Stats

35.4K views
9 bookmarked

Fill factor in SQL server.

Rate me:
Please Sign up or sign in to vote.
4.60/5 (6 votes)
22 Oct 2014CPOL5 min read
In this article we will discuss about fill factor and its effect on query performance.

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.  

SQL
​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.

SQL
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.

SQL
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Jitendra Ku. Sahoo
Software Developer
India India
No Biography provided

Comments and Discussions

 
QuestionStatement not executing Pin
Member 113711713-Jul-17 22:09
MemberMember 113711713-Jul-17 22:09 
GeneralMy vote of 1 Pin
Member 1090330723-Oct-14 11:35
MemberMember 1090330723-Oct-14 11:35 
GeneralRe: My vote of 1 Pin
Jitendra Ku. Sahoo23-Oct-14 21:57
MemberJitendra Ku. Sahoo23-Oct-14 21:57 
Please read about Page split.

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.