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.
Table of Contents
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.
- Following pop up window will open, under ‘Fill Specification’ you can see ‘Fill factor’ and can change it. Please save it after change.
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.
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:
- SQL server Column Encryption using symmetric key.
- How to Update using Select statement in SQL Server.
- SQL Server Interview Questions and Answers.
- Import One Database to another in SQL server.
- Generate database scripts in SQL server.
- Different ways to search SQL objects in database.
- SQL CASE statement
- SQL IIF function
- QUOTENAME function in SQL
- How to Take SQL Server Database Offline/Online
- How to use SQL ORDER BY in Your Queries
- SQL Paging using OFFSET and FETCH
- Fill Factor in SQL Server
- How to Search SQL Object in Complete Database
- How to generate SQL table script with data