December 16, 2008 at 2:08 pm
I have a table with multiple indexes, each with a separate fill factor
ranging from 80 to 95%. What is the best practice? Should all indexes on the same table have the same fillfactor?
December 17, 2008 at 12:56 am
It depends on what you are indexing, of course.
A primary key can have 100% since all records are unique.
An IDENTITY can also have 100% since the values are sequential.
An index on LastName column can vary depending on number of inserts and so on.
One way to tell you have the correct fillfactor is to monitor page splits. When page splits occur too often, it can be a sign of having a fillfactor with too high value.
N 56°04'39.16"
E 12°55'05.25"
December 18, 2008 at 8:57 am
Peso (12/17/2008)
It depends on what you are indexing, of course.A primary key can have 100% since all records are unique.
An IDENTITY can also have 100% since the values are sequential.
An index on LastName column can vary depending on number of inserts and so on.
One way to tell you have the correct fillfactor is to monitor page splits. When page splits occur too often, it can be a sign of having a fillfactor with too high value.
This is WAY too simplified. if your table with an identity is often updated with larger data then 100% FF will get you lots of page splits and a fragmented table. Usage patterns as well as access patterns and datatypes all come into play. There is no hard and fast rule. You must know your data and your data access/modification patterns. Take a class, read some books or hire a mentor to evaluate your systems while training you on how to construct and effective indexing scheme (and maintenance plan).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply