Kevin,

Fill factor is, IMO, related to updates and future inserts. If you have a lot of updates to those null columns, and you can do update in place, then you will probably want to leave room on the data pages for the records to grow. Similarly, if there is a clustered index and you will be doing inserts throughout the table, then you will probably want to set the fill factor to leave room on the data pages.

One possibility for speeding it up is to not create indexes until after the data is loaded. If you follow this strategy, create the clustered index first and then the other indexes. If possible, create the other indexes in parallel. You can use global temporary tables to track the status of the creation of the indexes - much as you would if you had separate processes and used shared memory to synchronize them - but if you do, you will be getting complex enough that others may have difficulty following the logic.

My suggestion would be that if you have the option, do not have any triggers or indexes on the created table, bulk insert into it to avoid the overhead of a transaction log, create the indexes and RI constraints and triggers after the data is loaded. Bulk insert is faster than bcp which is faster than insert. Data set operations are faster than cursor operations except in some less common cases where you need to do special calculations on the data.

If you can give more details, it would be easier to make concrete suggestions.

. . . Tom

Tom Zeblisky
Reuters

Kevin Wrote:

Fellow DBA's,
I have a process that runs and creates a 90GB table. (Assume I don't use
cursors)

Should I set the fillfactor for the database to it's maximum extent?
Is the fill factor called into play for the batch transactions or is it
applied for every row inserted?
Does it change the answer if I have a lot of nulls in my table?
What if I was using a cursor? Does that change the answer?

Are there any other settings I could change to increase the speed of the
process?

Thanks!

KEVIN.[Email Address Removed] unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed] Visit our Internet site at http://www.reuters.com

Any views expressed in this message are those of the individual
sender, except where the sender specifically states them to be
the views of Reuters Ltd.
MS Sql Server LazyDBA home page