inevercheckthis2002 (10/7/2013)
Steve Jones - SSC Editor (10/7/2013)
There are testing utilities, like SQLIO to measure raw performance.With fourteen 15k RPM SAS drives in a RAID 10 array, at a generous 200 IOP per disk, I should have (at most) 1400 write IOPS and 2800 read IOPS.
SQLIO reports 6000 - 8200 IOPS.
Isn't the raw performance dictated by the physical specs of the drives in the array? What does SQLIO bring to the table? How can I be getting the 6000 - 8200 IOPS it is reporting???
That's why I have my doubts about the value of SQLIO...
Steve Jones - SSC Editor (10/7/2013)
In terms of what your workload does, no one can tell you that. The workload on my disks doesn't apply to yours. That's one of the problems with database sizing.You can get a trace from your existing system and run that. Otherwise you'll have to make some guesses on reads v writes, sequential v random IO.
But my question was specific to whether I can gain an advantage by splitting off tempdb and/or logs from the data disks, given that I have 14 disks to work with. To me, the advantage is in aggregating the IO with one large array.
What advantage can be gained by segregating tempdb files and/or a log files on arrays with less IO capability?
I was curious for the SQLIO test was the test on random and sequential read/writes? Were they all same values or different in SQL IO?
In regards to your question splitting tempdb and/or logs from data disks.
It's considered best practice to split tempdb and logs. By combining temp and data disks there will be greater disk contention since tempdb is likely highly utilized. Like wise similar issue can arise combining log and data when the data is written to the log there can be disk contention. In addition from a database recovery standpoint, separating logs from Data provides a potential safety measure where if the data files are corrupted we can recover from a full back and the database logs w/assumption we are in full recovery and have appropriate logs available.
--------------------------------------------------
...0.05 points per day since registration... slowly crawl up to 1 pt per day hopefully 😀