Wednesday, February 22, 2017

How to change the default Fill Factor value of SQL Server instance

Fill Factor is a setting that uses with Indexes for determining how much free space remains on each leaf-level page for future operations (Insert or Update). If you do not specify the value for Fill Factor when creating the index, the default value is set with it, which is 0.

Is it possible to change the default value? Yes, it is possible. The current value set can be seen using sp_configure;

SP_CONFIGURE;

Here is the output of it.


If you create a table like below in tempdb database;

USE tempdb;
GO

CREATE TABLE dbo.Customer
(
 CustomerId int PRIMARY KEY
 , LastName varchar(20) NOT NULL 
  INDEX IX_Customer_LastName NONCLUSTERED
);
GO

and check the Properties of the index created, you will see that Fill Factor of it is 0.


You can change the default value using sp_configure. Once it is change, you need a restart of the service in order to get the value applied.

SP_CONFIGURE 'fill factor (%)', 80;

If you recreate the table again, you will see the Fill Factor as 80% now.


No comments:

Post a Comment