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?

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

Answer: 

Solarwinds

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.

How to Change Fill Factor in SQL Server - Interview Question of the Week #080 fillfactor-800x633

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)

Solarwinds
, , ,
Previous Post
How to Send Execution Plan in Email? – Interview Question of the Week #079
Next Post
How to Find Outdated Statistics? – Interview Question of the Week #081

Related Posts

3 Comments. Leave new

  • John Mitchell
    July 25, 2016 4:06 pm

    I think your advice on 0 fill factor isn’t general enough. You want to keep the fill factor at 0 for any index whose leading column is monotonically increasing (or decreasing) and isn’t likely to be updated. That could indeed be an identity column, but it could also be a “time added” column or a column that takes its value from a sequence object. This applies to non-clustered indexes as well as clustered.

    John

    Reply
  • Ed Eaglehouse
    July 25, 2016 5:38 pm

    Wow, I had no idea this was broken. A fill factor of 0 should mean 0, especially for data warehousing applications where you know the index pages won’t expand. Leave it to Microsoft to counter-intuitively redefine such a fundamental quantity. Thanks for the warning.

    Reply
  • Same T-SQL Script will work for azure cloud database?

    Reply

Leave a Reply

Menu