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 |
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
 Posted - 2014-05-14 : 19:12:17
|
| How many people are running their data files on SSDs? Curious about the performance gains. I'm not overly concerned about the DRP implications as we have a pretty robust backup/failover setup. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-05-14 : 19:23:00
|
| We are. Performance gains were massive. Because the database was no longer a bottleneck, it uncovered application bottlenecks. Oops!Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
 |
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2014-05-14 : 20:48:52
|
quote:
Originally posted by tkizer
We are. Performance gains were massive. Because the database was no longer a bottleneck, it uncovered application bottlenecks. Oops!Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Tara, were you seeing high IO waits prior to moving to SSDs? Most of my waits seem to be CPU/memory so I'm not optimistic about performance gains.This is what we are in the process of moving to (AWS)...i2.8xlarge 32 vCPU 244 Gigs RAM 8 x 800 Gig SSD |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2014-05-14 : 23:13:10
|
| We've been running our databases on FusionIO for about 3 years. Huge improvement, and like Tara's situation it revealed other issues in CPU, memory and network. We use SIOS Datakeeper software to do block replication between cluster nodes, so there is no traditional SAN or shared storage, but it fails over as if there was.We also do synchronous database mirroring to a DR site, so we have double redundancy. Even with all this overhead, we've absorbed a 600% increase in volume over 3 years on the same hardware and are still barely utilizing half of the server's resources. We're running out of disk space on the FusionIO cards (640 GB) rather than anything else.So while SSDs will help you a lot, your other resources will become the bottleneck. I have a friend and former coworker who has dealt with AWS before and regrets it, he had lots of problems that were never really solved. I don't know the details but he doesn't recommend it for certain SQL Server work, YMMV. |
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2014-05-15 : 00:18:19
|
quote:
Originally posted by robvolk
We've been running our databases on FusionIO for about 3 years. Huge improvement, and like Tara's situation it revealed other issues in CPU, memory and network. We use SIOS Datakeeper software to do block replication between cluster nodes, so there is no traditional SAN or shared storage, but it fails over as if there was.We also do synchronous database mirroring to a DR site, so we have double redundancy. Even with all this overhead, we've absorbed a 600% increase in volume over 3 years on the same hardware and are still barely utilizing half of the server's resources. We're running out of disk space on the FusionIO cards (640 GB) rather than anything else.So while SSDs will help you a lot, your other resources will become the bottleneck. I have a friend and former coworker who has dealt with AWS before and regrets it, he had lots of problems that were never really solved. I don't know the details but he doesn't recommend it for certain SQL Server work, YMMV.
|
|
|
|
ferrethouse
Constraint Violating Yak Guru
352 Posts |
Posted - 2014-05-15 : 00:19:44
|
| We've been on AWS for 2 years and love it. I suspect your friend was on it before PIOPS EBS volumes were available and IO was a challenge. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-05-15 : 09:59:21
|
quote:
Originally posted by ferrethouse
quote:
Originally posted by tkizer
We are. Performance gains were massive. Because the database was no longer a bottleneck, it uncovered application bottlenecks. Oops!Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/
Tara, were you seeing high IO waits prior to moving to SSDs? Most of my waits seem to be CPU/memory so I'm not optimistic about performance gains.This is what we are in the process of moving to (AWS)...i2.8xlarge 32 vCPU 244 Gigs RAM 8 x 800 Gig SSD
No our I/O was fine. With this upgrade, we upgraded the entire server though. But PerfMon is showing a dramatic increase in I/O performance, from 12ms or less to 1-2ms or less. Usually it's so fast it says 0ms.The one system we upgraded to Fusion-io, it had a dedicated SAN with some SSDs in that array. Then we switched to Fusion-io and BOOM, performance increased dramatically.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|
|
Solid State Drives