1

New server build for SQL SERVER STANDARD 2016 install. Total of 12 disks: 6 standard 15K HDs and 6 SSDs. This is the disk setup I am going for. Could this setup be improved on, if so how what would change?

SQL SERVER DISK SETUP

I could move TEMP DB mdf to DATA Drive and have TEMP DB log with other DB Logs on SSD LOG drive.

  • This will be for a SQL Server Production environment only.

  • Optimizing for speed but also HA to an extent.

  • Backups will get copied to an offsite location throughout the day.

  • Server will be mirrored not AG. Although mirroring is deprecated, due to install being Standard Edition I don't see benefits of AG at this point with 2 servers.

  • Biggest waits are LATCH_EX followed by CX_PACKET

Also looking to replicate some data for reporting purposes.

LOG average file size is 9.16 MB over past 6 months, Log backup every 15 mins MAX size has been 389.47MB in past 6 months

TEMP DB info for past 6 months

enter image description here

Improve this question
10
  • Looks pretty good. What is the latency of this configuration for the tempdb / log drive? This is where your main I/O bottleneck will be, since logs are sequential writes and tempdb will have random writes.
    – Randolph West
    Commented Aug 11, 2016 at 7:54
  • Don't know the latency as i haven't built it yet :), current setup is RAID 5 for everthing across 4 disks and we get average read latency of 15ms write of 35 ms. I was thinking I could move TEMP DB mdf files (6 of them) to the DATA Drive and have TEMP DB logs with other DB Logs on SSD LOG drive? Probably do the same for the other system DBs?
    – davey
    Commented Aug 11, 2016 at 7:57
  • 1
    Yeah, like I said it's pretty good. Anything will be better than RAID 5 for writes. Your target latency is 10-15ms for writes on log (preferably better if you're sharing). Put tempdb on the fastest disks you have, and do the same with logs. It's what you have here, pretty much.
    – Randolph West
    Commented Aug 11, 2016 at 8:00
  • 1
    To allow for better answers from others here are some questions: Is this server PROD only or does it need to do other workloads (DEV, TEST, QA, or non-SQL)? Are you optimizing for speed, or for availability? Will the backups get moved off of the server disks (RAID is not a backup). Do you have spare HDDs and SSDs or are you crossing fingers and hoping for the vendor to pull through in a moment of need? Do you know if your application usage is temp data, temp log, user data, or user log heavy? Is the server in an AG?
    – Cody Konior
    Commented Aug 11, 2016 at 9:53
  • added some more info to the question, thanks
    – davey
    Commented Aug 11, 2016 at 10:51

1 Answer 1

Reset to default
2

Looks fine to me.

Because of the way that SQL Server schedules writes, keeping logs and data on separate volumes doesn't save as save as much performance as many think for many workloads (it does have a large effect on some workloads though, so YMMV), and I expect this will be less still on SSDs (as you don't have the latency of head movements flipping between the log and data files to worry about) so if you expect a lot of tempdb activity you might be better off keeping the logs with the data files and leaving tempdb on its own to keep the IO bandwidth of that array to itself.

Of course this is very dependant on actual application workloads so my gut thought above could be completely wrong for your DB(s), so if possible benchmark before committing to anything in production.

Also Cody's comment regarding spare drives is critical: make sure there are some on-site or very nearby. From a HA standpoint mirroring will protect you to an extent should a whole array fail (without mirroring take "or nearby" from the above and make sure there are definitely spares on-site and ready to go) but I'd still feel safer knowing an array can start rebuilding ASAP after a single drive failure to reduce the chance of a dual drive failure. Also check with your infrastructure provider to make sure the machine supports hot-swap (or if not set your recovery plans accordingly to take into account entirely stopping one mirror temporarily while a replacement is installed).

Improve this answer
6
  • I have read as Randolph mentions that logs are sequential and data is random so best to keep them separate but as these are SSD like you say is it necessary?Also read to seperate them out in case of data drive failure you have the Log file. Currently temp DB isn't that big 3GB with 99% available. Main DB is 110GB. Maybe I should put logs together with data including system DBs etc and chuck tempDB on its own SSD.
    – davey
    Commented Aug 11, 2016 at 12:11
  • Will ensure infrastructure guys have a disk on standby.
    – davey
    Commented Aug 11, 2016 at 12:17
  • Maybe I should split data out. rather than RAID 10 have it in RAID 1. So have TEMP DB RAID 1, DATA RAID 1, LOG RAID 1 ?
    – davey
    Commented Aug 11, 2016 at 12:18
  • If you put TempDB on its own single disk remember that this becomes a single point of failure. That TempDB is very large and currently empty doesn't always mean much: you could have regular processes that churn it considerably so that up-to-3Gb sees a lot of activity. I suggest you watch that activity over a period to gauge its size and burstiness. If it turns out you barely use TempDB apart from in large overnight reports it may not be an issue needing a lot of thought at all even the sharing od IO bandwidth with other files.
    – David Spillett
    Commented Aug 11, 2016 at 12:48
  • Regarding R1 for data and R1 for logs: you might hit the compromise point of wasting storage here as you may end up with full data volume and a nearly empty log volume with no easy way to use the spare space to let the data grow. Note that IO activity on the logs is only going to be during writes - if your application is very read heavy then this may not pose much IO load so the extra bandwidth of R10 for the data reads will be more beneficial than separating the log/data IO payloads. Again, monitoring IO activity during normal operation is going to be a useful way of gaining insight.
    – David Spillett
    Commented Aug 11, 2016 at 12:56

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