Working in performance tuning area, one has to know about Index and Index Maintenance. For any Index the most important property is Fill Factor.
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 of 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 in the page, when the fill factor is 100.
I have written following two article about Fill Factor.
[Articles are relocated so links are disabled]
What is Fill factor? – Index, Fill Factor and Performance – Part 1
What is the best value for the Fill Factor? – Index, Fill Factor and Performance – Part 2
I strongly encourage read them and provide your feedback.
Here is a quick video about how to change fill factor:
Reference: Pinal Dave (https://blog.sqlauthority.com)
10 Comments. Leave new
Is Pad index is same or it deals with intermediate levels while Fill factor with leaf level?
Hey Pinal,
Just an FYI, the two links to Sqlmag.com website is not valid now. It gives an error.
Thanks,
I’m sorry for my previous comment.
There are 2 links in this page in first paragraph. those links are not working.
hi Pinal, 1st thanks for all your great posts!
I have a funny situation on a table and will appreciate any suggestions:
I have a very simple link table in my DB, 3 columns: 1st primary key clustered index bigint, 2nd and 3rd are foreign keys (bigint).
The table is read 99.99% of the time with very few inserts so I use a fill factor of 100%.
It is used in a lot of join statements.
Only 2 indexes on the table.
Problem is the fragmentation gets very high, very quickly.
I have set the fill factor from 100% to 50% but the indexes still get fragmented in less than 24 hours on the SQL 2008 DB.
There is a sql agent script defragmenting/reindexing the indexes every 24 hours too.
There is also no disk fragmentation. Similar tables on my DB have 0% fragmentation.
Hi Sir,
I have a query that I was not able to find answer to so came to seek help, can we connect two diffrenct database say MySql 4.1 and SqlServer 2005, as well is there any possible way for the data migration between the two.
Use import/export wizard to copy the data
ordinary index” and a fat index?
Above mentioned links are not working.
Why page is 8kb any reason behind?
legacy.