I have a large table which has a clustered index with an identity primary key. I'm deciding on the correct value for the fill factor for this table to minimise page splits. We maintain indexes using a script run daily which measures fragmentation and takes appropriate action. The table contains variable length columns.
My first thought was to set it at 100 (as records should only be written to then end of the table) but I assume that changes to the variable length columns could also cause page splits, so I'm now veering towards 90.
Any advice appreciated.
Best Answer
It Depends
It's a balancing act. If your table is read intensive, with not many updates or deletes then the default (which is 100) should be ok.
If your table is very write intensive, with lots of updates, then a value below 80 might be more appropriate.
There's no magic formula for this stuff. (AFAIK, if there is please let me know) Best thing to do is have a test environment, have some workload to test. Make the changes & see how your database performs with the workload.