Speed Issues After Upgrading from SQL 2000 to 2005

[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.

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.

The application is running on MS Access 2003.

 

[Larry Linson]

Even though your client application is Microsoft Access, the changes you
made seem all to be in the server and server database. I have not heard of
any particular general problems between Access 2003 and MS SQL Server 2005,
so suspect that you are going to have to pursue the performance issue in a
newsgroup devoted to MS SQL Server. It is not uncommon for new versions to
require more resources, or require different "tuning", than the versions
they replace. It seems, from what you say, that you have provided
more-than-adequate enhanced resources to run SQL Server 2005, so it is even
more likely that you need advice from SQL Server specialists on this issue.

Larry Linson
Microsoft Access MVP

 

[Guest]

Get new ODBC driver. Check your odbc driver.
need to specify native mode rather than tcpip or pipes.
use new syntax rather than specify tcpip/pipes library
dll's, which no longer are used by new driver.

(david)

 

[Ben]

David,


Could you please give more details on how and what?

Thanks,

Ben

 

[Rick Brandt]

David,


Could you please give more details on how and what?

Thanks,

While using the latest driver is always a good idea, I will toss into the
discussion that we recently changed one of our production SQL Servers to 2005
from 2000 and saw nothing like what you are experiencing. We did nothing
different at all to our Access front ends and saw no performance drop.

I suspect your problem lies elsewhere. If you want to experiment with drivers I
suggest building a test Access front end with some queries, processes, and
reports that are specifically appropriate to do performance testing with and see
if you can actually "measure" a difference when switching between drivers. That
way you are comparing apples to apples.

 

[Ben]

Sounds like a plan. I will try that. Thanks.

While using the latest driver is always a good idea, I will toss into the
discussion that we recently changed one of our production SQL Servers to
2005 from 2000 and saw nothing like what you are experiencing. We did
nothing different at all to our Access front ends and saw no performance
drop.

I suspect your problem lies elsewhere. If you want to experiment with
drivers I suggest building a test Access front end with some queries,
processes, and reports that are specifically appropriate to do performance
testing with and see if you can actually "measure" a difference when
switching between drivers. That way you are comparing apples to apples.

 

[Sylvain Lafontaine]

Try updating the statistics and freeing the procedure cache; if this doesn't
work than try reindexing:

UPDATE STATISTICS

Cleaning the caches:
DBCC FLUSHPROCINDB
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE

(See
http://sqlknowledge.com/index.php?option=com_content&task=view&id=65&Itemid=41 )
..

Reindexing:
http://blog.sqlauthority.com/2007/0...abase-tables-and-update-statistics-on-tables/

If you are making direct calls to stored procedures on the database with sql
passthrough queries, you might also have problem with bad execution plan
and/or parameter sniffing. See m.p.sqlserver.programming for more info on
that.