Fill Factor

I know default fillfactor is better. But can anyone
suggest me when it is good idea to change this. I have
some big tables. Several Mill rows.I like to fine tune it.
SQL 2000 and 7 both.
Thank you for your kind reply.
Aziz Karim wrote:
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.
Well if you have a table with a clustered index and inserts or updates
to the table will likely cause page splits, then you leave some free
space on the pages by specifying a fill factor when you create the
index. The fill factor only applies when the index is created. It's not
something that is maintained by SQL Server. Having some free space can
help prevent excessive page splitting and reduced performance, but this
will require you maintain the free space on the pages by reindexing on a
regular basis. If the table is not highly transactional or if the
clustered key is unique on something like an identity column, then
specifying a fill factor may not be necessary. In fact, most times it's
not necessary. Adding extra pages to a table can slow multi-read
operations.
David G.
|||Aziz,
This is a fairly vauge question. There are no hard and fast rules. But in
general, you would want a lower fillfactor for tables that are going to get
a lot of inserts in-between existing data. A higher fillfactor will be
useful for tables with less insert activity and/or insert activity at the
end of the cluster (e.g. if you're clustering on an IDENTITY column or other
sequential value).
This article has more information that might help you:
http://www.sql-server-performance.co...ng_indexes.asp
"Aziz Karim" <goAziz@.yahoo.com> wrote in message
news:824701c48531$e2de7ce0$a501280a@.phx.gbl...
> I know default fillfactor is better. But can anyone
> suggest me when it is good idea to change this. I have
> some big tables. Several Mill rows.I like to fine tune it.
> SQL 2000 and 7 both.
> Thank you for your kind reply.