This SQL Meditation contemplates on some common reasons why INSERT statements are taking a long time (slow)
1. Each Insert in a large batch is causing a log flush thus causing waiting
Is each insert a separate transaction or are those grouped in a transaction? If you review KB 230785 you will notice the section " Increasing performance " discusses how single INSERTs take a long time but when "batched" in a transaction, they take significantly less time. The reason for this is the number of log cache flushes: if you can minimize the log flushes, you can improve performance, but note that there is an upper limit after which "batching" too many inserts into a transaction can be detrimental (law of diminishing returns). Look for WRITELOG waittype here.
Group multiple inserts into a transaction so that all inserts in that transaction will result in a single log flush. The example from the KB article 230785 causes a log flush for every 10 inserts (note the %10)
BEGIN TRAN GO
INSERT INTO tblTest VALUES ('Test') WHILE @@IDENTITY < 50 BEGIN INSERT INTO tblTest VALUES ('Test')
if(0 = cast(@@IDENTITY as int) % 10) BEGIN PRINT 'Commit tran batch' COMMIT TRAN BEGIN TRAN END END GO
COMMIT TRAN GO
2. Each Insert is against a clustered-index primary key and which is also defined as an identity column, causing a natural hotspot (specific to inserts from multiple connections)
Look for multiple PAGELATCH_EX waits occurring from multiple sessions against on a particular page. Find out if a PK is defined as a Clustered Index and IDENTITY column. This will cause all new inserts to go against the same page - at the end of the clustered index because the data is ordered (as opposed to inserts going to different pages when data is not ordered). So this page will be a natural hotspot with threads waiting for PAGELATCH_EX. For more information see How to resolve last-page insert PAGELATCH_EX contention in SQL Server
Move the clustered index to a column that is not the Primary key. Some candidates may be columns where ORDER BY or Joins are performed. Do not simply replace the clustered index with a non-clustered index and as a result remove the clustered index altogether. If so, the INSERT performance can suffer KB297861 (see 3 below). Make sure you place the clustered index on another column.
Inserts against a table with no clustered index (heap) are optimized for saving space, rather than performance. This means that SQL Server will spend more time searching for available space than when a clustered index is used. This is not a very frequent cause for slow inserts, but if you are trying to squeeze every ounce of performance, consider this reason.