Saturday, 4 November 2017

Simple Way to Change Index Fill Factor Settings in SQL Server

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