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

Published Mar 13 2019 08:22 AM 65 Views
Microsoft
First published on MSDN on Feb 04, 2008

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



Version history
Last update:
‎Mar 13 2019 08:23 AM
Updated by: