2

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?

crb
  • 7,998
  • 1
  • 38
  • 53
Jorge Guzman
  • 337
  • 2
  • 4
  • 14

3 Answers3

2

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.

crb
  • 7,998
  • 1
  • 38
  • 53
  • Ouch RAIDing EBS drives can get very expensive for us, so we might go for ephemeral disks. Maybe we'll have to have one database for reads and a separate for writes but I still don't think that would be ideal. Only other idea I can think of is separating the DB file into multiple EBS drives. Would that help much? – Jorge Guzman Jul 12 '11 at 17:18
  • EC2 allows you the flexibility to quickly test this kind of thing based on your exact workload. I would use that option if I were you! :) – crb Jul 12 '11 at 18:52
1

Take a whole bunch of EBS volumes, and bundle them together into a RAID10 array. Then put your data on that array, and theoretically you should get better performance.

Works on Linux. I've no idea how you'd do it on windows, but that's the theory.

Tom O'Connor
  • 27,480
  • 10
  • 73
  • 148
1

I recommend, as with any performance problem, to measure and identify your bottleneck. Use a well proven methodology, like Waits and Queues. Only after you identified the cause of the problem can you propose and test solutions. Seems so obvious yet so few do it...

Raiding your EBS or ephemeral volumes is a solution if and only if the problem is identified to be caused by high IO that cannot be addresses any other way. Since you deploy on EC2 your solution should be designed to fit in an EC2 instance w/o buffer pool spilling (w/o needing to read from disk) and the only IO should be initial warm up, updates flush at checkpoint and, of course, log flush. If you find yourself that you need more IO than this then your best approach is to analyze the database structure and application code and have them fit into your target host, you'll get a lot more mileage out of that than anything else you can do. What does that mean exactly is, again, completely dependent on your application and your investigation results.

Remus Rusanu
  • 8,283
  • 1
  • 21
  • 23