Performance Hits within EM After Upgrading OS to Windows 2003

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!To answer my own question, in case others have experienced this, there was a
database with the "AutoClose" option checked. This was causing the
performance hit in Enterprise Manager after we upgraded to 2003. For
whatever reason, this did not cause problems on a 2000 OS, but definitely
caused issues on 2003 OS.
Resolution, just went into properties for the database and unchecked it.
This increased performance back to its original state. We now have a script
that checks and ensures database properties are set correctly.
Hope this saves someone else out there the headaches we went through trying
to determine the performance issues after upgrading to Windows 2003.
"JaneDoe" wrote:
> 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!

Performance Hits within EM After Upgrading OS to Windows 2003

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 other
s
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!To answer my own question, in case others have experienced this, there was a
database with the "AutoClose" option checked. This was causing the
performance hit in Enterprise Manager after we upgraded to 2003. For
whatever reason, this did not cause problems on a 2000 OS, but definitely
caused issues on 2003 OS.
Resolution, just went into properties for the database and unchecked it.
This increased performance back to its original state. We now have a script
that checks and ensures database properties are set correctly.
Hope this saves someone else out there the headaches we went through trying
to determine the performance issues after upgrading to Windows 2003.
"JaneDoe" wrote:

> We have recently upgraded 4 SQL servers to Windows 2003, running SQL Serve
r
> 2000. Many people are complaining that Enterprise Manager now takes 4-20
> seconds to expand the databases on those servers, where before it was eith
er
> instant or 1-2 seconds. Once the intial connection has been made, there a
re
> no further performance issues. Do you know if there is a fix, or have oth
ers
> 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!sql

Performance Hits within EM After Upgrading OS to Windows 2003

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!
To answer my own question, in case others have experienced this, there was a
database with the "AutoClose" option checked. This was causing the
performance hit in Enterprise Manager after we upgraded to 2003. For
whatever reason, this did not cause problems on a 2000 OS, but definitely
caused issues on 2003 OS.
Resolution, just went into properties for the database and unchecked it.
This increased performance back to its original state. We now have a script
that checks and ensures database properties are set correctly.
Hope this saves someone else out there the headaches we went through trying
to determine the performance issues after upgrading to Windows 2003.
"JaneDoe" wrote:

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

Performance Hit after upgrading to SQL 2005

I am hit with bad performance after upgrading my sql 2000 to sql 2005 version. All the infrastructure remains expect the performance :(

Please provide any pointers /guidelines so that I will check the same.

Thanks in Advance

Varun

I am seeing a huge performance problem right now.

I have to tagged to the TempDB right now. It seems to be leaving leftover objects and making the TempDB a mess. Check yours for a bunch of "#" + 8 digit Hex

If I stop and restart my SQL Services, which redoes the TempDB....I am good to go for a day or 2...then the same old problem. I have called MS about this but I am waiting for response from them

select * from tempdb.dbo.sysobjects where name like '#%' order by crdate desc

|||

What is the problem with performance, is it a particular query?

Have you rebuilt your indexes? This is a must do excercise after upgrading to SQL 2005.

|||

I had the same problem -- we got a new server to run sql server 2005. It was a major upgrade over the old server. When we started testing our code on the new machine, we found that a significant amount of our code ran a lot slower on the new machine with sql 2005.

Eventually we were able to change our code, mostly through trial and error, to get the performance we expected. In almost every case, the performance problems were caused by:

1. Sub-queries - apparently the sql 2005 optimizer isn't as good as the sql 2000 optimizer at figuring out when the sub-query needs to be re-evaluated -- so large recordsets that join to a sub-query can be really slow since the sub-query sometimes gets re-evaluated for each record it's being joined to even when the sub-query generates the exact same result for each parent record.

2. Table variables - when we substituted #temp tables for table variables, performance increased dramatically -- in some cases putting a primary key on the table variable fixed the problem.

Unfortunately, we had to spend a couple of weeks to change all of our code, but eventually we were able to fix the performance problems.

Hope that helps.