Fill Factor in SQL Server

Do you know what is FillFactor for an index? In this post, we will discuss about Fill Factor in SQL Server. Lets start discussion about FILLFACTOR in SQL Server.

What is FILLFACTOR?

Fill factor in SQL server is used to control the filled space of leaf pages with data. Remaining space is left to accommodate future growth of data inside the page. The default value for fill factor is 0 i.e. packed to 100%. Fill factor has a percentage value which could be anything between 1 and 100 including both. A page in SQL Server is the smallest unit of 8K, which holds the number of rows depending on the size of the row.

Setting Fill Factor

Fill factor can be set either at Server Level or at index level. Just see each in action below;

Setting Fill Factor at Server Level

By default it’s 0 at server level, but SQL Server has exposed it to alter it as per our requirement. We can change it either from Object explorer or using sp_configure.

Using Object explorer

1. Just go to Object Explorer in the left pane.
2. Right click on Server Name and select “Properties” option from the pop up menu.
3. Go to “Database Settings” tab in left and there you can find an option to set the fill factor for this server.

In above figure you can see what we have explained above.

Using sp_configure

First check the current value for fill factor on your server, you can use the below command​

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

To change it use below command

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

In above command we have set Fill factor to 80% on server level. i.e. 80% of the each leaf page will be filled with data and remaining 20% on each leaf page will be used to accommodate any future growth on that page.

Setting Fill Factor at Index level

To set the fill factor at index level, we can use FILLFACTOR option with CREATE INDEX or ALTER INDEX command as below;

CREATE TABLE TEST
(
    ID INT NOT NULL,
    NAME CHAR(10)
)
GO

CREATE CLUSTERED INDEX IX_TEST_NAME ON TEST(NAME)
WITH(FILLFACTOR = 90)
GO

In above example just see this line WITH(FILLFACTOR = 90). This line is setting the fill factor value for this index.

To change the fill factor for already created index, you can use below command;

ALTER INDEX IX_TEST_NAME ON TEST REBUILD
WITH(FILLFACTOR = 90)
GO

Best Value for fill factor

Before deciding the best value for fill factor, just understand the impact of fill factor. Fill factor decides the total number of rows to be accommodated inside a leaf page. More the number of rows accommodated in one page will require less number of pages for the given index and it will require the less number of logical read and I/O on the disk and will increase the performance of the index. But if your system is a transactional system and you have set 100% for fill factor, it will cause too many page split during Insert and Update operations and in turn will decrease the over all performance of the index. Setting a value for fill factor differs as per the type of your system. If your system has too many Insert and Update queries, set it low like something between 75-85 whereas in case your system is a reporting system and does not accommodate any change, you can set it upto 100. Remember that the default value for fill factor is 0 which is equivalent to 100%. what value i have suggested for fill factor may vary in your environment, so before making any change please test it in your environment and then choose a value which fits for you.

Thanks for reading this post and please do share and don’t forget to rate. Please put your comments below the article. Thanks!