1

I have an EC2 instance (r5a.4xlarge) which should be able to do 2Gbps+ in EBS access. This machine has a 7TB mounted gp2 volume, with 16,000 IOPS, which is mounted and contains a SQL Server database on the H: drive. The database is about 5TB in size.

When running SQL Server commands (specifically, reading from the drive and writing to another drive), and monitoring the the volume throughput, on this volume / drive, commands are taking an incredibly long time to process and SQL Server is showing a PAGEIOLATCH_SH lock, with 34ms response times (other volumes are 1ms or 2ms).

Microsoft's literature says this is a disk subsystem issue. We do not have this problem with other drives / volumes on this machine.

What needs to happen to get this from 34ms to < 2ms, like we are on the other drives? Something we're not configuring properly?

Improve this question
4
  • Are any of the other volumes you're comparing smaller than 1TB? The General purpose SSD (gp2) volumes section of the doc states that "GP2 volumes smaller than 1 TB can also burst up to 3,000 IOPS.". Also, could it be that you're using Amazon EBS-Optimized instances with the other instances and not for this one?
    – Ronaldo
    Oct 24, 2020 at 10:15
  • @Ronaldo All of our instances are the same, and they are all EBS-Optimized instances. We have tested with both < 1TB and also > 1TB, and also RAID-0 instances. I've tried benchmarking them (outside of SQL Server), and the volumes appear speedy. However, once SQL Server is spun up, we start seeing these latches.
    – user113242
    Oct 25, 2020 at 15:07
  • You may need to increase RAM, if data significantly outpaces memory, or if you have many queries borrowing from the buffer pool: Compensating For Disk Waits
    – Erik Darling
    Oct 25, 2020 at 18:45
  • It turns out, that if a volume is created from a snapshot, then the volume is not initialized until that data is actually read completely from S3 to EBS. So, we had a lot of data being stored in S3, which appeared to be on EBS volumes. It wasn't a SQL Server issue at all.
    – user113242
    Oct 28, 2020 at 4:39

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.