SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Moving database slowed it down a lot
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 01/29/2010 :  15:15:07  Show Profile  Reply with Quote
Hello,

We have recently moved a database to Amazon EC2 instance. We chose the server with identical configurations. The performance on the new server is awful. After initial analysis I realized that the old one used much more memory through AWE enable option, and so I reset memory on the new server the same way. However, it does not seem to have helped the problem.

The only idea I have is to rebuild indexes. The only problem is that db size is pretty big (~ 80 GB) so it might take a while. So before doing that I wanted to get some opinions as to whether I am on the right track or there is something else to check.

Thank you!

Kristen
Test

United Kingdom
22403 Posts

Posted - 01/30/2010 :  01:51:51  Show Profile  Reply with Quote
If you transfered it via Backup and Restore of the database (I don't know how one creates a database on Amazon EC2) then the indexes would be identical after Restore.

If you transfered the database by create database, then created empty tables, then transfer data then the idnexes might be fragmented.

Either way, you should rebuild indexes regularly.

Also update statistics.

I think it would be worth looking at the Query Plan for a query plan that is slow and comparing the Plan on Old and New databases and seeing what is different. If the plans are the same most likely it is a hardware / bandwidth type problem
Go to Top of Page

sql_er
Constraint Violating Yak Guru

267 Posts

Posted - 02/01/2010 :  10:33:33  Show Profile  Reply with Quote
The transfer was via backup and restore.

We do index defragmentation daily and update statistics (Opitimization SQL Maintenance Job). We can't rebuild indexes as DB needs to be online 24/7. I looked at the execution plans and they are identical - that is why I was surprised ...

I found out that our old machine had 8 CPUs and the new one only has 4. Could this be the cause of such a drastic slow down?

Thank you!
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 02/01/2010 :  10:48:18  Show Profile  Reply with Quote
"Could this be the cause of such a drastic slow down?"

I suppose so. But not if the 4 CPUs are twice as fast as the old ones.

"update statistics (Opitimization SQL Maintenance Job)."

Note that if one uses WITH FULLSCAN and the other uses the, default, Sample then that could make a significant difference (although I think the query plans would be different if that was the case)

In summary: I think if the query plans are the same it looks like "less hardware" on the new machine (or the hardware is shared with other APPs, rather than dedicated to you, perhaps?)
Go to Top of Page

tripodal
Constraint Violating Yak Guru

259 Posts

Posted - 02/01/2010 :  14:40:08  Show Profile  Click to see tripodal's MSN Messenger address  Reply with Quote
Was the previous sql server local to your environment, and now putting it on EC2 that would make it remote?

That is definatly a major source of slowdown, depending on the types of usage you use.

The tcp turnaround time on a local network would be 100-1000 times faster than a hosted server.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000