Speed Issues After SQL Upgrade

[Ben]

We recently upgraded our MS SQL Server 2000 to 2005.

Here is what we did:

1. Perform backup of the database from the old server.
2. Created a blank database in the new server.
3. Restored the database into the blank database located in the new server.

After this process, there was an obvious slowness in the MS Access
application. Program-wise, I did not do any code changes. The only thing
that we did was just this upgrade.

I am leaning towards the netwok causing the slowness. But I do not know the
issues that might slow down the application just because we upgraded the
same database into SQL 2005.

[Ben]

The hardware configuration below:

- Has two Intel Quad-Core Xeon processors
- 10GB of RAM
- 15,000RPM SATA hard drives

The old server was just a regular P4, 4G RAM.
"Ben" <pi******@sbcglobal.netwrote in message
news:FU****************@newssvr17.news.prodigy.net ...

We recently upgraded our MS SQL Server 2000 to 2005.

Here is what we did:

1. Perform backup of the database from the old server.
2. Created a blank database in the new server.
3. Restored the database into the blank database located in the new
server.

After this process, there was an obvious slowness in the MS Access
application. Program-wise, I did not do any code changes. The only thing
that we did was just this upgrade.

I am leaning towards the netwok causing the slowness. But I do not know
the issues that might slow down the application just because we upgraded
the same database into SQL 2005.

[Greg D. Moore \(Strider\)]

"Ben" <pi******@sbcglobal.netwrote in message
news:T%*****************@newssvr17.news.prodigy.ne t...

The hardware configuration below:

- Has two Intel Quad-Core Xeon processors
- 10GB of RAM
- 15,000RPM SATA hard drives

The old server was just a regular P4, 4G RAM.

Did you update stats on the restored DB?

If not try that. The query optimizer may be making bad choices based on
information gleaned from the old hardware.
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

[Erland Sommarskog]

Ben (pi******@sbcglobal.net) writes:

We recently upgraded our MS SQL Server 2000 to 2005.

Here is what we did:

1. Perform backup of the database from the old server.
2. Created a blank database in the new server.
3. Restored the database into the blank database located in the new
server.

After this process, there was an obvious slowness in the MS Access
application. Program-wise, I did not do any code changes. The only thing
that we did was just this upgrade.

I am leaning towards the netwok causing the slowness. But I do not know
the issues that might slow down the application just because we upgraded
the same database into SQL 2005.

Stastistics were invalidated when you upgraded to SQL 2005, so you need to
run sp_updatstats.

If that does not help, you need to analyse more closely what is slow. It's
possible that the optimizer makes a different decision for some queries to
the worse.

Also, I would recommend that you try running the database in
compatibility level 90. If you restored the database and did nothing
more, it is in level 80. This is not likely to affect performance, but
it may prevent you from using new features in SQL 2005 later on. On
the other hand, there may be compatibility issues causing your queries
to fail in level 90. You can switch back to 80 if this happens.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Ben]

The sp_updatestats did the trick. Now the speed as it should be.

Thanks.
"Erland Sommarskog" <es****@sommarskog.sewrote in message
news:Xn*********************@127.0.0.1...

Ben (pi******@sbcglobal.net) writes:

>We recently upgraded our MS SQL Server 2000 to 2005.

Here is what we did:

1. Perform backup of the database from the old server.
2. Created a blank database in the new server.
3. Restored the database into the blank database located in the new
server.

After this process, there was an obvious slowness in the MS Access
application. Program-wise, I did not do any code changes. The only
thing
that we did was just this upgrade.

I am leaning towards the netwok causing the slowness. But I do not know
the issues that might slow down the application just because we upgraded
the same database into SQL 2005.

Stastistics were invalidated when you upgraded to SQL 2005, so you need to
run sp_updatstats.

If that does not help, you need to analyse more closely what is slow. It's
possible that the optimizer makes a different decision for some queries to
the worse.

Also, I would recommend that you try running the database in
compatibility level 90. If you restored the database and did nothing
more, it is in level 80. This is not likely to affect performance, but
it may prevent you from using new features in SQL 2005 later on. On
the other hand, there may be compatibility issues causing your queries
to fail in level 90. You can switch back to 80 if this happens.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Ed Murphy]

Side note: When posting the same thing to multiple newsgroups,
please crosspost (to all of them in a single step) rather than
multi-posting (to each of them individually). I responded to
this in another group before reading in this one that you'd
already resolved the issue.