Performance Hits - Second Post

Sorry for re-posting - wasn't sure how to -repost the same question. We are
becoming very frustrated with this performance issue with SQL server - any
response would be GREATLY appreciated.
We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
2000. Many people are complaining that Enterprise Manager now takes 4-20
seconds to expand the databases on those servers, where before it was either
instant or 1-2 seconds. Once the intial connection has been made, there are
no further performance issues. Do you know if there is a fix, or have others
experienced this slow performance after the upgrade?
We have compared user desktops' memory, processors, hard drives, etc, and
there is no trend there.
We would appreciate any advice before moving forward w/ our other servers'
upgrades!
Hi
How many databases are on the server?
Have you applied SQL Server SP3a to the clients also (EM and QA do get fixes
too)?
On a client machine, run in QA, "SELECT * FROM Master.dbo.sysdatabases" and
see how long it takes to return.
Regards
Mike
"JaneDoe" wrote:

> Sorry for re-posting - wasn't sure how to -repost the same question. We are
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was either
> instant or 1-2 seconds. Once the intial connection has been made, there are
> no further performance issues. Do you know if there is a fix, or have others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>
|||Do you have Auto-Close turned on for those clients?
Andrew J. Kelly SQL MVP
"JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
> Sorry for re-posting - wasn't sure how to -repost the same question. We
> are
> becoming very frustrated with this performance issue with SQL server - any
> response would be GREATLY appreciated.
> We have recently upgraded 4 SQL servers to Windows 2003, running SQL
> Server
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was
> either
> instant or 1-2 seconds. Once the intial connection has been made, there
> are
> no further performance issues. Do you know if there is a fix, or have
> others
> experienced this slow performance after the upgrade?
> We have compared user desktops' memory, processors, hard drives, etc, and
> there is no trend there.
> We would appreciate any advice before moving forward w/ our other servers'
> upgrades!
>
|||Make sure you have SP3a applied to the client machines. See if these help:
http://support.microsoft.com/default...b;en-us;889696
http://support.microsoft.com/default...b;en-us;282416
Ryan Stonecipher
Microsoft Sql Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:e0eCIEhCFHA.3416@.TK2MSFTNGP09.phx.gbl...
> Do you have Auto-Close turned on for those clients?
> --
> Andrew J. Kelly SQL MVP
>
> "JaneDoe" <JaneDoe@.discussions.microsoft.com> wrote in message
> news:10F5B1D4-84F8-4F1B-B419-26E82CEE8710@.microsoft.com...
>

perform calculation for only desired rows

hi all, i wasnt quite sure where to look to answer my particular problem so i am posting up this thread in the hopes that someone can point me in the right direction. in my report, i am showing sales figures for an area. i added a table to display sales from this year, sales from last year, and then the comparable percentage('this' divided by 'last' and then minus one). to account for some ppl who didnt have sales last year, i was able to use an IIF expression to return "N/A" in the textbox. my problem is i want the compared percentage to show for the area total, but not including ppl who were an 'N/A'. i am assuming that some sort of expression will be needed for the area total row, i.e. i want the area total to sum up this year and last year and then get the percentage, but i need to filter out the individuals who didnt have data for last year.

as an example

nsty nsly percentage

total: 15 7 X

A: 5 4 25%

B: 5 3 66%

C: 5 0 N/A

the percentage for total, X, should be (10/7)-1, with p0 as the format; right now it is summing rows A, B, and C.. how can i exclude row C from the calculation?

am i on the right track by researching filters and expressions, or is this a matter for the query

thanks in advance for any help

Hi

An expression will be able to handle this fine.
Use the IIF function to only include nsty if nsly is greater than zero.
For your total % sum use something like this:

= (Sum(IIF(Fields!nsly.value > 0,Fields!nsty.value,0))/Sum(Fields!nsly.Value)) -1

Hope this helps

Cheers
Mark
|||

thanks for the response; i think i still need to expand on the expression a little bit but at least i know how to go about it now.