Home
%3CLINGO-SUB%20id%3D%22lingo-sub-333984%22%20slang%3D%22en-US%22%3EMeditation%3A%20Slow%20Inserts%20in%20SQL%20Server%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-333984%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSTRONG%3E%20First%20published%20on%20MSDN%20on%20Dec%2006%2C%202012%20%3C%2FSTRONG%3E%3C%2FP%3E%0A%3CDIV%3EThis%20SQL%20Meditation%20contemplates%20on%20some%20common%20reasons%20why%26nbsp%3BINSERT%20statements%26nbsp%3Bare%20taking%20a%20long%20time%20(slow)%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CSTRONG%3E%201.%20Each%20Insert%20in%20a%20large%20batch%20is%20causing%20a%20log%20flush%20thus%20causing%20waiting%20%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3EIs%20each%20insert%20a%20separate%20transaction%20or%20are%20those%20grouped%20in%20a%20transaction%3F%20If%20you%20review%20KB%20%3CA%20title%3D%22230785%22%20href%3D%22http%3A%2F%2Fsupport.microsoft.com%2F%3Fid%3D230785%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20230785%20%3C%2FA%3E%20you%20will%20notice%20the%20section%20%22%20%3CEM%3E%20Increasing%20performance%20%3C%2FEM%3E%20%22%20discusses%20how%20single%20INSERTs%20take%20a%20long%20time%20but%20when%20%22batched%22%20in%20a%26nbsp%3B%20transaction%2C%20they%20take%20significantly%20less%20time.%26nbsp%3B%20The%20reason%20for%20this%20is%20the%20number%20of%20log%20cache%20flushes%3A%20if%20you%20can%20minimize%20the%20log%20flushes%2C%20you%20can%20improve%20performance%2C%20but%20note%20that%20there%20is%20an%20upper%20limit%20after%20which%20%22batching%22%20too%20many%20inserts%20into%20a%20transaction%20can%20be%20detrimental%20(law%20of%20diminishing%20returns).%20Look%20for%20WRITELOG%20waittype%20here.%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CBR%20%2F%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3ESolution%3A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3EGroup%20multiple%20inserts%20into%20a%20transaction%20so%20that%20all%20inserts%20in%20that%20transaction%20will%20result%20in%20a%20single%20log%20flush.%20The%20example%20from%20the%20KB%20article%20230785%20causes%20a%20log%20flush%20for%20every%2010%20inserts%20(note%20the%20%2510)%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CBR%20%2F%3E%3CBR%20%2F%3EBEGIN%20TRAN%20%3CBR%20%2F%3EGO%20%3CBR%20%2F%3E%3CBR%20%2F%3EINSERT%20INTO%20tblTest%20VALUES%20('Test')%20%3CBR%20%2F%3EWHILE%20%40%40IDENTITY%20%26lt%3B%2050%20%3CBR%20%2F%3EBEGIN%20%3CBR%20%2F%3EINSERT%20INTO%20tblTest%20VALUES%20('Test')%20%3CBR%20%2F%3E%3CBR%20%2F%3Eif(0%20%3D%20cast(%40%40IDENTITY%20as%20int)%20%25%2010)%20%3CBR%20%2F%3EBEGIN%20%3CBR%20%2F%3EPRINT%20'Commit%20tran%20batch'%20%3CBR%20%2F%3ECOMMIT%20TRAN%20%3CBR%20%2F%3EBEGIN%20TRAN%20%3CBR%20%2F%3EEND%20%3CBR%20%2F%3EEND%20%3CBR%20%2F%3EGO%20%3CBR%20%2F%3E%3CBR%20%2F%3ECOMMIT%20TRAN%20%3CBR%20%2F%3EGO%20%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CSTRONG%3E%202.%20Each%20Insert%20is%20against%26nbsp%3Ba%20clustered-index%20primary%20key%26nbsp%3Band%20which%20is%20also%20defined%20as%20an%20identity%20column%2C%20causing%20a%20natural%20hotspot%20(specific%20to%20inserts%20from%20multiple%20connections)%20%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3ELook%20for%20multiple%20PAGELATCH_EX%20waits%20occurring%20from%20multiple%20sessions%20against%20on%20a%20particular%20page.%20Find%20out%20if%20a%20PK%20is%20defined%20as%20a%20Clustered%20Index%20and%20IDENTITY%20column.%20This%20will%20cause%20all%20new%20inserts%20to%20go%20against%20the%20same%20page%20-%20at%20the%20end%20of%20the%20clustered%20index%20because%20the%20data%20is%20ordered%20(as%20opposed%20to%20inserts%20going%20to%20different%20pages%20when%20data%20is%20not%20ordered).%20So%20this%20page%20will%20be%20a%20natural%20hotspot%20with%20threads%20waiting%20for%20PAGELATCH_EX.%20For%20more%20information%20see%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fhelp%2F4460004%2Fperformance-degradation-because-of-pagelatch-ex-contention-issue-in-sq%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20How%20to%20resolve%20last-page%20insert%20PAGELATCH_EX%20contention%20in%20SQL%20Server%3C%2FA%3E%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3ESolution%3A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3EMove%20the%20clustered%20index%20to%20a%20column%20that%20is%20not%20the%20Primary%20key.%20Some%20candidates%20may%20be%20columns%20where%20ORDER%20BY%20or%20Joins%20are%20performed.%20Do%20not%20simply%20replace%20the%20clustered%20index%20with%20a%20non-clustered%20index%20and%20as%20a%20result%20remove%20the%20clustered%20index%20altogether.%20If%20so%2C%20the%20INSERT%20performance%20can%20suffer%20KB297861%20(see%203%20below).%26nbsp%3BMake%20sure%20you%26nbsp%3Bplace%20the%20clustered%20index%20on%20another%20column.%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CBR%20%2F%3E%3CDIV%3EThere%20is%20a%20fix%20in%20SQL%202005%20that%20resolves%20slower%20inserts%20problem%20introduced%20by%20new%20page%20split%20logic.%26nbsp%3BSee%20%3CA%20href%3D%22http%3A%2F%2Fsupport.microsoft.com%2Fdefault.aspx%3Fscid%3Dkb%3BEN-US%3B940545%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20940545%20FIX%3A%20The%20performance%20of%20INSERT%20operations%20against%20a%20table%20that%20contains%20an%20identity%20column%20may%20be%20slow%20in%20SQL%20Server%202005%3C%2FA%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3EAlso%2C%20you%20can%20partition%20the%20table%2Fclustered%20index%20to%20reduce%20(but%20not%20eliminate)%20the%20contention%20on%20a%20single%20data%2Findex%20page.%20See%20%3CA%20href%3D%22http%3A%2F%2Fsqlcat.com%2Fsqlcat%2Fb%2Ftechnicalnotes%2Farchive%2F2009%2F09%2F22%2Fresolving-pagelatch-contention-on-highly-concurrent-insert-workloads-part-1.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20http%3A%2F%2Fsqlcat.com%2Fsqlcat%2Fb%2Ftechnicalnotes%2Farchive%2F2009%2F09%2F22%2Fresolving-pagelatch-contention-on-highly-concurrent-insert-workloads-part-1.aspx%3C%2FA%3E%3C%2FDIV%3E%3CBR%20%2F%3E%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CSTRONG%3E%203.%20Inserts%20are%20slower%20against%20a%20Heap.%20(see%20%3C%2FSTRONG%3E%20%3CA%20href%3D%22http%3A%2F%2Fsupport.microsoft.com%2Fdefault.aspx%3Fscid%3Dkb%3BEN-US%3B297861%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20%3CSTRONG%3E%20297861%20PRB%3A%20Poor%20Performance%20on%20a%20Heap%20%3C%2FSTRONG%3E%20%3C%2FA%3E%20%3CSTRONG%3E%20)%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3EInserts%20against%20a%20table%20with%20no%20clustered%20index%20(heap)%20are%20optimized%20for%20saving%20space%2C%20rather%20than%20performance.%20This%20means%20that%20SQL%20Server%20will%20spend%20more%20time%20searching%20for%20available%20space%20than%20when%20a%20clustered%20index%20is%20used.%20This%20is%20not%20a%20very%20frequent%20cause%20for%20slow%20inserts%2C%20but%20if%20you%20are%20trying%20to%20squeeze%20every%20ounce%20of%20performance%2C%20consider%20this%20reason.%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3ESolution%3A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3ECreate%20a%20clustered%20index%20on%20the%20table.%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%3CSTRONG%3E%204.%20Slow%20I%2FO%20subsystem.%20%3C%2FSTRONG%3E%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3ECheck%20if%20response%20time%20of%20the%20I%2FO%20subsystem%20are%20suboptimal%20and%20if%20SQL%20is%20causing%20this%20or%20it%20is%20hardware%20problem.%20See%20%3CA%20title%3D%22Slow%20disk%20I%2FO%20Troubleshooting%22%20href%3D%22http%3A%2F%2Fblogs.msdn.com%2Fb%2Fsqlmeditation%2Farchive%2F2012%2F12%2F06%2Ftroubleshooting-slow-i-o-problems-with-sql-server.aspx%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20Slow%20Disk%20IO%20Troubleshooting%3C%2FA%3E%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3ESolution%3A%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3ERemove%20the%20bottleneck%20causing%20the%20slow%20I%2FO%20subsystem%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3ENamaste!%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3E%26nbsp%3B%3C%2FDIV%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CDIV%3EJoseph%3C%2FDIV%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-333984%22%20slang%3D%22en-US%22%3E%3CP%3EFirst%20published%20on%20MSDN%20on%20Dec%2006%2C%202012%20This%20SQL%20Meditation%20contemplates%20on%20some%20common%20reasons%20why%26nbsp%3BINSERT%20statements%26nbsp%3Bare%20taking%20a%20long%20time%20(slow)1.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-333984%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EPerformance%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Etroubleshooting%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
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)

 

 

 



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

 

 

 

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.

 


There is a fix in SQL 2005 that resolves slower inserts problem introduced by new page split logic. See 940545 FIX: The performance of INSERT operations against a table that contains an identity column m...

Also, you can partition the table/clustered index to reduce (but not eliminate) the contention on a single data/index page. See http://sqlcat.com/sqlcat/b/technicalnotes/archive/2009/09/22/resolving-pagelatch-contention-on-high...

 

 

3. Inserts are slower against a Heap. (see 297861 PRB: Poor Performance on a Heap )

 

 

 

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