September 30, 2010 at 11:49 pm
Hi,
We ran SQLIO on our f:\ drive of windows server to benchmark the f:\ drive. But we are unable to analyse the results.How to analyse? Are there any standards ? Please help me on this?
Here are the results :
threads readorwrite durationseconds sectorsizekb iopattern iosoutstanding filesizemb ios_sec mbs_sec latencyms_min latencyms_avg latencyms_max
2 W 120 64 random 1 0 0.02 0 46145 66965 93648
64 W 120 64 random 128 13477 1692.75 105.79 140 4744 7722
64 W 120 64 sequential 128 13477 1728.91 108.05 150 4643 4926
64 R 120 64 random 128 13477 1762.03 110.12 63 4560 4941
64 R 120 64 sequential 128 13477 1778.77 111.17 137 4516 4744
64 W 120 64 sequential 64 13477 1685.76 105.36 79 2405 5385
32 W 120 64 random 128 13477 1697.21 106.07 134 2388 5576
64 R 120 64 random 64 13477 1719.66 107.47 68 2343 5500
32 W 120 64 sequential 128 13477 1729.14 108.07 126 2343 2501
64 R 120 64 sequential 64 13477 1733.31 108.33 73 2340 5370
64 W 120 64 random 64 13477 1740.65 108.79 76 2331 2526
32 R 120 64 random 128 13477 1760.43 110.02 32 2302 2622
32 R 120 64 sequential 128 13477 1777.81 111.11 124 2280 2415
32 W 120 64 sequential 64 13477 1686.01 105.37 73 1207 4198
64 W 120 64 random 32 13477 1695.46 105.96 36 1201 4179
16 W 120 64 random 128 13477 1716.46 107.27 66 1186 4173
32 R 120 64 random 64 13477 1720.03 107.5 63 1184 4294
64 W 120 64 sequential 32 13477 1730.19 108.13 34 1177 1324
32 R 120 64 sequential 64 13477 1729.75 108.1 21 1177 4203
16 W 120 64 sequential 128 13477 1730.44 108.15 59 1176 1332
32 W 120 64 random 64 13477 1760.71 110.04 69 1156 1301
64 R 120 64 random 32 13477 1761.85 110.11 34 1156 1327
16 R 120 64 random 128 13477 1763.87 110.24 121 1154 1340
64 R 120 64 sequential 32 13477 1777.48 111.09 31 1146 1277
16 R 120 64 sequential 128 13477 1781.96 111.37 63 1142 1223
16 W 120 64 sequential 64 13477 1687.57 105.47 34 604 3607
64 W 120 64 sequential 16 13477 1687.44 105.46 16 604 3605
8 W 120 64 random 128 13477 1708.69 106.79 13 597 3623
16 R 120 64 random 64 13477 1716.52 107.28 25 594 3740
64 R 120 64 random 16 13477 1717.11 107.31 13 594 3723
32 W 120 64 random 32 13477 1719.2 107.45 35 593 3674
8 W 120 64 sequential 128 13477 1728.8 108.05 15 590 742
32 W 120 64 sequential 32 13477 1728.25 108.01 32 590 727
16 R 120 64 sequential 64 13477 1729.88 108.11 29 589 3642
64 R 120 64 sequential 16 13477 1731.83 108.23 13 589 3652
64 W 120 64 random 16 13477 1735.59 108.47 17 588 736
16 W 120 64 random 64 13477 1760 110 31 579 720
32 R 120 64 random 32 13477 1762.17 110.13 31 579 754
8 R 120 64 random 128 13477 1764.88 110.3 123 578 748
8 R 120 64 sequential 128 13477 1780.87 111.3 14 573 700
32 R 120 64 sequential 32 13477 1781.88 111.36 31 572 699
64 W 120 64 random 8 13477 1688.65 105.54 8 302 3310
8 W 120 64 sequential 64 13477 1688.04 105.5 3 302 3307
32 W 120 64 sequential 16 13477 1686.86 105.42 2 302 3313
4 W 120 64 random 128 13477 1712.18 107.01 9 298 3305
16 W 120 64 random 32 13477 1714.84 107.17 14 297 3294
32 R 120 64 random 16 13477 1715.78 107.23 14 297 3433
8 R 120 64 random 64 13477 1723.96 107.74 6 296 3420
4 W 120 64 sequential 128 13477 1727.33 107.95 5 295 434
16 W 120 64 sequential 32 13477 1730.14 108.13 12 295 439
64 W 120 64 sequential 8 13477 1729.88 108.11 12 295 433
8 R 120 64 sequential 64 13477 1733.29 108.33 7 294 3389
32 R 120 64 sequential 16 13477 1731.1 108.19 13 294 3338
8 W 120 64 random 64 13477 1756.94 109.8 7 290 440
32 W 120 64 random 16 13477 1757.24 109.82 17 290 431
4 R 120 64 random 128 13477 1758.05 109.87 125 290 464
16 R 120 64 random 32 13477 1763.89 110.24 123 289 469
64 R 120 64 random 8 13477 1764.39 110.27 11 289 461
4 R 120 64 sequential 128 13477 1775.79 110.98 6 287 422
16 R 120 64 sequential 32 13477 1781.21 111.32 14 286 401
64 R 120 64 sequential 8 13477 1780.47 111.27 13 286 422
4 W 120 64 sequential 64 13477 1684.84 105.3 3 151 3163
16 W 120 64 sequential 16 13477 1687.94 105.49 6 151 3159
64 W 120 64 sequential 4 13477 1687.52 105.47 4 151 3169
2 W 120 64 random 128 13477 1696.54 106.03 1 150 3148
8 W 120 64 random 32 13477 1718.43 107.4 3 148 3158
32 W 120 64 random 8 13477 1716.07 107.25 7 148 3154
4 R 120 64 random 64 13477 1718.19 107.38 4 148 3267
16 R 120 64 random 16 13477 1721.9 107.61 6 148 3277
64 R 120 64 random 4 13477 1714.31 107.14 6 148 3266
2 W 120 64 sequential 128 13477 1728.25 108.01 3 147 287
8 W 120 64 sequential 32 13477 1728.85 108.05 3 147 284
32 W 120 64 sequential 8 13477 1728.88 108.05 6 147 291
4 R 120 64 sequential 64 13477 1732.56 108.28 4 147 3205
16 R 120 64 sequential 16 13477 1731.55 108.22 7 147 3284
64 R 120 64 sequential 4 13477 1730.24 108.14 6 147 3223
64 W 120 64 random 4 13477 1739.72 108.73 6 146 293
4 W 120 64 random 64 13477 1762.06 110.12 3 144 288
16 W 120 64 random 16 13477 1758.61 109.91 6 144 287
2 R 120 64 random 128 13477 1763.09 110.19 16 144 303
8 R 120 64 random 32 13477 1759.84 109.99 4 144 308
32 R 120 64 random 8 13477 1765.73 110.35 4 144 293
2 R 120 64 sequential 128 13477 1781.03 111.31 8 143 272
8 R 120 64 sequential 32 13477 1777.38 111.08 4 143 209
32 R 120 64 sequential 8 13477 1779.3 111.2 8 143 270
64 W 120 64 random 2 13477 1693.34 105.83 5 75 3092
2 W 120 64 sequential 64 13477 1686.32 105.39 1 75 3082
8 W 120 64 sequential 16 13477 1684.8 105.3 1 75 3095
32 W 120 64 sequential 4 13477 1686.72 105.42 3 75 3085
4 W 120 64 random 32 13477 1715.4 107.21 1 74 3078
16 W 120 64 random 8 13477 1715.13 107.19 3 74 3093
2 R 120 64 random 64 13477 1711.73 106.98 1 74 3152
2 R 120 64 sequential 64 13477 1702.73 106.42 4 74 3143
2 W 120 64 random 64 13477 1739.92 108.74 1 73 220
8 R 120 64 random 16 13477 1717.93 107.37 2 73 3163
32 R 120 64 random 4 13477 1717.81 107.36 2 73 3175
4 W 120 64 sequential 32 13477 1728.2 108.01 1 73 212
16 W 120 64 sequential 8 13477 1728.34 108.02 2 73 220
64 W 120 64 sequential 2 13477 1727.9 107.99 2 73 219
8 R 120 64 sequential 16 13477 1720.11 107.5 9 73 3120
32 R 120 64 sequential 4 13477 1720.56 107.53 5 73 3128
8 W 120 64 random 16 13477 1754.31 109.64 1 72 215
32 W 120 64 random 4 13477 1758.18 109.88 4 72 218
4 R 120 64 random 32 13477 1760.05 110 3 72 255
16 R 120 64 random 8 13477 1755.46 109.71 2 72 183
16 R 120 64 sequential 8 13477 1758.28 109.89 5 72 202
64 R 120 64 random 2 13477 1765.08 110.31 5 71 188
4 R 120 64 sequential 32 13477 1770.2 110.63 4 71 191
64 R 120 64 sequential 2 13477 1765.88 110.36 7 71 143
2 W 120 64 random 32 13477 1692.61 105.78 1 37 3055
4 W 120 64 sequential 16 13477 1688.39 105.52 1 37 3050
16 W 120 64 sequential 4 13477 1684.42 105.27 1 37 3050
64 W 120 64 sequential 1 13477 1686.46 105.4 1 37 3055
8 W 120 64 random 8 13477 1714.75 107.17 1 36 3049
32 W 120 64 random 2 13477 1717.76 107.36 2 36 3051
64 W 120 64 random 1 13477 1737.39 108.58 1 36 181
4 R 120 64 random 16 13477 1712.89 107.05 4 36 3078
16 R 120 64 random 4 13477 1718.53 107.4 3 36 3105
32 R 120 64 random 2 13477 1754.48 109.65 6 36 138
64 R 120 64 random 1 13477 1719.04 107.44 2 36 3091
2 W 120 64 sequential 32 13477 1727.96 107.99 0 36 161
8 W 120 64 sequential 8 13477 1728.38 108.02 0 36 164
32 W 120 64 sequential 2 13477 1729.04 108.06 2 36 165
4 R 120 64 sequential 16 13477 1712.2 107.01 1 36 3067
16 R 120 64 sequential 4 13477 1722.03 107.62 5 36 3070
32 R 120 64 sequential 2 13477 1754.35 109.64 1 36 176
64 R 120 64 sequential 1 13477 1720.06 107.5 1 36 3064
4 W 120 64 random 16 13477 1760.54 110.03 1 35 180
16 W 120 64 random 4 13477 1764.09 110.25 1 35 178
2 R 120 64 random 32 13477 1767.8 110.48 1 35 96
8 R 120 64 random 8 13477 1765.9 110.36 3 35 155
2 R 120 64 sequential 32 13477 1767.06 110.44 1 35 165
8 R 120 64 sequential 8 13477 1769.64 110.6 3 35 161
4 W 120 64 random 8 13477 1710.77 106.92 1 18 3023
16 W 120 64 random 2 13477 1717.86 107.36 1 18 3203
2 R 120 64 random 16 13477 1698.2 106.13 2 18 3044
8 R 120 64 random 4 13477 1710.29 106.89 4 18 3055
32 R 120 64 random 1 13477 1717.75 107.35 2 18 3046
2 W 120 64 sequential 16 13477 1684.3 105.26 1 18 3040
8 W 120 64 sequential 4 13477 1687.34 105.45 1 18 3043
32 W 120 64 sequential 1 13477 1685.95 105.37 1 18 3036
2 R 120 64 sequential 16 13477 1723.77 107.73 1 18 3029
8 R 120 64 sequential 4 13477 1704.12 106.5 0 18 3046
32 R 120 64 sequential 1 13477 1717.2 107.32 1 18 3031
2 W 120 64 random 16 13477 1738.64 108.66 1 17 157
8 W 120 64 random 4 13477 1755.12 109.69 0 17 155
32 W 120 64 random 1 13477 1760.38 110.02 1 17 154
4 R 120 64 random 8 13477 1761.02 110.06 4 17 155
16 R 120 64 random 2 13477 1765.62 110.35 2 17 138
4 W 120 64 sequential 8 13477 1731 108.18 1 17 94
16 W 120 64 sequential 2 13477 1728.03 108 1 17 137
4 R 120 64 sequential 8 13477 1766.88 110.43 1 17 112
16 R 120 64 sequential 2 13477 1767.86 110.49 1 17 169
2 W 120 64 random 8 13477 1691.57 105.72 1 8 3028
4 W 120 64 random 4 13477 1750.12 109.38 1 8 142
8 W 120 64 random 2 13477 1713.82 107.11 1 8 3022
16 W 120 64 random 1 13477 1758 109.87 1 8 43
2 R 120 64 random 8 13477 1762.74 110.17 2 8 145
4 R 120 64 random 4 13477 1723.21 107.7 2 8 3035
8 R 120 64 random 2 13477 1765.4 110.33 2 8 133
16 R 120 64 random 1 13477 1707.97 106.74 2 8 3031
2 W 120 64 sequential 8 13477 1728.4 108.02 1 8 150
4 W 120 64 sequential 4 13477 1685.1 105.31 1 8 3038
8 W 120 64 sequential 2 13477 1730.99 108.18 1 8 46
16 W 120 64 sequential 1 13477 1688 105.5 1 8 3039
2 R 120 64 sequential 8 13477 1747.58 109.22 0 8 165
4 R 120 64 sequential 4 13477 1715.02 107.18 0 8 3021
8 R 120 64 sequential 2 13477 1765.55 110.34 1 8 171
16 R 120 64 sequential 1 13477 1706 106.62 1 8 3031
2 W 120 64 random 4 13477 1673.4 104.58 0 4 144
4 W 120 64 random 2 13477 1702.13 106.38 1 4 3015
8 W 120 64 random 1 13477 1753.95 109.62 1 4 135
2 R 120 64 random 1 13477 411.09 25.69 0 4 3013
2 R 120 64 random 2 13477 849.21 53.07 0 4 135
2 R 120 64 random 4 13477 1507.93 94.24 0 4 3021
4 R 120 64 random 1 13477 834.58 52.16 0 4 3021
4 R 120 64 random 2 13477 1516.67 94.79 0 4 139
8 R 120 64 random 1 13477 1501.7 93.85 0 4 3008
2 W 120 64 sequential 4 13477 1686.82 105.42 1 4 3026
4 W 120 64 sequential 2 13477 1726.97 107.93 1 4 130
8 W 120 64 sequential 1 13477 1682.9 105.18 1 4 3034
2 R 120 64 sequential 4 13477 1660.67 103.79 0 4 3017
4 R 120 64 sequential 2 13477 1691.5 105.71 1 4 165
8 R 120 64 sequential 1 13477 1661.5 103.84 1 4 3014
2 W 120 64 random 2 13477 1608.38 100.52 0 2 3007
2 R 120 64 sequential 2 13477 1653.15 103.32 1 2 125
4 R 120 64 sequential 1 13477 1593.5 99.59 0 2 3014
4 W 120 64 random 1 13477 1742.95 108.93 0 1 131
2 W 120 64 sequential 2 13477 1728.87 108.05 0 1 86
4 W 120 64 sequential 1 13477 1683.59 105.22 0 1 3030
2 W 120 64 sequential 1 13477 1563.2 97.7 0 0 3021
2 R 120 64 sequential 1 13477 1551.9 96.99 0 0 3013
October 1, 2010 at 2:02 am
It looks like a good sata drive, good stable overall performance.
But you have to compare it with other drive to have some kind of baseline.
No need to run so many tests and to go that high in thread count, as you can see, once the avg_latencyms start to rise steadily as (thread cound x ioOustanding) increase, your drive has reached it's io/sec limit (> 16 in your case).
You should also run 10/20 mins tests (and not 120sec) if it's a SAN.
Make also sure you are using a big filesize (10GB or 20GB) to avoid hitting the disk cache (SAN can have very big cache).
Depending on what you plan to use the drive for you have to focus on different things.
If you plan to store backups or regular files, you want the highest MB/sec as possible in sequential read and write.
If you plan to put tempdb on it, you want highest io/sec as possible.
If you plan to store log files you are looking for good sequential write MB/sec troughput.
If you plan to put the data file of a DB you are looking for 8KB and 64KB random Read and Write with highest IO/sec as possible.
October 12, 2010 at 5:40 am
Thanks for your reply....
Based up on above results how I can make a decision:
Which files I have to place on this drive to get high performance benefit and please explain how to make decisions based up on the output of SQLIO.
Sorry this much late response as I got stucked on to other issues.
Thanks,
Vamsy
October 12, 2010 at 6:03 am
Any one please help me on this..........
October 12, 2010 at 6:47 am
You don't take decision based on a single SQLIO, it works the other way around, you have expectation (xxx IO/s or xxx MB/s) and you use SQLIO to make sure you can reach it.
If you have no idea of how much IO/s or MB/s you need then ... there is no way to tell you if the drive will be enough for the job.
You could put anything on this drive, but to know if the performance will be good, it all depend on what is "good" for you, there are way too many different variables.
Now, based on what we have here, the drive would be ok for any DB files (data, log or backup) but don't expect it to keep up if you have too much load.
Don't mix different kind of load though (no log and data on the same drive).
October 12, 2010 at 7:22 am
Thanks for your suggestions:
Is this the way of predicting how much IOs do I need? Collecting avg disk reads/sec and avg disk writes/sec using perfmon
Thanks,
Vamsy
October 12, 2010 at 7:54 am
Yes, you can see how many IO you are using right now.
If you can test that on a DB server already running you would already have a good idea of what to expect.
If you see no improvement when switching to faster disk, that means your disk is not the bottleneck.
October 13, 2010 at 8:04 am
PLEASE do yourself a favor and get a professional to help you configure your IO system correctly. Right now is the ONLY time you will have to do MANY things correctly - and from my experience almost EVERYONE does most things SUBOPTIMALLY from an IO configuration standpoint.
I do have to note that it really doesn't make much sense to run a test that you have no ability to interpret the output of!
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 13, 2010 at 9:24 am
A) Yes, get a professional in. You'll learn a lot, and have a better setup.
B) For a simplified system, build yourself a table with rather less factors: Change the IO size (8, 64, 256, and for sequential 1024), and random/sequential and read/write. Analyze all the random together, and all the sequential together. See what the curves look like.
October 14, 2010 at 8:41 am
Take a look here: http://msdn.microsoft.com/en-us/library/cc966412.aspx
IO size = 64 KB is not enough.
October 15, 2010 at 2:26 am
Actually most sas disk will have the same throughput for IO size of 8K and above, so you really just need to test for 8K.
October 15, 2010 at 7:27 am
Oliiii (10/15/2010)
Actually most sas disk will have the same throughput for IO size of 8K and above, so you really just need to test for 8K.
Do you have any reference you can point me to to confirm that statement?
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 15, 2010 at 9:20 am
I didn't read it online, it's something we've noticed here while testing sas disk (so i don't have a link).
Later it came up in a High perf IO subsystem presentation we had with a msft PFE and he confirmed that most sas disk reach their max throughput with 8K+ io size, which is quite easy to test if you have a sas disk on hand.
If all you have a is a LUN you'll have to crank up the concurent IO to offset whatever latency you have and use a file big enough to avoid hitting the cache.
I should add that it's only true for sequential IO (but since we are talking about throughput i guess that was obvious).
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply