Speed Up Insert Performance

The following are some recommendations to speed up insert performance:

Reduce 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 table¹.

Consider 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.

Avoid long-running transactions. Keep transactions as short as possible.

RAID 5 is slow on insert operations. RAID 1 and RAID 1+0 do much better.

If you are doing bulk copy of data, when possible, use the BULK INSERT statement instead of the bcp utility.

Inserting 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 index².

Adjust 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 performance.

Consider 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 RAID 1+0.

References.

1. Fill Factor.

2. Comparing Tables Organized with Clustered Indexes versus Heaps.

.Send mail to webmaster@sqlcoffee.com with questions or comments about this web site.