Our SQL queries sometimes take too long. We have a Windows Server 2003/SQL Server 2005 setup. We have already created indexes everywhere that's possible. Can we get quicker disks? Any ideas from anyone?
Sql-server – How to speed up SQL Server queries on Amazon EC2
amazon ec2database-performancesql serversql-server-2005windows-server-2003
Best Answer
The current line of thinking (as of July 2011) is that the best performance on EC2 is by RAIDing the ephemeral disks (the larger your instance, the more there are of them). You had better be sure you have backups if you choose to do this, as any kind of host failure means your data can and will disappear.
As Tom suggests, you can RAID EBS volumes: the performance isn't as good as you have to go out to the network, but you will decrease your seek as you increase the number of spindles that can seek in parallel. Here are some good benchmarks.
There are other things to factor in, like sizes of instance likely to get you the least contention, and sizes of EBS volume which maximise your performance. Be sure to "warm up" your volumes before using them also, as there is a first-write penalty on ephemeral disks and a first-read penalty on EBS.
Finally, you don't get much more of a SQL server expert than Brent Ozar, so read and understand his post on the topic. It led to a conversation on the AWS forum which talks about the relative performance metrics you can optimize for.