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

Cursor taking longer to open at every open

I am connecting to a server using ODBC. The program I am
running opens, fetches and closes a cursor at regular
intervals. It is a 'greater-than-or-equal-to'
FAST_FORWARD, READONLY cursor. Isolation level is READ
UNCOMMITED. We use a dedicated connection for the
cursor. Using TCP/IP to connect.
The program is moving from the beginning of a million row
table, opening and closing the cursor every time it wants
a new row - it does not want every row. Bad code -
DEFINATELY! But a re-write may not be a usable option.
When I run my program locally (pointing to the database on
the server), it takes the same amount of time every time
the cursor is opened. The ODBC DSN is defined locally.
When I run the program directly on the server (same
database), the amount of time to open the cursor gets
progressivly worse. Almost as though on the server it is
reading from the beginning of the table everytime, and as
the program logically moves through the table. The ODBC
DSN is defined on the server.Just curious why you're using cursors and not the
preferred method of T-SQL, which would make your access
much faster. I know you mention bad code, but if it's
ingrained, and other than, say, making sure you have thr
right index or hints, there's not much you can do. SQL
Server is not best when used with cursors for large
things; using T-SQL is like night and day.
>--Original Message--
>I am connecting to a server using ODBC. The program I am
>running opens, fetches and closes a cursor at regular
>intervals. It is a 'greater-than-or-equal-to'
>FAST_FORWARD, READONLY cursor. Isolation level is READ
>UNCOMMITED. We use a dedicated connection for the
>cursor. Using TCP/IP to connect.
>The program is moving from the beginning of a million row
>table, opening and closing the cursor every time it wants
>a new row - it does not want every row. Bad code -
>DEFINATELY! But a re-write may not be a usable option.
>When I run my program locally (pointing to the database
on
>the server), it takes the same amount of time every time
>the cursor is opened. The ODBC DSN is defined locally.
>When I run the program directly on the server (same
>database), the amount of time to open the cursor gets
>progressivly worse. Almost as though on the server it is
>reading from the beginning of the table everytime, and as
>the program logically moves through the table. The ODBC
>DSN is defined on the server.
>.
>

Cube deployment

Hi,

I am trying to silently deploy a cube within my install using the following command line:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\Microsoft.AnalysisServices.Deployment.exe" "c:\MyCubesFolder\Cubes.asdatabase" /s

but i keep getting the error message:

Reading input files...

Done

The 'Name' property cannot contain any of the following characters: . , ; ' ` : / * | ? " & % $ ! + = ( ) [ ] { } < >

The file contains URLs in some name properties with the ':' character. I tried removing those URLs but that didnt help.

Also, I was able to deploy the cube successfully using the same file from the Microsoft.AnalysisServices.Deployment.exe IDE - so I know my file is ok.

But I want to wrap this into the install and deploy it silently. Any ideas?

Thanks

Are you doing all steps from the list below ?

To create a XMLA script from solution project you have to buld solution (generas .asdatabase file), then run deployment wizard with option specifying that you want to generate XMLA script. Step by step guide:

Run script to build solution:
devenv.exe YourSolution.sln /build development /out BuildOutputLog.log
Instead of /build you can specify /rebuild
Instead of development you can specify other soluction configuration, like: Release
Example: "c:\Program Files\Microsoft Visual Studio 8\Common7\ide\devenv.exe" "c:\documents and settings\vidas\my documents\visual studio 2005\projects\MySolution\MySolution.sln" /build development /out BuildOutputLog.log Optionally run deployment wizard in answer mode to generate deployment script configuration. This is interactive step and can be done just once. Command:
Microsoft.AnalysisServices.Deployment.exe MySolution.asdatabase /a
Here /a runs deployment wizard in answer mode.
Example: Microsoft.AnalysisServices.Deployment.exe "c:\documents and settings\Vidas\My Documents\Visual Studio 2005\Projects\MySolution\MySolution\bin\MySolution.asdatabase" /a Run deployment wizard command line script to generate XMLA file:
Microsoft.AnalysisServices.Deployment.exe MySolution.asDatabase /d /o:c:\MySolutionXMLAScript.xmla
Example: Microsoft.analysisServices.Deployment.exe "c:\Documents And Settings\Vidas\My Documents\Visual Studio 2005\Projects\MySolution\MySolution\bin\MySolution.asDatabase" /d /o:c:\MySolutionXMLAScript.xmla|||

I think most of those names will refer to internal SSAS objects like annotations, so that is not likely to be the issue, specially if you can deploy from the UI. The ouput from silently deploying Adventure Works on my laptop look like the following. Notice that the line where you are getting hte error is where it should be attempting to connect to the target server. This is probably where the illegal character is. This should be in your .deploymentOptions file and it would depend on which configuration you had build last.

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE>microsoft.analysisservices.deployment.exe "C:\Data\Projects\SSAS 2005 Samples\Enterprise\bin\Adventure Works DW.asdatabase" /s

Reading input files...
Done
Connecting to the localhost\sql05 server
Database, Adventure Works DW, found on server, localhost\sql05. Applying configuration settings and options...
Analyzing configuration settings...
Done
Analyzing optimization settings...
Done
Analyzing storage information...
Done
Analyzing security information...
Done
Generating processing sequence...
Deploying the 'Adventure Works DW' database to 'localhost\sql05'.
Done

Hope this helps

|||

Thanks for the replies

During install, I am updating the .deploymentOptions file with the ip address and instance where of the Analysis server where the cube is to be deployed. When I replaced the ip address with the system name, it seemed to work fine - looks like it cannot deal with the "." in the ip address - defect?

|||I think it might be. You should log this at http://connect.microsoft.com, it sounds like it might be an issue with the deployment wizard.

Cube deployment

Hi,

I am trying to silently deploy a cube within my install using the following command line:

"C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\Microsoft.AnalysisServices.Deployment.exe" "c:\MyCubesFolder\Cubes.asdatabase" /s

but i keep getting the error message:

Reading input files...

Done

The 'Name' property cannot contain any of the following characters: . , ; ' ` : / * | ? " & % $ ! + = ( ) [ ] { } < >

The file contains URLs in some name properties with the ':' character. I tried removing those URLs but that didnt help.

Also, I was able to deploy the cube successfully using the same file from the Microsoft.AnalysisServices.Deployment.exe IDE - so I know my file is ok.

But I want to wrap this into the install and deploy it silently. Any ideas?

Thanks

Are you doing all steps from the list below ?

To create a XMLA script from solution project you have to buld solution (generas .asdatabase file), then run deployment wizard with option specifying that you want to generate XMLA script. Step by step guide:

Run script to build solution:
devenv.exe YourSolution.sln /build development /out BuildOutputLog.log
Instead of /build you can specify /rebuild
Instead of development you can specify other soluction configuration, like: Release
Example: "c:\Program Files\Microsoft Visual Studio 8\Common7\ide\devenv.exe" "c:\documents and settings\vidas\my documents\visual studio 2005\projects\MySolution\MySolution.sln" /build development /out BuildOutputLog.log Optionally run deployment wizard in answer mode to generate deployment script configuration. This is interactive step and can be done just once. Command:
Microsoft.AnalysisServices.Deployment.exe MySolution.asdatabase /a
Here /a runs deployment wizard in answer mode.
Example: Microsoft.AnalysisServices.Deployment.exe "c:\documents and settings\Vidas\My Documents\Visual Studio 2005\Projects\MySolution\MySolution\bin\MySolution.asdatabase" /a Run deployment wizard command line script to generate XMLA file:
Microsoft.AnalysisServices.Deployment.exe MySolution.asDatabase /d /o:c:\MySolutionXMLAScript.xmla
Example: Microsoft.analysisServices.Deployment.exe "c:\Documents And Settings\Vidas\My Documents\Visual Studio 2005\Projects\MySolution\MySolution\bin\MySolution.asDatabase" /d /o:c:\MySolutionXMLAScript.xmla|||

I think most of those names will refer to internal SSAS objects like annotations, so that is not likely to be the issue, specially if you can deploy from the UI. The ouput from silently deploying Adventure Works on my laptop look like the following. Notice that the line where you are getting hte error is where it should be attempting to connect to the target server. This is probably where the illegal character is. This should be in your .deploymentOptions file and it would depend on which configuration you had build last.

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE>microsoft.analysisservices.deployment.exe "C:\Data\Projects\SSAS 2005 Samples\Enterprise\bin\Adventure Works DW.asdatabase" /s

Reading input files...
Done
Connecting to the localhost\sql05 server
Database, Adventure Works DW, found on server, localhost\sql05. Applying configuration settings and options...
Analyzing configuration settings...
Done
Analyzing optimization settings...
Done
Analyzing storage information...
Done
Analyzing security information...
Done
Generating processing sequence...
Deploying the 'Adventure Works DW' database to 'localhost\sql05'.
Done

Hope this helps

|||

Thanks for the replies

During install, I am updating the .deploymentOptions file with the ip address and instance where of the Analysis server where the cube is to be deployed. When I replaced the ip address with the system name, it seemed to work fine - looks like it cannot deal with the "." in the ip address - defect?

|||I think it might be. You should log this at http://connect.microsoft.com, it sounds like it might be an issue with the deployment wizard.