Cursor Vs. Performance

We have a program that is opening a recordset as a forward only cursor.
What we are experiencing is that the cursor is being opened thousands of
times per minute by our different clients. When this happens our entire
system slows down. I have asked that we not use cursors and return the
entire recordset and release it. During these periods of slow downs we are
experiencing a high number of locks and a high number of Work Tables being
created as well as a high number of Page Splits. The odd thing is that when
I run Profiler, during these slow times, I am not showing any Duration
issues. I do see a large amount of batches being processed (mostly curor
fetches).
I have told the programmers to fix the cursor problem, but I have been
tasked to prove that this is the cause of the slow downs. Any ideas?some thoughts...
1. You're right. This needs to change...
2. you may not see duration problems in profiler. Each individual cursor
(whether it's TSQL or API) might only take a MS or so. But... I just
finsihed working on a system doing over 1000 sp_cursorfetches per second.
Each call showed 0 or 10MS in Profiler. But the aggregate time for these
calls was taking close to 75% of CPU (note, Profiler will generally not show
a duration less than 10MS. SQL is fast, but it's not faster than the speed
of light and cursors take more than 0MS. I usually round to 2.5MS as an apx
time for cursor duration times...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Joe Jackson" <jj@.microsoft.com> wrote in message
news:u6Gt%23Jt2DHA.2604@.TK2MSFTNGP09.phx.gbl...
> We have a program that is opening a recordset as a forward only cursor.
> What we are experiencing is that the cursor is being opened thousands of
> times per minute by our different clients. When this happens our entire
> system slows down. I have asked that we not use cursors and return the
> entire recordset and release it. During these periods of slow downs we
are
> experiencing a high number of locks and a high number of Work Tables being
> created as well as a high number of Page Splits. The odd thing is that
when
> I run Profiler, during these slow times, I am not showing any Duration
> issues. I do see a large amount of batches being processed (mostly curor
> fetches).
> I have told the programmers to fix the cursor problem, but I have been
> tasked to prove that this is the cause of the slow downs. Any ideas?
>|||One other strange thing, CPU was only running at about 30%.
"Brian Moran" <brian@.solidqualitylearning.com> wrote in message
news:Ou9Zevt2DHA.2548@.tk2msftngp13.phx.gbl...
> some thoughts...
> 1. You're right. This needs to change...
> 2. you may not see duration problems in profiler. Each individual cursor
> (whether it's TSQL or API) might only take a MS or so. But... I just
> finsihed working on a system doing over 1000 sp_cursorfetches per second.
> Each call showed 0 or 10MS in Profiler. But the aggregate time for these
> calls was taking close to 75% of CPU (note, Profiler will generally not
show
> a duration less than 10MS. SQL is fast, but it's not faster than the speed
> of light and cursors take more than 0MS. I usually round to 2.5MS as an
apx
> time for cursor duration times...
>
> --
> Brian Moran
> Principal Mentor
> Solid Quality Learning
> SQL Server MVP
> http://www.solidqualitylearning.com
>
> "Joe Jackson" <jj@.microsoft.com> wrote in message
> news:u6Gt%23Jt2DHA.2604@.TK2MSFTNGP09.phx.gbl...
> > We have a program that is opening a recordset as a forward only cursor.
> > What we are experiencing is that the cursor is being opened thousands of
> > times per minute by our different clients. When this happens our
entire
> > system slows down. I have asked that we not use cursors and return the
> > entire recordset and release it. During these periods of slow downs we
> are
> > experiencing a high number of locks and a high number of Work Tables
being
> > created as well as a high number of Page Splits. The odd thing is that
> when
> > I run Profiler, during these slow times, I am not showing any Duration
> > issues. I do see a large amount of batches being processed (mostly
curor
> > fetches).
> >
> > I have told the programmers to fix the cursor problem, but I have been
> > tasked to prove that this is the cause of the slow downs. Any ideas?
> >
> >
>|||With a very large number of cursor fetches, assuming your clients are on =separate system to the database, you will be generating a very large =number of network round-trips from the client, to SQl Server and back =again.
So I guess your current bottleneck may possibly be network traffic - =with large number of small packets carrying 1 row each. getting rid of =the cursors will most likely help the network as well.
Mike John
"Joe Jackson" <jj@.microsoft.com> wrote in message =news:%233BKP9t2DHA.4060@.TK2MSFTNGP11.phx.gbl...
> One other strange thing, CPU was only running at about 30%.
> > > "Brian Moran" <brian@.solidqualitylearning.com> wrote in message
> news:Ou9Zevt2DHA.2548@.tk2msftngp13.phx.gbl...
> > some thoughts...
> >
> > 1. You're right. This needs to change...
> > 2. you may not see duration problems in profiler. Each individual =cursor
> > (whether it's TSQL or API) might only take a MS or so. But... I just
> > finsihed working on a system doing over 1000 sp_cursorfetches per =second.
> > Each call showed 0 or 10MS in Profiler. But the aggregate time for =these
> > calls was taking close to 75% of CPU (note, Profiler will generally =not
> show
> > a duration less than 10MS. SQL is fast, but it's not faster than the =speed
> > of light and cursors take more than 0MS. I usually round to 2.5MS as =an
> apx
> > time for cursor duration times...
> >
> >
> > -- > >
> > Brian Moran
> > Principal Mentor
> > Solid Quality Learning
> > SQL Server MVP
> > http://www.solidqualitylearning.com
> >
> >
> > "Joe Jackson" <jj@.microsoft.com> wrote in message
> > news:u6Gt%23Jt2DHA.2604@.TK2MSFTNGP09.phx.gbl...
> > > We have a program that is opening a recordset as a forward only =cursor.
> > > What we are experiencing is that the cursor is being opened =thousands of
> > > times per minute by our different clients. When this happens our
> entire
> > > system slows down. I have asked that we not use cursors and =return the
> > > entire recordset and release it. During these periods of slow =downs we
> > are
> > > experiencing a high number of locks and a high number of Work =Tables
> being
> > > created as well as a high number of Page Splits. The odd thing is =that
> > when
> > > I run Profiler, during these slow times, I am not showing any =Duration
> > > issues. I do see a large amount of batches being processed =(mostly
> curor
> > > fetches).
> > >
> > > I have told the programmers to fix the cursor problem, but I have =been
> > > tasked to prove that this is the cause of the slow downs. Any =ideas?
> > >
> > >
> >
> >
> >