Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sql_er
Constraint Violating Yak Guru
267 Posts |
 Posted - 2010-01-29 : 15:15:07
|
| 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
22859 Posts |
Posted - 2010-01-30 : 01:51:51
|
| 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 |
 |
|
|
sql_er
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-02-01 : 10:33:33
|
| 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! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-01 : 10:48:18
|
| "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?) |
|
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2010-02-01 : 14:40:08
|
| 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. |
|
|
|
|
|
|
|
|
Moving database slowed it down a lot