By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,715 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,715 IT Pros & Developers. It's quick & easy.

Speed Issues After SQL Upgrade

P: n/a
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.
Oct 12 '07 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.

Oct 12 '07 #2

P: n/a


"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
Oct 13 '07 #3

P: n/a
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
Oct 13 '07 #4

P: n/a
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

Oct 13 '07 #5

P: n/a
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.
Oct 14 '07 #6

This discussion thread is closed

Replies have been disabled for this discussion.