FILLFACTOR 100 on Empty Table

Hi,
I have a nightly process that starts with empty tables to create tables that
will be read-only durring the day. At the end of the process I would like
to have the indexes compacted to FILLFACTOR = 100. My process creates the
tables in one code module and finializes their processing in another code
module. I would prefer to keep the CREATE INDEX statements with the CREATE
TABLE statements (I know that my tables will populate faster without the
nonclustered indexes, but I don't care if it takes 5 minutes instead of 3;
reliable maintenance is a more important factor).
I'm thinking that if a table is empty that it shouldn't matter whether I
create the indexes with a FILLFACTOR = 1 or = 100; I'll just have one page
per index. And, once I start adding data, the FILLFACTOR is not a factor in
index maintenance. (BOL says FILLFACTOR is only for index creation, I'm
assuming that index pages will always be split 50/50 as records are
inserted.)
However, once my processing is done, and if I have used FILLFACTOR = 100,
then I can use DBCC DBREINDEX 'TableName' and all indexes will be compressed
to 100% FILL. This way I don't have to add every index in my early code
module to the final code module, thereby making maintenance more reliable.
So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
factor for insert performance when starting with a completely empty table?
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:uwbh8yucGHA.536@.TK2MSFTNGP02.phx.gbl...
> So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
> factor for insert performance when starting with a completely empty table?
Yes; every observation in your post is correct.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org

FILLFACTOR 100 on Empty Table

Hi,
I have a nightly process that starts with empty tables to create tables that
will be read-only durring the day. At the end of the process I would like
to have the indexes compacted to FILLFACTOR = 100. My process creates the
tables in one code module and finializes their processing in another code
module. I would prefer to keep the CREATE INDEX statements with the CREATE
TABLE statements (I know that my tables will populate faster without the
nonclustered indexes, but I don't care if it takes 5 minutes instead of 3;
reliable maintenance is a more important factor).
I'm thinking that if a table is empty that it shouldn't matter whether I
create the indexes with a FILLFACTOR = 1 or = 100; I'll just have one page
per index. And, once I start adding data, the FILLFACTOR is not a factor in
index maintenance. (BOL says FILLFACTOR is only for index creation, I'm
assuming that index pages will always be split 50/50 as records are
inserted.)
However, once my processing is done, and if I have used FILLFACTOR = 100,
then I can use DBCC DBREINDEX 'TableName' and all indexes will be compressed
to 100% FILL. This way I don't have to add every index in my early code
module to the final code module, thereby making maintenance more reliable.
So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
factor for insert performance when starting with a completely empty table?
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:uwbh8yucGHA.536@.TK2MSFTNGP02.phx.gbl...
> So, to wrap it up, am I correct in my assumption that FILLFACTOR is not a
> factor for insert performance when starting with a completely empty table?
Yes; every observation in your post is correct.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org