[Company Logo Image] 

Home Up Contents Coffee Break Credits Glossary Links Search

Speed Up Insert Performance



Analysis Services
CLR Integration
High Availability
Open Source
SQL Server 2008
SQL Server 2012
SQL Server 2014
SQL Server 2016
SQL Server 2017
SQL Server 2019

Speed Up Insert Performance

The following are some recommendations to speed up insert performance:

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

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

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

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

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

bulletInserting 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 index2.

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

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



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.