Speed Issues After SQL Upgrade

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.

Re: Speed Issues After SQL Upgrade

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" wrote in message
news:FURPi.1671$LD2.266@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.
>

Re: Speed Issues After SQL Upgrade

"Ben" wrote in message
news:T%RPi.1672$LD2.1355@newssvr17.news.prodigy.net...
> 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

Re: Speed Issues After SQL Upgrade

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Speed Issues After SQL Upgrade

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

Thanks.


"Erland Sommarskog" wrote in message
news:Xns99C8AFB6246FYazorman@127.0.0.1...
> 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/downlo ads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Speed Issues After SQL Upgrade

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.