asked
How to determine what Fill Factor to use
Hi guys How do you determine what Fill Factor to use when creating indexes? I am currently rebuilding indexes on large tables which have become heavily fragmented but don't know what Fill Factor to specify. Most people say 70 or 80 should be fine, but how do you determine that? Thanks Ruan
If your index key is auto increment like Identity column you can set it as 100 because they are never been updated and they are always sequential Inserts. Setting fill factor depends on size of row, how many rows does it fit on a page,whether its read only, Frequency of writes or whether they are sequential or not. Choosing a less value can cause read performance problems of database. There is no accurate number for this but anything between 75-85 is good.
The biggest determining factor for setting the size is how often you expect to see page splits based on your data and it's distribution. For data that is effectively random, where you may see inserts or updates anywhere at any time, you may want to look at a lower fill factor, say 50%. For average distribution data with an average number of page splits, 75% is a generally good number. For data that isn't going to suffer from page splits you should go for 100%. And as @cyborg says, read only data should also go for 100%. You measure page splits by looking at the Performance Monitor counter. If you're in 2012 you can use an extended event to get better info. There's not a single bad number to watch for, you have to measure it over time and compare it to a baseline. Bradley Ball has an [excellent article][1] on tracking page splits. [1]:
http://www.sqlservercentral.com/blogs/sqlballs/2012/08/06/how-to-find-bad-page-splits/
16 People are following this question.
