Sunday, March 6, 2016

Diskspd Utility: A Robust Storage Testing Tool (superseding SQLIO)

https://gallery.technet.microsoft.com/DiskSpd-a-robust-storage-6cd2f223


The following table covers the different SQL Server operations.


File type
Operation
READ pattern
WRITE pattern
Threads used
I/O type
Data File
Normal Activity
8KiB up to 128KiB
8KiB up to 128KiB
Based on MaxDOP
Random
Checkpoint
N/A
64KiB up to 128 KiB
# of Sockets in Computer
Random
LazyWriter
N/A
64KiB up to 128 KiB
1 per NUMA Node
Random
Bulk Insert
N/A
8KiB up to 128 KiB
Based on MaxDOP
Sequential
Backup
1 MB
1 MB
Based on MaxDOP
Sequential
Restore
64KiB
64KiB
Based on MaxDOP
Sequential
DBCC Checkdb w/ no repair option
8KiB up to 64KiB
N/A
Based on MaxDOP
Sequential
Rebuild Index
See Read Ahead
8KiB – 128 KiB
Based on MaxDOP
Sequential
ReadAhead
Up to 512 KiB
N/A
Based on MaxDOP
Sequential
Log File
Normal Activity
512 bytes - 64KiB
512 bytes - 64KiB
one log writer thread per soft NUMA node with a cap of 4
Sequential

Parameters Explained:


  • -b8K (Block size is 8KiB)
  • -d300 (5 Minutes)
  • -h (Disable both software caching and hardware write caching)
  • -L (Measure latency statistics)
  • -o32 (32 outstanding IO requests)
  • -r (Random IO)
  • -w40 (40% writes, 60% READ)
  • -t8 (8 threads)
  • -c1G (A 1 GiBfile to be created)
  • .\datafile.dat (File path and name to create for test)


D:\temp\Diskspd-v2.0.15\amd64fre>.\diskspd.exe -b8K -d300 -h -L -o32 -r -w40 -t8 -c1G .\datafile.dat

Command Line: .\diskspd.exe -b8K -d300 -h -L -o32 -r -w40 -t8 -c1G .\datafile.dat

Input parameters:

        timespan:   1
        -------------
        duration: 300s
        warm up time: 5s
        cool down time: 0s
        measuring latency
        random seed: 0
        path: '.\datafile.dat'
                think time: 0ms
                burst size: 0
                software and hardware write cache disabled
                performing mix test (write/read ratio: 40/100)
                block size: 8192
                using random I/O (alignment: 8192)
                number of outstanding I/O operations: 32
                thread stride size: 0
                threads per file: 8
                using I/O Completion Ports
                IO priority: normal



Results for timespan 1:
*******************************************************************************

actual test time:       300.01s
thread count:           8
proc count:             1

CPU |  Usage |  User  |  Kernel |  Idle
-------------------------------------------
   0|  35.31%|   2.49%|   32.82%|  64.69%
-------------------------------------------
avg.|  35.31%|   2.49%|   32.82%|  64.69%

Total IO
thread |       bytes     |     I/Os     |     MB/s   |  I/O per s |  AvgLat  | LatStdDev |  file
-----------------------------------------------------------------------------------------------------
     0 |      1148518400 |       140200 |       3.65 |     467.33 |   68.764 |   177.241 | .\datafile.dat (1024MB)
     1 |      1105862656 |       134993 |       3.52 |     449.97 |   71.264 |   178.528 | .\datafile.dat (1024MB)
     2 |      1061683200 |       129600 |       3.37 |     431.99 |   74.395 |   184.924 | .\datafile.dat (1024MB)
     3 |      1089929216 |       133048 |       3.46 |     443.49 |   72.456 |   184.804 | .\datafile.dat (1024MB)
     4 |      1099988992 |       134276 |       3.50 |     447.58 |   71.656 |   181.355 | .\datafile.dat (1024MB)
     5 |      1115602944 |       136182 |       3.55 |     453.93 |   70.492 |   175.891 | .\datafile.dat (1024MB)
     6 |      1076895744 |       131457 |       3.42 |     438.18 |   73.068 |   180.855 | .\datafile.dat (1024MB)
     7 |      1126612992 |       137526 |       3.58 |     458.41 |   69.888 |   177.912 | .\datafile.dat (1024MB)
-----------------------------------------------------------------------------------------------------
total:        8825094144 |      1077282 |      28.05 |    3590.88 |   71.458 |   180.165

Read IO
thread |       bytes     |     I/Os     |     MB/s   |  I/O per s |  AvgLat  | LatStdDev |  file
-----------------------------------------------------------------------------------------------------
     0 |       690298880 |        84265 |       2.19 |     280.88 |   68.483 |   176.451 | .\datafile.dat (1024MB)
     1 |       665673728 |        81259 |       2.12 |     270.86 |   70.630 |   177.404 | .\datafile.dat (1024MB)
     2 |       637288448 |        77794 |       2.03 |     259.31 |   73.995 |   184.461 | .\datafile.dat (1024MB)
     3 |       652034048 |        79594 |       2.07 |     265.31 |   72.569 |   185.260 | .\datafile.dat (1024MB)
     4 |       659439616 |        80498 |       2.10 |     268.32 |   71.175 |   180.766 | .\datafile.dat (1024MB)
     5 |       667975680 |        81540 |       2.12 |     271.80 |   70.073 |   175.241 | .\datafile.dat (1024MB)
     6 |       646021120 |        78860 |       2.05 |     262.86 |   73.246 |   181.189 | .\datafile.dat (1024MB)
     7 |       676167680 |        82540 |       2.15 |     275.13 |   70.110 |   178.506 | .\datafile.dat (1024MB)
-----------------------------------------------------------------------------------------------------
total:        5294899200 |       646350 |      16.83 |    2154.46 |   71.244 |   179.886

Write IO
thread |       bytes     |     I/Os     |     MB/s   |  I/O per s |  AvgLat  | LatStdDev |  file
-----------------------------------------------------------------------------------------------------
     0 |       458219520 |        55935 |       1.46 |     186.45 |   69.187 |   178.424 | .\datafile.dat (1024MB)
     1 |       440188928 |        53734 |       1.40 |     179.11 |   72.222 |   180.210 | .\datafile.dat (1024MB)
     2 |       424394752 |        51806 |       1.35 |     172.68 |   74.996 |   185.616 | .\datafile.dat (1024MB)
     3 |       437895168 |        53454 |       1.39 |     178.18 |   72.288 |   184.124 | .\datafile.dat (1024MB)
     4 |       440549376 |        53778 |       1.40 |     179.26 |   72.377 |   182.230 | .\datafile.dat (1024MB)
     5 |       447627264 |        54642 |       1.42 |     182.14 |   71.116 |   176.855 | .\datafile.dat (1024MB)
     6 |       430874624 |        52597 |       1.37 |     175.32 |   72.800 |   180.354 | .\datafile.dat (1024MB)
     7 |       450445312 |        54986 |       1.43 |     183.28 |   69.556 |   177.017 | .\datafile.dat (1024MB)
-----------------------------------------------------------------------------------------------------
total:        3530194944 |       430932 |      11.22 |    1436.42 |   71.779 |   180.584


  %-ile |  Read (ms) | Write (ms) | Total (ms)
----------------------------------------------
    min |      0.034 |      0.036 |      0.034
   25th |      0.622 |      0.624 |      0.622
   50th |      1.174 |      1.179 |      1.176
   75th |     47.547 |     47.779 |     47.652
   90th |    190.024 |    196.566 |    192.592
   95th |    470.770 |    475.655 |    472.954
   99th |    935.253 |    935.162 |    935.253
3-nines |   1366.195 |   1379.733 |   1377.489
4-nines |   1741.597 |   1726.357 |   1733.603
5-nines |   2158.595 |   1897.107 |   2121.649
6-nines |   2452.415 |   2219.306 |   2229.552
7-nines |   2452.415 |   2219.306 |   2452.415
8-nines |   2452.415 |   2219.306 |   2452.415
    max |   2452.415 |   2219.306 |   2452.415