1

I'm getting into the dba world for SQL Server via "trial by fire", and I have a IO question.

I have a SQL Server 2016 engine built upon Windows Server 2012. This setup includes:

  1. 128Gb Ram
  2. 6Gb/s Megaid SAS controller

enter image description here

  1. 4 x 600Gb 15KSAS drives in RAID 5 on a 2-backplane configuration with 6Gb/s connection:

enter image description here

  1. 2 x 1Gb Network Connection (Fiber I believe)

So here's my question:

  1. if everything is connected via 6Gb/s SAS connections, why is my IO only at the 5 to 6Mb/s speed resulting in a continuous PAGEIOLATCH_EX suspension? This is where I'm confused. This includes SQL Server Read/Write, and Operating System negotiation.

Any ideas on how to figure this one out would be greatly appreciated.

Thank you very much.

2

If you're using good ol' fashioned spinning rusty frisbees, also known as magnetic hard drives, this isn't an unusual number to see when you're doing random reads and writes. You can outpace those drives with a simple USB 3 flash drive.

To find out for sure, run the portable edition of CrystalDiskMark on your server during a maintenance window. Test the C drive, test where data is stored, where logs are stored, etc - and then compare it to your laptop's SSD. You might be surprised at how much slower random reads & writes are on this array of spinning rusty frisbees.

3
  • 1
    ok. so you are suggesting that 5-6Mb/s is average for a 5-6Gb/s connection with a 15K SAS setup? The problem I have with this, is that this is a new server, and the older one was running over 100Mb/s or better on reads alone using 7200 Brown spinning doughnuts. is there a configuration I could be missing? or messing up?
    – arcee123
    Jul 3 '17 at 13:36
  • Yeah, it depends on the type of access. If they're doing lots of tiny (think 4-8KB) random reads and writes, that's not unusual. You'll need to follow the instructions in my answer, then post those in your question. Thanks!
    – Brent Ozar
    Jul 3 '17 at 14:49
  • ok. I'm attempting to load something right now, and the rollback would take days, so when complete, I'll put it in the answer. Thank you very much!
    – arcee123
    Jul 3 '17 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.