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")} = &
#123;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 ?
>

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?
Dan
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 ?

> 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 ?
>

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 ?
>

A lot of sp_cursorfetch in profiler.

Hi all.
I am looking at some profiler logging and gets a lot of the lines looking
like:
exec sp_cursorfetch 180150024, 2, 0, 1
This comes after running a simple prosedure that select some lines.
All i VB6. I guess it has something to do on how we retrieve data (ADO).
Does anyone have a few hints on where to look to get rid of all these lines.
Or what to try.
thanx all.
geirYou should open a "firehose" cursor, AKA "default resultset", AKA adOpenForw
ardOnly (try this
attribute on your recordset).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Geir Holme" <geir@.multicase.no> wrote in message news:OQ2SDd0EFHA.3536@.TK2MSFTNGP15.phx.gb
l...
> Hi all.
> I am looking at some profiler logging and gets a lot of the lines looking
like:
> exec sp_cursorfetch 180150024, 2, 0, 1
>
> This comes after running a simple prosedure that select some lines.
> All i VB6. I guess it has something to do on how we retrieve data (ADO). D
oes anyone have a few
> hints on where to look to get rid of all these lines. Or what to try.
>
> thanx all.
> geir
>|||sp_cursorfetch is a pseudo stored procedure (server internal) used by OLE
DB, ODBC, ADO, and DB-Library APIs which map cursors over the result sets of
executed SQL statements. Check the cursor type and locking mode of your
cursor to investigate any performance problems. A quick search on SQL Server
BOL will give you more idea on sp_cursorfetch. Read topic "API Server
Cursors" ...
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"Geir Holme" <geir@.multicase.no> wrote in message
news:OQ2SDd0EFHA.3536@.TK2MSFTNGP15.phx.gbl...
> Hi all.
> I am looking at some profiler logging and gets a lot of the lines looking
> like:
> exec sp_cursorfetch 180150024, 2, 0, 1
>
> This comes after running a simple prosedure that select some lines.
> All i VB6. I guess it has something to do on how we retrieve data (ADO).
> Does anyone have a few hints on where to look to get rid of all these
lines.
> Or what to try.
>
> thanx all.
> geir
>