Timeline for MS SQL Server 2016 Oprimal Disk Configuration
Current License: CC BY-SA 3.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Aug 12, 2016 at 6:50 | vote | accept | davey | ||
| Aug 11, 2016 at 14:18 | comment | added | davey | Appreciate all the feedback its awesome. I thought general consensus is to put temp DB on its own disk(s)? If its in RAID 1 and with mirroring I think its enough cover in case of failure. I think the Temp DB growth was probably due to some data extraction/report that happened ages ago. I've included a graph in original question generated from the data I've captured of its growth for past 6 months. I'm leaning towards shared LOG and DATA and separate TEMP DB..Hmmm also thinking of doing 6 SSD drives in RAID 10 ... this is so difficult to choose | |
| Aug 11, 2016 at 12:56 | comment | added | David Spillett | 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. | |
| Aug 11, 2016 at 12:48 | comment | added | David Spillett | 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. | |
| Aug 11, 2016 at 12:43 | history | edited | David Spillett | CC BY-SA 3.0 |
added 64 characters in body
|
| Aug 11, 2016 at 12:18 | comment | added | davey | 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 ? | |
| Aug 11, 2016 at 12:17 | comment | added | davey | Will ensure infrastructure guys have a disk on standby. | |
| Aug 11, 2016 at 12:11 | comment | added | davey | 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. | |
| Aug 11, 2016 at 11:40 | history | answered | David Spillett | CC BY-SA 3.0 |