Sporadic but periodic slow Inserts into a table – Why you should apply SQL Server 2005 SP2

Analyzing a SQL Query trace with SAP’s ST05 SQL Trace, a pattern could be observed which looks like several inserts into a specific table taking place as expected in the milliseconds range but suddenly one inserting query takes hundreds of milliseconds up to a few seconds. Afterwards inserts are back to normal millisecond times and suddenly a long one happens again. One can see a nice pattern of periodic long inserts. Looking at a ST05 output which got consolidated in Excel, the situation could look like:

Duration in ms

Obj. Name

Records

Statement

 7246

COEP  

12

 INSERT VALUES…

209

COEP  

12

 INSERT VALUES…

397

COEP  

12

 INSERT VALUES…

287

COEP  

12

 INSERT VALUES…

8942

COEP  

12

 INSERT VALUES…

468

COEP  

12

 INSERT VALUES…

154

COEP  

12

 INSERT VALUES…

749

COEP  

12

 INSERT VALUES…

9724

COEP  

12

 INSERT VALUES…

387

COEP  

12

 INSERT VALUES…

637

COEP  

12

 INSERT VALUES…

481