SQL 2005 Backwards Compatibility - Performance Issues

We had a hardware failure on one DB Server, and had to restore SQL 2000 DBs to a DB Server with SQL 2005.  We changed Compatibility Mode to 80.  Users are functioning, but are reporting slowness and time-outs.  The machine we restored to has a faster processor and double the RAM.  Neither are experiencing average utilization above 50%.  In fact, we see no hardware issues in any of the machines involved in the path from Web Server to App Server to DB Server to SAN.  Users on other DB Servers are not experiencing problems, but none of those have utilized backwards compatibility.

Are there any known significant performance issues relating to utilizing backwards compatibility????  And/Or, any suggestions on where to look further to isolate the true problem, and to fix it?
jimmckenzieAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Aneesh RetnakaranDatabase AdministratorCommented:
did you execute these after the restration

DBCC UPDATEUSAGE ()
DBCC CHECKDB()
ALTER DATABASE <dbName> SET PAGE_VERIFY CHECKSUM
EXEC sp_UpdateStats
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jimmckenzieAuthor Commented:
We had not run these.  We just ran them in test, and since we clearly see no harm in these, we are running them in production.  While we see no harm in them, what is it about these that would "fix" the problem?  We are not doubting you, but rather, we are just trying to clarify our understanding.  Thanks.
0
Jim P.Commented:
After a restore of the database, especially a different/new server, until these maintenance type tasks are run, statistics and detailed database consistency checks aren't really performed.

The DBCC CHECKDB command needs to have the database in single-user mode, so that will probably have to be run out of hours.

Note that even with the databases in Compatibility mode for 80, you can't just detach and re-attach the databases to a SQL2K box.  The only way to take them back is by using DTS to import them back into an empty database.
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

jimmckenzieAuthor Commented:
Additional information:  SQL Server is not logging messages to its log file, as in messages like those referring to starting or stopping of the service.  Again, this is isolated only to the SQL 2005 DB Server into which SQL 2000 DBs were restored.  Our other DB Servers (both 2000 and 2005) are functioning just fine.

To the specific comments by "jimpen", we did create empty databases, but we did not import using DTS.  Instead, we restored from SQL 2000 BAK files.  Would there be any issues with the approach we took?
0
Jim P.Commented:
I know it is a click or checkbox in SQL somewhere to get those events logged. I just can't remember where. I hit it when I'm configuring SQL right after an install. I'll look for it Monday.

The other possibility is that your errorlog directory isn't there. Go to the SQL Server Configuration Manager. Right click MSSQLSERVER --> Properties --> Advanced tab and in the startup parameters it should have something like:
-dD:\DATA\MSSQL.1\MSSQL\DATA\master.mdf; -eD:\DATA\MSSQL.1\MSSQL\LOG\ERRORLOG; -lD:\DATA\MSSQL.1\MSSQL\DATA\mastlog.ldf
as one long line. Fine the -e and make sure that path down to the word "ERRORLOG" exists.

>> Would there be any issues with the approach we took?

No issues with how you did it. Just there is no easy way to go back to SQL2K if you were wanting to do that. I ran into the issue shortly after upgrading our first SQL server to 2K5. I couldn't take a database back to SQL2k after 2K5 touched it. Not a big problem if you aren't reverting.


0
LowfatspreadCommented:
Hello jimmckenzie,

Migrating you database shouldn't be a spur of the moment decision , even in compatability mode you aren't guaranteed that the system will continue to function as previously...

you do need consider re-organising / re-indexing  a database whenever it is moved.
the restoration process of restoring the 2000 backups onto a 2005 system HAS upgraded you DATABASE to 2005 structures even though you're running in 2000 mode.

there can be problems "slow response etc"  for legacy applications attaching to sql 2005 systems perhapos your base paroblems lie there...

you should run the upgrade advisor on a copy of the 2000 database ASAP to see if there are any obvious issues with your system which should have been addressed prior to migration.

a better choice would probably have been to install a sql 2000 instance on one of your servers and restored to that.

as previously stated there is no immedaite way back for your database now that it a 2005 one apart from unloading all the data and reloading to a fresh / empty sql 2000 version.


good luck
 



Regards,

Lowfatspread
0
jimmckenzieAuthor Commented:
We still have performance issues, but those appear to be inherent in that which me upgraded using the Compatibility Mode.  We will not see those issues go away until we modify our physical code.  However, the maintenance steps provided did help.
0
Jim P.Commented:
Glad to be of assistance. May all your days get brighter and brighter.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.