Lock and timeout problems SQL2K  
Author Message
planb





PostPosted: Thu Apr 22 04:40:10 CDT 2004 Top

SQL Server Developer >> Lock and timeout problems SQL2K

Hi all,

I posted a question yesterday - here's the reference:



Since nobody replied to this question yet, I'd like to ask if there's more
input needed to go. I didn't post the entire SP intentionally, because it's
quite large.
One thing I found in various test runs is that the SP dramatically
decreases in speed as more calls are issued - there are 20-30 calls per sec
after starting but only 1-2 calls when it ran 3000 times (SQL server then
has an utilization of 100%). I guess it's caused by the surrounding
transaction (which is started in the host language part calling the SP).
But I have to put all together into one single transaction, ensuring either
all work has been done or nothing.

Timeouts may also be caused by growing DB and/or transaction protocol; when
timeout occurs, there's almost heavy HDD activity on SQL server.

Maybe someone can shed a little light onto me.

TIA,
- Michael

SQL Server87  
 
 
Michael





PostPosted: Thu Apr 22 04:40:10 CDT 2004 Top

SQL Server Developer >> Lock and timeout problems SQL2K


> Hi all,
>
> I posted a question yesterday - here's the reference:
>

>
> Since nobody replied to this question yet, I'd like to ask if there's
> more input needed to go. I didn't post the entire SP intentionally,
> because it's quite large.
> One thing I found in various test runs is that the SP dramatically
> decreases in speed as more calls are issued - there are 20-30 calls
> per sec after starting but only 1-2 calls when it ran 3000 times (SQL
> server then has an utilization of 100%). I guess it's caused by the
> surrounding transaction (which is started in the host language part
> calling the SP). But I have to put all together into one single
> transaction, ensuring either all work has been done or nothing.
>
> Timeouts may also be caused by growing DB and/or transaction protocol;
> when timeout occurs, there's almost heavy HDD activity on SQL server.
>
> Maybe someone can shed a little light onto me.
>
> TIA,
> - Michael

What's the problem for my question to keep unanswered? Please tell me if
you don't understand where the problem is, or if there's too less
information to reply.

If someone could help me, I'd like to provide all info you need to help,
but please tell me.

- Michael