A lot of sp_cursorfetch statements in profiler

Hallo!
Win2003 server
SQL2000+SP3a
After a couple of months of working fine, our application started to hang
almost every day. It just freeze for all users and after half an hour or so
it starts running back again. During this time, SQL service jumps to 90%
(normally below 10%). In profiler I can see a lot of entries like this:
declare @.P1 int
set @.P1=5918
declare @.P2 int
set @.P2=180157859
declare @.P3 int
set @.P3=4
declare @.P4 int
set @.P4=4
declare @.P5 int
set @.P5=-1
exec sp_cursorprepexec @.P1 output, @.P2 output, NULL, N'SELECT ... ...
FROM dbo.RAPORT A WHERE ( A."ODDE" = 681 AND {fn UCASE( A."STAT")} = {fn
UCASE(''S'')} ) ORDER BY A."ODDE" ASC, A."STAT" ASC, A."MATS" ASC', @.P3
output, @.P4 output, @.P5 output
select @.P1, @.P2, @.P3, @.P4, @.P5
.
.
exec sp_cursorfetch 180157859, 2, 1, 1
.
.
exec sp_cursorclose 180157853
There are practically no other statements in profiler other than these.
Normaly, every user generates a bunch of sp_cursorfatch-es that run in a
second or less, than another user starts another bunch and so on with a lot
of idle time in between.
But when application hangs, all 30 users are generating sp_cursorfatch-es at
the same time ALL the time. 30-60 min non-stop. Around 500 per second.
Nothing else is running on the server at that time (backups...)
What could cause this?
DanAfter some further analysis I found out that every user is running the same
exec sp_cursorprepexec statemants over and over again in a loop. Looks like
application is requesting the same data from a cursor all the time.
(different for every user).
Why would that happen all of the sudden after running so smooth ?
> Hallo!
> Win2003 server
> SQL2000+SP3a
> After a couple of months of working fine, our application started to hang
> almost every day. It just freeze for all users and after half an hour or
so
> it starts running back again. During this time, SQL service jumps to 90%
> (normally below 10%). In profiler I can see a lot of entries like this:
> declare @.P1 int
> set @.P1=5918
> declare @.P2 int
> set @.P2=180157859
> declare @.P3 int
> set @.P3=4
> declare @.P4 int
> set @.P4=4
> declare @.P5 int
> set @.P5=-1
> exec sp_cursorprepexec @.P1 output, @.P2 output, NULL, N'SELECT ... ...
> FROM dbo.RAPORT A WHERE ( A."ODDE" = 681 AND {fn UCASE( A."STAT")} = {fn
> UCASE(''S'')} ) ORDER BY A."ODDE" ASC, A."STAT" ASC, A."MATS" ASC', @.P3
> output, @.P4 output, @.P5 output
> select @.P1, @.P2, @.P3, @.P4, @.P5
> .
> .
> exec sp_cursorfetch 180157859, 2, 1, 1
> .
> .
> exec sp_cursorclose 180157853
> There are practically no other statements in profiler other than these.
> Normaly, every user generates a bunch of sp_cursorfatch-es that run in a
> second or less, than another user starts another bunch and so on with a
lot
> of idle time in between.
> But when application hangs, all 30 users are generating sp_cursorfatch-es
at
> the same time ALL the time. 30-60 min non-stop. Around 500 per second.
> Nothing else is running on the server at that time (backups...)
> What could cause this?
>
> Dan
>|||If no application changes have been made, then a possible cause is that a
new data condition has been introduced which causes the app to behave badly.
I suggest you examine the application code surrounding execution of this
statement to see what condition might cause it to be re-executed so
frequently.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan" <mcseman2002@.hotmail.com> wrote in message
news:%23vrz1vsZEHA.2816@.TK2MSFTNGP11.phx.gbl...
> After some further analysis I found out that every user is running the
same
> exec sp_cursorprepexec statemants over and over again in a loop. Looks
like
> application is requesting the same data from a cursor all the time.
> (different for every user).
> Why would that happen all of the sudden after running so smooth ?
>