“I want to change fill factor to another value – how do I do that?”
The other day I received this question in via Facebook. Fill factor is the value that determines the percentage of space on each leaf-level page to be filled with data. In an SQL Server, the smallest unit is a page, which is made by Page with size 8K. Every 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 same as value 0. The default Fill Factor (100 or 0) will allow the SQL Server to fill the leaf-level pages of an index with the maximum numbers of the rows it can fit. There will be no or very little empty space left on the page, when the fill factor is 100.
I decided to build a quick video which explains how to change fill factor with the help of T-SQL as well as SSMS.
[youtube=http://www.youtube.com/watch?v=2ER5BgCAn7g]
The code which I have used in this code is over here:
EXEC sys.sp_configure N'fill factor (%)', N'80' GO RECONFIGURE WITH OVERRIDE GO
Action Item
Here are the blog posts I have previously written on the subject of Fillfactor. You can read it over here:
- 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
You can subscribe to my YouTube Channel for frequent updates.
Reference: Pinal Dave (https://blog.sqlauthority.com)
