How to Change Fill Factor in SQL Server – Interview Question of the Week #080

It is surprising to see that lots of people do not know that SQL Server Fill Factor 0 (which is the default value when SQL Server is installed) is equal to value as 100.

If you did not know that value 0 of this setting is equal to value 100. I am very confident that your settings may not be optimal as you do know about these settings.

Question: What is the ideal value for the fill factor?

Answer: 

Here is a quick answer for you.

If you have an incremental identity column of your table with a clustered index, it makes sense to keep the fill factor at 0 (zero) or 100. However, for any other indexes and table without incremental identity columns it makes sense to have a lower value of the fill factor. Let us see the official definition.

Fill Factor: It specifies a percentage that indicates how full the Database Engine should make the leaf level of each index page during index creation or alteration.

In simple words, it keeps the empty space on the table to accommodate new rows which are inserted into the table. If there are not empty space in the table, the page split will occur and it may not be optimal for your database performance. There are two different ways to change this value. Let us assume that we want our fill factor as 90.

Method 1: SQL Server Management Studio

Right click on the server property and it will bring up following pop up. You can change the value of fill factor over here and click on OK.

Method 2: T-SQL Script

Here is a T-SQL script:

EXEC sys.sp_configure 'show advanced options', '1'
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure 'fill factor (%)', '90'
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure 'show advanced options', '0'
RECONFIGURE WITH OVERRIDE
GO

If you need further help, you can also watch the following video which will explain how you can change the value.

Reference: Pinal Dave (https://blog.sqlauthority.com)