Meditation: Slow Inserts in SQL Server
Published Feb 10 2019 05:20 PM 20K Views
Microsoft

First published on MSDN on Dec 06, 2012

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.

 

Solution:
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)

 

CREATE TABLE tblTest ( iID int IDENTITY(1,1), strData char(10))
GO

SET NOCOUNT ON
GO

BEGIN TRAN

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 defined as an identity column, causing a natural hotspot 

This issue occurs with multiple connections performing INSERTs on the same objec.t 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

 

Solution:
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.

3. Inserts are slower against a Heap (no Clustered index)

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.

 

Solution:
Create a clustered index on the table.

 

 

4. Slow I/O subsystem

Check if response time of the I/O subsystem are suboptimal and if SQL is causing this or it is hardware problem. See Slow Disk IO Troubleshooting

 

 

Solution:
Remove the bottleneck causing the slow I/O subsystem
 

 

Namaste!

 

 

 

Joseph
Co-Authors
Version history
Last update:
‎Nov 19 2021 02:00 PM
Updated by: