Speed Up Insert Performance
The following are some recommendations to speed up insert performance:
potential of page splits choosing the right fill factor. If you see that page
splits are high (using Performance Monitor for example), then try to set the
fill factor on 90 or 80, and if you still see the counter high, try 70. Zero is
the default fill factor. Don't modify that default, if you are adding data to
the end of the table1.
removing unused indexes or duplicated indexes. Updating unneeded indexes have an
impact on write operations because they need to be maintained even though they
are not used. The fewer indexes on the tables you are adding data, the better.
long-running transactions. Keep transactions as short as possible.
5 is slow on insert operations. RAID 1 and RAID 1+0 do much better.
you are doing bulk copy of data, when possible, use the BULK INSERT statement
instead of the bcp utility.
data into a table with a single clustered index is slightly better than
inserting the same data into the same table with a corresponding nonclustered
the size of the data and log files to avoid "autogrowth" events. If these files
grow during a transaction, the operation of allocating new space will affect
moving the log file of the database to a separate drive from
the data files. Better if that separate drive is a RAID 1 or
Comparing Tables Organized with Clustered Indexes versus Heaps.