SQL SERVER – Change Default Fill Factor For Index

SQL Server has default value for fill factor is Zero (0). The fill factor is implemented only when the index is created; it is not maintained after the index is created as data is added, deleted, or updated in the table. When creating an index, you can specify a fill factor to leave extra gaps and reserve a percentage of free space on each leaf level page of the index to accommodate future expansion in the storage of the table’s data and reduce the potential for page splits. Let us learn about how to change default fill factor of the index.

I like my fill factor to 90 (Why? I like it!) I use sp_configure to change the default fill factor for the SQL Server.

sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'fill factor', 90
GO
RECONFIGURE
GO

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.

Let me know what you think of this blog post. Here is the relevant video on this subject.

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