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

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

quote:

---

> 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

quote:

---

> 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

quote:

---

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

quote:

---

> 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

quote:

---

> 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

quote:

---

> 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...
entire[QUOTE]
> are
being[QUOTE]
> when
curor[QUOTE]
>

---

|||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.=20
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...

quote:

---

> One other strange thing, CPU was only running at about 30%.
>=20
>=20
> "Brian Moran" <brian@.solidqualitylearning.com> wrote in message
> news:Ou9Zevt2DHA.2548@.tk2msftngp13.phx.gbl...
cursor[QUOTE]
second.[QUOTE]
these[QUOTE]
not[QUOTE]
> show
speed[QUOTE]
an[QUOTE]
> apx
cursor.[QUOTE]
thousands of[QUOTE]
> entire
return the[QUOTE]
downs we[QUOTE]
Tables[QUOTE]
> being
that[QUOTE]
Duration[QUOTE]
(mostly[QUOTE]
> curor
been[QUOTE]
ideas?[QUOTE]
>=20
>

---

sql

CSV Export error

Hi,

After exporting a report to CSV format, when opening the file in Excel, all the data in a row comes into a single column, than spliting into different columns.

We can split into different columns using 'Text to columns' option from 'Data' Menu, but does not do defaultly as a CSV file is supposed to do.

Is this is bug with Reporting Service? Or am i missing something?

I am using release version of MS Reporting Service 2005.

Thanks,

Anobe

I experienced this problem too. The default setting is unicode, you have to specify ASCII for csv rendering. We were able to fix it by changing the config file.

File to change:

C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\rsreportserver.config

Before:

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>

After:

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">

<Configuration>

<DeviceInfo>

<Encoding>ASCII</Encoding>

</DeviceInfo>

</Configuration>

</Extension>

|||

Thanks buddy,

this was very helpful. I didn't do the exact way, since i cannot change the config file. So, i handled the situation in the code using System.Text.UniEncoding.

|||

hi anobe....

i am facing the same situation like u...i cant change the config file. so i am wondering how did u handled the situation

using the system.text.uniencoding ? could you please elobrate further

thanks bro

CSV Export error

Hi,

After exporting a report to CSV format, when opening the file in Excel, all the data in a row comes into a single column, than spliting into different columns.

We can split into different columns using 'Text to columns' option from 'Data' Menu, but does not do defaultly as a CSV file is supposed to do.

Is this is bug with Reporting Service? Or am i missing something?

I am using release version of MS Reporting Service 2005.

Thanks,

Anobe

I experienced this problem too. The default setting is unicode, you have to specify ASCII for csv rendering. We were able to fix it by changing the config file.

File to change:

C:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer\rsreportserver.config

Before:

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering"/>

After:

<Extension Name="CSV" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">

<Configuration>

<DeviceInfo>

<Encoding>ASCII</Encoding>

</DeviceInfo>

</Configuration>

</Extension>

|||

Thanks buddy,

this was very helpful. I didn't do the exact way, since i cannot change the config file. So, i handled the situation in the code using System.Text.UniEncoding.

|||

hi anobe....

i am facing the same situation like u...i cant change the config file. so i am wondering how did u handled the situation

using the system.text.uniencoding ? could you please elobrate further

thanks bro

|||In case you didn't get your problem solved (or for others out there with the same problem):

I was having this problem too and changing the encoding of the report to ASCII (default is UTF8 I think) fixed the problem.

I am calling my report through the url, you can pass the encoding as a url parameter, for example:
http://reportservername/ReportServer/report?rs:Command=Render&rs:Format=CSV&rc:Encoding=ASCII

CSRSS.EXE and SP3

Hi,
I've had this same issue on two machines, one Win2k3 Server and a XP SP1
machine.
The problem is that after installing SP3 opening any SQL Gui tool (I think
it's only that as osql.exe don't cause this) including the SQL Service
Manager causes CSRSS.EXE to spike at 100% and basically render the machine
unusable. Closer inspection reveals that it's continually respawning
whichever application you opened.
I've seen this referenced [1] a while back but no real cause/solutions :S
Any help would greatly appreciated as it's quite urgent that I get a working
SQL server.
Thanks in advance,
Armand
[1]
http://www.derkeiler.com/Newsgroups/microsoft.public.sqlserver.security/2003-01/5353.htmlJust a quick update - osql.exe causes exactly the same effect.
"Armand du Plessis" wrote:
> Hi,
> I've had this same issue on two machines, one Win2k3 Server and a XP SP1
> machine.
> The problem is that after installing SP3 opening any SQL Gui tool (I think
> it's only that as osql.exe don't cause this) including the SQL Service
> Manager causes CSRSS.EXE to spike at 100% and basically render the machine
> unusable. Closer inspection reveals that it's continually respawning
> whichever application you opened.
> I've seen this referenced [1] a while back but no real cause/solutions :S
> Any help would greatly appreciated as it's quite urgent that I get a working
> SQL server.
> Thanks in advance,
> Armand
> [1]
> http://www.derkeiler.com/Newsgroups/microsoft.public.sqlserver.security/2003-01/5353.html

CSRSS.EXE and SP3

Hi,
I've had this same issue on two machines, one Win2k3 Server and a XP SP1
machine.
The problem is that after installing SP3 opening any SQL Gui tool (I think
it's only that as osql.exe don't cause this) including the SQL Service
Manager causes CSRSS.EXE to spike at 100% and basically render the machine
unusable. Closer inspection reveals that it's continually respawning
whichever application you opened.
I've seen this referenced [1] a while back but no real cause/solutions :S
Any help would greatly appreciated as it's quite urgent that I get a working
SQL server.
Thanks in advance,
Armand
[1]
http://www.derkeiler.com/Newsgroups/...3-01/5353.html
Just a quick update - osql.exe causes exactly the same effect.
"Armand du Plessis" wrote:

> Hi,
> I've had this same issue on two machines, one Win2k3 Server and a XP SP1
> machine.
> The problem is that after installing SP3 opening any SQL Gui tool (I think
> it's only that as osql.exe don't cause this) including the SQL Service
> Manager causes CSRSS.EXE to spike at 100% and basically render the machine
> unusable. Closer inspection reveals that it's continually respawning
> whichever application you opened.
> I've seen this referenced [1] a while back but no real cause/solutions :S
> Any help would greatly appreciated as it's quite urgent that I get a working
> SQL server.
> Thanks in advance,
> Armand
> [1]
> http://www.derkeiler.com/Newsgroups/...3-01/5353.html

CSRSS.EXE and SP3

Hi,
I've had this same issue on two machines, one Win2k3 Server and a XP SP1
machine.
The problem is that after installing SP3 opening any SQL Gui tool (I think
it's only that as osql.exe don't cause this) including the SQL Service
Manager causes CSRSS.EXE to spike at 100% and basically render the machine
unusable. Closer inspection reveals that it's continually respawning
whichever application you opened.
I've seen this referenced [1] a while back but no real cause/solutions :
S
Any help would greatly appreciated as it's quite urgent that I get a working
SQL server.
Thanks in advance,
Armand
[1]
53.html" target="_blank">http://www.derkeiler.com/Newsgroups...
53.htmlJust a quick update - osql.exe causes exactly the same effect.
"Armand du Plessis" wrote:

> Hi,
> I've had this same issue on two machines, one Win2k3 Server and a XP SP1
> machine.
> The problem is that after installing SP3 opening any SQL Gui tool (I think
> it's only that as osql.exe don't cause this) including the SQL Service
> Manager causes CSRSS.EXE to spike at 100% and basically render the machine
> unusable. Closer inspection reveals that it's continually respawning
> whichever application you opened.
> I've seen this referenced [1] a while back but no real cause/solutions
:S
> Any help would greatly appreciated as it's quite urgent that I get a worki
ng
> SQL server.
> Thanks in advance,
> Armand
> [1]
> l" target="_blank">http://www.derkeiler.com/Newsgroups...53.htm
l