What should be the Best Value for Fill Factor in SQL Server

Lot of SQL Server resources ask what is the best value for fill factor in SQL Server and should we change its value from default to some other value? On high level, generally we should not change fill factor value from its default value but you should analyze the history and based on its fragmentation frequency you can take the call. Here, I will show you how to check fill factor in SQL Server and also its best value that you should keep in your database.

A fill factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, If we specify a fill factor value of 95 means that 5% of each leaf-level page will be left empty, providing space for future index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.

Fill factor values 0 and 100 are the same in all respects. The fill-factor option is provided for fine-tuning index data storage and performance. Setting inappropriate fillfactor value will increase the page split occurrence that is not good from SQL Server performance point of view. A correctly chosen value can reduce potential page splits by providing enough space for index expansion.

Although, there is no defined value that we can say is perfect value for fill factor. You can set it to somewhere around 80% and monitor fragmentation over time. Then, you can tweak its value up or down depending on how fragmented the indexes get.

There is a myth that Database engine considers fill factor value during every INSERT or UPDATE transaction. But it’s not true. Database engine does not consider fill factor value during normal DML (insert / update / delete) operations. This value comes into picture only during index creation or rebuild operation. So whenever you insert or load data in your database data pages will be filled 100% irrespective of your fill-factor value.

How to specify a fill factor in an index by using GUI in SSMS

  1. Connect to SQL Server Instance using SSMS.
  2. Expand Database folder followed by the database that contains the table on which you want to specify an index’s fill factor.
  3. Expand the Tables folder.
  4. Click the plus sign to expand the table on which you want to specify an index fill-factor.
  5. Click the plus sign to expand the Indexes
  6. Right-click the index with the fill factor that you want to specify and select Properties.
  7. Under Select a page, select Options.
  8. In the Fill factor row, enter the fill factor that you want.
  9. Click OK.

You can check fill factor value in below screenshot as well. Fill factor value is showing zero because of its default value. Change it for this particular index if you want.

fill factor

How to specify a fill factor in an index using Transact-SQL

We can determine fill factor either during Index REBUILD operation or during Index creation. Below is the T-SQL codes that we can use to specify fill factor for an index.

To specify a fill factor in an existing index

Run below T-SQL command to determine fill factor for an existing index during Rebuild operation.

-- Rebuilds the IX_Salary_EMPID index with a fill-factor of 95 on the dbo.Salary table.
USE Techyaz
GO

ALTER INDEX IX_Salary_EMPID ON dbo.Salary
REBUILD WITH (FILLFACTOR = 95);
GO

To specify a fill-factor during create index

Run below command to specify a fill-factor during index creation.

--Drops and re-creates the IX_Salary_EMPID index on the dbo.Salary table with a fill-factor of 95.
USE Techyaz;
GO

CREATE INDEX IX_Salary_EMPID ON dbo.Salary
(EMPID)
WITH (DROP_EXISTING = ON, FILLFACTOR = 95);
GO

Related Articles

I hope you like this article. Drop your questions and feedbacks in comment section. Please follow our Facebook page and Twitter handle to get latest updates.

Follow me:

Manvendra Deo Singh

I am working as a Technical Architect in one of the top IT consulting firm. I have expertise on all versions of SQL Server since SQL Server 2000. I have lead multiple SQL Server projects like consolidation, upgrades, migrations, HA & DR. I love to share my knowledge. You can contact me on my social accounts for any consulting work.
Manvendra Deo Singh
Follow me:
Summary
What should be the Best Value for Fill Factor in SQL Server
Article Name
What should be the Best Value for Fill Factor in SQL Server
Description
A fill factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth.
Author
Publisher Name
www.techyaz.com

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *