Your thoughs on 2000 to 2005 upgrade issues

Question

Your thoughs on 2000 to 2005 upgrade issues

Butch Kenney

SSC Enthusiast

Points: 159
More actions
August 14, 2007 at 2:08 pm

#197343

We're having some perfomance issues with our line of business database. I recently upgraded our SQL 2000 to 2005. The next day the Access front end slowed to a crawl. I've been all over these forums and I've found quite a few gems and implemented almost all of them. Thanks!!
But now, my question is "all else being equal" ( same server, same Access front end, same network etc. ) will the SQL 2005 version of the database be more needy? After all the tweaks and settings I've changed, would simply throwing more RAM onto the server help? I know in general the answer will be yes, but I was just wondering if the differences between the two versions would require a more beefy server. I know MS has some pretty low entry requirements, but... the speed issue is really taking it's toll on me.
Thanks for all the help!
Butch Kenney

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 1

How did you go about your upgrade? What compatability level is the db current set at? Have you rebuilt indexes/reorganized the database since the upgrade?

Also where is your tempdb/how is it configured? SQL 2005 uses tempdb more extensively than SQL 2000...

Stefan K SSCommitted Points: 1786 More actions · Answer 2

Simply adding more RAM to your machine may not be useful. Is it a 32 or 64bit system? If it is a 32bit System one process (e.g. SQL Server DB Engine) could only use 2 Gigabytes of RAM or 3 with the 3 GB switch set in the boot.ini. Or you have to use AWE, which could cause other problems.

You have to do more manual update statistics if bulk inserting data to tables than in 2000.

Best regards,

Stefan

Kindest Regards,

SK

swjohnson Hall of Fame Points: 3254 More actions · Answer 3

Yes simply adding more RAM to the machine may help but that depends on your current settings and how much you do have and if the max server memory is configured or not. What are your settings and what version (32 or 64 bit) are you running. Also, have you re-indexed and updated statistics--that was a big performance boost for us after we upgraded from SQL 2000 to 2005 as the SQL engine uses them a bit differently.

SJ

Accepting all invites

Butch Kenney SSC Enthusiast Points: 159 More actions · Answer 4

Thanks all!

Ok, we're running 32bit, SQL Standard and the database is set to SQL 2005 ( 90 ).

I did all the re-build, re-organize, re-statisticized tricks as well.

Currently the 2003 Enterprise server has 4Gb of ram. I set SQL to use a minimum of 1024Mb and a max of 3072Mb.

Again, thanks for your replys!

Butch

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 5

Joe Clifford

SSCrazy Eights

Points: 8739

August 15, 2007 at 10:14 am

#727112

Tempdb? Where is that sitting?

Joe

Butch Kenney SSC Enthusiast Points: 159 More actions · Answer 6

It's on the same server in the MSSQL\Data folder. I have it set to back up every Sunday and it has ( along with all the system tables ) successfully for the last three weeks. Other than that, I haven't touched any of the system tables...is that bad?

Thanks for the help!

Butch

Butch Kenney SSC Enthusiast Points: 159 More actions · Answer 7

On thing I've noticed:

The Activity Monitor is showing that almost all the users are using my PC as the host and not their own PC...that can't be good. One user I can find that the host is their PC, but that's it. Everyone is set up the same though. All the PCs have their ODBC connections set the same ( obviously ). So, how is that 90% of the users are using my PC as the host?

We are on 2005 SP2 also.

Default Database:

I've setup all the users to use the production database as their default...that's the correct way, right? I wouldn't set their defaut to Master or Tempdb, would I?

Ok, I'm beat, heading home, my 10 hours are up!

Thanks!

Butch

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 8

Butch -

OK, now you've made me wonder... the only way I can think of that the users could be hitting your PC would be if the application is supposed to be multi-tier and somehow one of the tiers is sitting on your machine? Maybe a web service or?

Joe

Butch Kenney SSC Enthusiast Points: 159 More actions · Answer 9

That's what I was thinking, but there's no such beast. No multi-tier structure with it just being an Access 2003 front end linking to SQL 2005. I do development work on that computer, including creating the MDE file that gets copied down to the workstations, but that's it. I do all the SSMS work on the server itself using a remote connection from that PC.

I did set up Reporting services so IIS is installed on the server. I have even stopped the Reporting service in hopes that would speed the SQL server up!

Thanks for the help, Joe!

Butch

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 10

MDE, OK that helps a little bit let me fiddle with my copy of MS Access... is there any chance you are using a sql account to connect to the database from your workstation and that your username/password is getting saved within the MDE?

Do me a favor and give us the settings from your DSN.

MIJ SSCommitted Points: 1915 More actions · Answer 11

I'm not much of an Access programmer, but value displayed in the "Host" column in the Activity Monitor can be embedded in the connection string/object and not actually match the host name of the client connecting to the db.

I've seen processes with host values for computers that I know no longer exist, and it hasn't had any effect on the client application. So that might not be anything to worry about, and is easy enough to check.