tsql - SQL speed up performance of insert?

Question

I am performing some test on sql server and I want to get the best insert speed possible. The statement I use is something like this:

INSERT INTO db_Test_databse..tbl_test with(rowlock)  
   (  joinid, date_key,   
      col1, col2,col3,col4, col5, col6, col7, col8, col9, col10,  ...     
   )  

SELECT  tabid as joinid, 
        date_key,
        rec_type,  
        col2,  
        dbo.udf_convert_hex_to_dec(col3),  
        col4, col5, col6, col7, col8,  
        dbo.udf_convert_hex_to_dec(col9),  
        dbo.udf_convert_hex_to_dec(col10),  
        ...
from source_table f

There are 25 columns; most of them are of type bigint or int.

I dropped all indexes from the destination table except the primary key which is an identity field.

Any tips on how to improve the performance more?

P.s. In this form I have an average speed of 16.000 rows / sec.

Answer 1

To get the best possible performance you should:

• Remove all triggers and constraints on the table
• Remove all indexes, except for those needed by the insert
• Ensure your clustered index is such that new records will always be inserted at the end of the table (an identity column will do just fine). This prevents page splits (where SQL Server must move data around because an existing page is full)
• Set the fill factor to 0 or 100 (they are equivalent) so that no space in the table is left empty, reducing the number of pages that the data is spread across.
• Change the recovery model of the database to Simple, reducing the overhead for the transaction log.

Are multiple clients inserting records in parallel? If so then you should also consdier the locking implications.

Note that SQL Server can suggest indexes for a given query either by executing the query in SQL Server Management Studio or via the Database Engine Tuning Advisor. You should do this to make sure you haven't removed an index which SQL Server was using to speed up the INSERT.

If this still isn't fast enough then you should consider grouping up inserts an using BULK INSERT instead (or something like the bcp utility or SqlBulkCopy, both of which use BULK INSERT under the covers). This will give the highest throughput when inserting rows.

Also see Optimizing Bulk Import Performance - much of the advice in that article also applies to "normal" inserts.