cursors CPU usage

h
i started trace and found some procedures running in background
1) sp_cursorope
2) sp_cursorfetch and sp_cursorclose.
wot are these system procedures and it runs after every 2-3 mins and taking too much cpu.
any help would be appreciated
SanjayGenerally, those are SP's used by the MDAC client when a cursor is opened to
retrieve data by an application. Have a look at what application is being
used at the time. Maybe that application needs tuning.
Those 3 are Extended SP's that are shipped with SQL as standard and can not
be changed.
Cheers
--
Mike Epprecht, Microsoft SQL Server MVP
Epprecht Consulting (PTY) LTD
Johannesburg, South Africa
Mobile: +27-82-552-0268
IM: mike@.NOSPAMepprecht.net
Specialist SQL Server Solutions and Consulting
"sanjay" <anonymous@.discussions.microsoft.com> wrote in message
news:59A038C1-4F19-4AD0-8FD2-440ECEDF008E@.microsoft.com...
> hi
> i started trace and found some procedures running in background.
> 1) sp_cursoropen
> 2) sp_cursorfetch and sp_cursorclose..
> wot are these system procedures and it runs after every 2-3 mins and
taking too much cpu..
> any help would be appreciated.
> Sanjay|||hi mike,
i checked the application..but this sp's run continously after every 2-3 mins and there are no background processes running on..
do they utilise cpu..as site is working too slow..
can we avoid them'
regards
sanjay|||These stored procedures are being called on the behalf of
the application. For instance, if your application
executes a query "select field1, field2,... from table1",
the ADO drivers are converting this to the three cursor
stored procedure calls. Essentially what is happening is
that the first one, sp_cursoropen executes the query
storing the results in a server side cursor (sort of a
temp table), and the sequence of sp_cursorfetch's return
the rows to the application on the client side.
sp_cursorclose closes the server side cursor.
This is usually the result of an application written to
use the defaults when populating an ADO recordset. It
becomes very inefficient if you have a large number of
rows to return, since the default is to return one row to
the client for each execution of the sp_cursorfetch
stored proc. If the third parameter for sp_cursorfetch
is 1 (one), then you are seeing this default behavior.
To fix this you would have to recode the application to
use non-default ADO property settings for properties like
cachesize or cursorlocation.
I hope that this helps.
Matthew Bando
BandoM@.CSCTGI(RemoveThis).com
>--Original Message--
>hi mike,
> i checked the application..but this sp's run
continously after every 2-3 mins and there are no
background processes running on..
>do they utilise cpu..as site is working too slow..
>can we avoid them'
>regards
>sanjay
>.
>