intermittent performance problems while insertig records

[DK]

I have a SP that I use to insert records in a table. This SP is called
hundreds of times per minute.

Most inserts complete very fast. And the profiler data is as follows:

CPU: 0
Reads: 10
Writes: 1
Duration: varies from 1 to 30

But once in a while the insert SP seems to stall and takes a very long
time. Here's the info returned by profiles in this case:

CPU: 0
Reads: 10
Writes: 1
Duration: can vary from 6000 to 60000

Note that the CPU, reads, writes remain the same. But the duration of
the SP increases. What could be the reason for this?? The SP
eventually completes in all cases - its just that they seem to take a
very long time sometimes??

What areas should I investigate??

Thanks in advance,

DK

[Erland Sommarskog]

DK (dk@realmagnet. com) writes:

Most inserts complete very fast. And the profiler data is as follows:

CPU: 0
Reads: 10
Writes: 1
Duration: varies from 1 to 30

But once in a while the insert SP seems to stall and takes a very long
time. Here's the info returned by profiles in this case:

CPU: 0
Reads: 10
Writes: 1
Duration: can vary from 6000 to 60000

Note that the CPU, reads, writes remain the same. But the duration of
the SP increases. What could be the reason for this?? The SP
eventually completes in all cases - its just that they seem to take a
very long time sometimes??

The most likely cause is blocking. That is, another process accesses
data from the table, which prevents the INSERT operation to continue.
It could be that this access operation is poorly written, and does not
make use of indexes.

Another possible cause is autogrow. This is more likely to be the cause
if the database is small. Say that you started with 10 MB database. The
default is to autogrow with 10%. You will get frequent autogrows. On
the other hand, if the database is 10 GB in size, the autogrows will
not appear equally often. The remedy here is to pre-grow to a determined
size.

Rather than the data file autogrowing, it could be the transaction
log that autogrows.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Maj]

you should look at some options like auto shrink and auto growth wich may
use a lot of I/O; you should shrink manually during offline hours and give
auto growth a sufficient value for a week/month of insert activity.

Maj

"DK" <dk@realmagnet. com> wrote in message
news:14******** *************** ***@posting.goo gle.com...

I have a SP that I use to insert records in a table. This SP is called
hundreds of times per minute.

Most inserts complete very fast. And the profiler data is as follows:

CPU: 0
Reads: 10
Writes: 1
Duration: varies from 1 to 30

But once in a while the insert SP seems to stall and takes a very long
time. Here's the info returned by profiles in this case:

CPU: 0
Reads: 10
Writes: 1
Duration: can vary from 6000 to 60000

Note that the CPU, reads, writes remain the same. But the duration of
the SP increases. What could be the reason for this?? The SP
eventually completes in all cases - its just that they seem to take a
very long time sometimes??

What areas should I investigate??

Thanks in advance,

DK

[DK]

Thanks for your replies. I am sure auto-grow is not causing this -
because the datafile size is almost 10Gb and the growth is set to 25%.
And I have noticed this issue quite frequently - sometimes 4-5 times in
a day.

Blocking could be an issue - how can I find if "blocking" is indeed the
reason - does profiler have a counter that indicates "blocking"? ?

Thanks

DK

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

DK (ne*****@yahoo. com) writes:

Blocking could be an issue - how can I find if "blocking" is indeed the
reason - does profiler have a counter that indicates "blocking"? ?

Hm, don't remember off hand if you can track blocking in Profiler.
Look in Books Online under Administrating SQL Server/Monitoriing Server
Performance. There is a very good description of what events and what
data you can catch with Profiler.

The simplest way to see blocking is to run sp_who, and look for non-zero
values in the Blk column. But your blocking scenarios appear to fairly
short, a couples of seconds, so you would have to run it frequently to
see any.

--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Dan Guzman]

Blocking will manifest itself as long duration for the 'Lock: Acquired'
event. You can filter these events based on your target threshold (e.g.
Duration >= 5000). It may be helpful to include the ObjectID column in
the trace.

The sp_who (or sp_who2) technique mentioned by Erland is handy to
monitor and analyze blocking while it is occurring. You can also use
sp_lock to help identify the contended resource.

--
Hope this helps.

Dan Guzman
SQL Server MVP

-----------------------
SQL FAQ links (courtesy Neil Pike):

http://www.ntfaq.com/Articles/Index....partmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
-----------------------

"DK" <ne*****@yahoo. com> wrote in message
news:3f******** *************** @news.frii.net. ..

Thanks for your replies. I am sure auto-grow is not causing this -
because the datafile size is almost 10Gb and the growth is set to 25%.
And I have noticed this issue quite frequently - sometimes 4-5 times in a day.

Blocking could be an issue - how can I find if "blocking" is indeed the reason - does profiler have a counter that indicates "blocking"? ?

Thanks

DK

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[DK]

Thanks for all your suggestions - I have been trying out your
recommendations - but looks like blocking is not the issue here. I tried
running sp_who/ sp_who2 while the inserts seemed to have stuck (and was
taking a long time) - but there was no blocking.

I am wondering if it could be something to so with the network
connection or the database connection that my app. server makes with the
db server. Here's some more info on what exactly is happening:

In my app. I have 3 threads that could be inserting records in this same
table.

Thread 1: loop through 10000 times and insert records in TableA

Thread 2: loop through 5000 times and insert records in TableA

Thread 3: loop through 20000 times and insert records in TableA

All these 3 threads may be running simultaneously. And it often happens
that one of these threads get stuck while the other keeps writing. So
say for example Thread 1 is has written 1003 records; the 1004th record
may take almost 10-60 seconds. And thread2 keeps writing. Thread1
eventually starts again; but again gets stuck at some other number.

While this is happening, I have observed that once a particular thread
gets stuck - its always that thread that keeps having issues. While the
other threads keep going on. This leads me to suspect that it could be
the database connection. But am not sure how I can confirm this? Or if
this could be the case at all? Any ideas how I can go about
investigating this??

Thanks for all your help once again...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Dan Guzman]

Do you have a separate database connection for each thread? How many
CPUs on the database and app servers?

You might examine master..sysproc esses info while a thread is stalled to
see if that indicates why a thread is waiting.
--
Hope this helps.

Dan Guzman
SQL Server MVP
"DK" <ne*****@yahoo. com> wrote in message
news:3f******** *************** @news.frii.net. ..

Thanks for all your suggestions - I have been trying out your
recommendations - but looks like blocking is not the issue here. I tried running sp_who/ sp_who2 while the inserts seemed to have stuck (and was taking a long time) - but there was no blocking.

I am wondering if it could be something to so with the network
connection or the database connection that my app. server makes with the db server. Here's some more info on what exactly is happening:

In my app. I have 3 threads that could be inserting records in this same table.

Thread 1: loop through 10000 times and insert records in TableA

Thread 2: loop through 5000 times and insert records in TableA

Thread 3: loop through 20000 times and insert records in TableA

All these 3 threads may be running simultaneously. And it often happens that one of these threads get stuck while the other keeps writing. So
say for example Thread 1 is has written 1003 records; the 1004th record may take almost 10-60 seconds. And thread2 keeps writing. Thread1
eventually starts again; but again gets stuck at some other number.

While this is happening, I have observed that once a particular thread
gets stuck - its always that thread that keeps having issues. While the other threads keep going on. This leads me to suspect that it could be
the database connection. But am not sure how I can confirm this? Or if
this could be the case at all? Any ideas how I can go about
investigating this??

Thanks for all your help once again...

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

DK (ne*****@yahoo. com) writes:

I am wondering if it could be something to so with the network
connection or the database connection that my app. server makes with the
db server. Here's some more info on what exactly is happening:

In my app. I have 3 threads that could be inserting records in this same
table.

Thread 1: loop through 10000 times and insert records in TableA

Thread 2: loop through 5000 times and insert records in TableA

Thread 3: loop through 20000 times and insert records in TableA

All these 3 threads may be running simultaneously. And it often happens
that one of these threads get stuck while the other keeps writing. So
say for example Thread 1 is has written 1003 records; the 1004th record
may take almost 10-60 seconds. And thread2 keeps writing. Thread1
eventually starts again; but again gets stuck at some other number.

I have to admit that at this point I am completely stumped. If it is
not blocking, nor autogrow, then I can't think of anything obvious.
But here are some ideas how to improve your application, and thus
remove the problem.

1) Issue SET NOCOUNT ON when you connect.
2) Use the bulk-copy interface instead.
3) Form an XML document of all rows to insert, and then send down
all the data to a stored procedure that unpacks the XML into a
result set with OPENXML. This can give a tremendous performance
boost.
--
Erland Sommarskog, SQL Server MVP, so****@algonet. se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp