One of the questions I often receive is, “How do I change the fill factor in SQL Server?” Fill factor is a value that determines the percentage of space on each leaf-level page to be filled with data. In SQL Server, the smallest unit is a page, which is made up of 8K in size. Each page can store one or more rows based on the size of the row.

The default value of the fill factor is 100, which is the same as value 0. This means that the SQL Server will fill the leaf-level pages of an index with the maximum number of rows it can fit. When the fill factor is set to 100, there will be little to no empty space left on the page.

If you want to change the fill factor, you can do so using T-SQL or SSMS. Here is an example of how to change the fill factor using T-SQL:

EXEC sys.sp_configure N'fill factor (%)', N'80'
GO
RECONFIGURE WITH OVERRIDE
GO

This code will set the fill factor to 80%. You can adjust the value to your desired percentage.

Changing the fill factor can have an impact on the performance of your SQL Server. It can affect the amount of space used by your indexes and the speed at which queries are executed. It is important to carefully consider the fill factor value based on your specific requirements and workload.

If you want to learn more about fill factor and its effects on performance, I have written several blog posts on the subject:

• SQL SERVER – Fillfactor, Index and In-depth Look at Effect on Performance
• SQL SERVER – Set Server Level FILLFACTOR Using T-SQL Script
• SQL SERVER – Correct Value for Fillfactor – Quiz – Puzzle – 24 of 31
• SQL SERVER – Change Default Fill Factor For Index Rebuild Every Index of All Tables of Database – Rebuild Index with FillFactor Comparison
• Index Fragmentation, Index De-Fragmentation, Index Rebuild – SQL SERVER 2000 and SQL SERVER 2005

Feel free to check out these articles for more in-depth information on fill factor and its impact on your SQL Server performance.

If you prefer video tutorials, you can also subscribe to my YouTube Channel for frequent updates on SQL Server topics.