Fill Factor in SQL Server

In this article we’ll learn about Fill factor in SQL server”. Fill factor is used to decide how the data will get stored on SQL 8KB pages. We all know that in SQL our data is stored in the form of pages and each page is of 8192 bytes. 8060 bytes are used for data rest of bytes are used by Header and Footer. Now, fill factor decide how much bytes of this page will be used for storing data. You can learn more on SQL page storage HERE.

Fill factor is defined in percentage like if we will set it to 50% then only 50% of page bytes (50% of 8060 bytes) will get used for data storage and rest empty space will stay as it is for future data addition/ updates on the same page.

Page Split and performance

During update when more data is added, and page has no space to add this data at that time page splitting occurred and data is saved on the leaf- level pages. So, if Fill factor is 0 or 100 then page splitting will take place because all page space is already used. On the other hand, if suppose we set fill factor to 50% then number of pages will become double because 50% data space will get occupied and 50% will remain free for future use. In this case page splitting will not happen but 2 X pages will be there which will increase reads operation too.

How to change fill factor value?

We can change fill factor value using command and UI too. Let’s discuss it below:

SQL command to see fill factor value and modify it at DB level

EXEC [sys].[sp_configure] 'fill factor'   -- get Fill factor value.
GO

EXEC sys.sp_configure 'fill factor', 90       -- Set fill factor value to 90  
GO
RECONFIGURE WITH OVERRIDE
GO

We can also set it for specific index like, it is set to 80 for index named ‘PK_Region’ in table ‘Region’

USE NorthWND     
GO
ALTER INDEX PK_Region ON [dbo].[Region]   
REBUILD WITH (FILLFACTOR = 80);
GO  

Change Fill factor using Designer on UI

  • Select table and right click on this and select ‘Design’.
  • Design of table will open, select the row and right click, following list will open.
  • Select ‘Indexes/Keys…’ option.
Fill factor in SQL Server
  • Following pop up window will open, under ‘Fill Specification’ you can see ‘Fill factor’ and can change it. Please save it after change.
Fill factor in SQL Server

Change Fill factor using Index on UI

  • Select the table and expand the Indexes folder.
  • Right click on desired Index and Select ‘Property’
  • A new pop window will open, under ’Select a page’ select ‘options’.
  • Under ‘Storage’ you can see ‘Fill factor’.
  • You can change its value as required and click ‘OK’. That’s all you have to do.
Fill factor in SQL Server

How can we decide fill factor value?

It depends upon our application design and use of DB. If more frequent insert/update, then we can have 70 or 80 % Fill factor so that we can keep some space for future inserts/updates. If less insert/update and more read operations (Select statements) then we must go with 0 or 100 % fill factor value.

I hope you enjoyed reading this article. In this article you learned about Fill factor, its use and how to change it using SQL queries and also from UI. You can learn more SQL concepts on Microsoft Learn.

For more knowledge of MS SQL, you can check our section HERE

You may also like:

 

Leave a Reply

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