about fill factors
-
hi!
i need some information about fill factor.
i know, if i use a column read only, the fill factor must be 100%. but how can i calculate the perfect % for a column which i read and write to?
i wolud like to reach that inserts and selects won't be slow.
for sharing your knowledge,Gloria
-
Gloria - you cannot really calculate a perfect % fillfactor. The only real way to do it is by trial and error. The exact value depends on which columns you modify, how frequently you change the data, how frequently you are able to rebuild the indexes, etc.
Firstly, fillfactor applies only to indexes, both clustered and non-clustered, and not individual columns.
The main aim of fillfactor is to prevent page splits which can lead to extent fragmentation. If you use a fillfactor of 100%, then you will get extent fragmentation quickly if you start inserting data. If you can rebuild your indexes frequently (and so restore the fillfactor free space), then you might only need to reduce your fillfactor a little. If you can only rebuild your indexes at weekends, then you might need to use a higher fillfactor to last the week.
If you need to insert data into a table, pick a "guess" fillfactor value first of all. Try 90% - why not? Check the fragmentation over the next few days using the DBCC SHOWCONTIG command. The value you are checking is the Scan Density value - if this value falls rapidly, then you are seeing severe fragmentation, and you need to LOWER your fillfactor, or rebuild your indexes more frequently to re-distribute the data.
If you cannot rebuild the indexes more frequently, rebuild your indexes using 80% fillfactor, and repeat the tests above. If the scan density only falls slightly, or does not fall at all, then it sounds as if 80% is a good figure. especially if you
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply