Posted by Erland Sommarskog on 10/13/07 15:11

Ben (pillars4@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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]