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)
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
No comments:
Post a Comment