Put non-clustered indexes onto on a different drive?

I'm currently having an IO problem on 2 of my 10 drives attached to my SQL Server. We recently shifted away from using ephemeral drives as data drives due to the obvious DRP implications. But EBS volumes (attached storage) does not perform nearly as well as the ephermeral drives. I'm thinking of spinning up a couple more EBS volumes and gradually migrated many of the heavily used non-clustered indexes over to a different fileground. Or would a better strategy be to move some of the tables over (both clustered and non-clustered indexes)? The reason I prefer the former strategy is because I can do it more seamlessly without incurring downtime since I can build the non-clustered indices online.


AWS, right? What is the nature of the IO problem? Why do you think shifting the SQL drives will fix it?

Yeah AWS. We used PAL to analyse the performance of the drives and most of them are fine. Two of them, however, have an average disk seconds / read of over 30ms and regularly go into the >100ms range (maxing out at 1200ms briefly). We are seeing degraded performance for the clients located on those drives. For one of the drives the solution is simple. It has 9 databases on it. I simply need to move some of those databases onto a different EBS volume. But for the other drives it is more complicated because it only has one huge database on it (1.4 TB). So the only way to improve the read latency on that drive is to start using file groups and moving some of the data/indices onto a second disk. I guess the other option would be to RAID a couple of EBS volumes.

the secs/read stat bothers me. Could there be an issue with the actual disk or path to it? That is, unless you're already sure that its the combination of index lookup + row read that's the culprit, but then maybe you only need to adjust some covering indexes?