The fill factor is basically used for a database only if
there is an index created. While checking for any databases, the fill factor is
set to zero by default. Depending the table usage, the fill factor is set. In
simple terms, when there is an insert, update or delete for a table, there
should be some interpretation of data and these data should be stored somewhere
ideally. Now, these data store in the leaf-level page and the amount of data to
be stored on each page is determined by the Fill Factor.
A smallest unit page is 8K. When a page of 8k size is filled
with data of more than 8K, then the data will be stored in a new page, which is
basically called as page split. Let’s check on a simple way to change index
fill factor settings in SQL Server.
Steps to change index fill factor settings in SSMS
1. Open SSMS and right click on the server name to
click the “Properties”.
2. A new window pops with more info about the
server, Click on “Database Settings”
Take a look at the screenshot of the same below:
Here is the T-SQL script for the changes from the screenshot.
EXEC sys.sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
GO
EXEC sys.sp_configure N'fill factor (%)', N'80'
GO
RECONFIGURE WITH
OVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
GO
