No tags yet.
It's important to test your storage performance especially prior to installing or deploying a new SQL Server.
Microsoft has provided us with a great tool called Diskspd, which was meant to replace SQLIO. Diskspd synthetically generates workloads to run against your server. It's pretty robust and has a lot of parameters so that you can customize your test.
Ex. In the command below, I specified -b8k, which means the block size is going to run at 8k, which is the size that SQL uses for pages.
Diskspd.exe -c20G -d30 -r -w0 -t8 -o8 -b8K -Sh -L N:\IOTestfile.dat
Let's take a quick look at the Parameters:
-c - creates a file that's going to be used for the workload. Above I've set it to be 20G
-d - test duration in seconds.
-w Write percentage (I set it to 0 to do 100% reads, but you can set the ratio here)
-o - outstanding IO per target and workerthread.
-b - block size of the IO, I set it to 8K, as that's how SQL Server stores it's data pages.
-L - Latency information which is important to SQL DBAs.
Also i specified the drive that I wanted to test and thus place the test file.
Once it's finished running, you can see the output below.
Summary of parameters used:
Output:
Input parameters:
timespan: 1 ------------- duration: 30s warm up time: 5s cool down time: 0s measuring latency random seed: 0 path: 'M:\IOTestfile.dat' think time: 0ms burst size: 0 software cache disabled hardware write cache disabled, writethrough on performing read test block size: 8192 using random I/O (alignment: 8192) number of outstanding I/O operations: 8 thread stride size: 0 threads per file: 8 using I/O Completion Ports IO priority: normal
Results for timespan 1: *************************************************************************
CPU Usage:
actual test time: 30.00s thread count: 8 proc count: 64
CPU | Usage | User | Kernel | Idle ------------------------------------------- 0| 22.55%| 2.81%| 19.74%| 77.45% 1| 24.06%| 3.54%| 20.52%| 75.94% 2| 23.12%| 3.33%| 19.79%| 76.87% 3| 43.23%| 2.45%| 40.78%| 56.77% 4| 14.37%| 2.14%| 12.24%| 85.62% 5| 15.42%| 1.93%| 13.49%| 84.58% 6| 13.54%| 0.83%| 12.71%| 86.46% 7| 13.49%| 0.94%| 12.55%| 86.51% ------------------------------------------- avg.| 2.65%| 0.28%| 2.37%| 22.35%
Total IO thread | bytes | I/Os | MB/s | I/O per s | AvgLat |atStdDev | file -------------------------------------------------------------------------------------------------------------------- 0 | 1129332736 | 137858 | 35.90 | 4595.20 | 1.737 | 1.692 | M:\IOTestfile.dat (10240MB) 1 | 1140785152 | 139256 | 36.26 | 4641.80 | 1.720 | 1.509 | M:\IOTestfile.dat (10240MB) 2 | 1127604224 | 137647 | 35.85 | 4588.17 | 1.742 | 1.999 | M:\IOTestfile.dat (10240MB) 3 | 1137983488 | 138914 | 36.17 | 4630.40 | 1.724 | 1.030 | M:\IOTestfile.dat (10240MB) 4 | 1166639104 | 142412 | 37.09 | 4747.00 | 1.682 | 0.998 | M:\IOTestfile.dat (10240MB) 5 | 1160880128 | 141709 | 36.90 | 4723.56 | 1.690 | 1.017 | M:\IOTestfile.dat (10240MB) 6 | 1169096704 | 142712 | 37.16 | 4757.00 | 1.678 | 0.990 | M:\IOTestfile.dat (10240MB) 7 | 1168007168 | 142579 | 37.13 | 4752.56 | 1.680 | 1.032 | M:\IOTestfile.dat (10240MB) ------------------------------------------------------------------------------- total: 9200328704 | 1123087 | 292.47 | 37435.68 | 1.706 | 1.331
Read IO thread | bytes | I/Os | MB/s | I/O per s | AvgLat |atStdDev | file ------------------------------------------------------------------------------- 0 | 1129332736 | 137858 | 35.90 | 4595.20 | 1.737 | 1.692 | M:\IOTestfile.dat (10240MB) 1 | 1140785152 | 139256 | 36.26 | 4641.80 | 1.720 | 1.509 | M:\IOTestfile.dat (10240MB) 2 | 1127604224 | 137647 | 35.85 | 4588.17 | 1.742 | 1.999 | M:\IOTestfile.dat (10240MB) 3 | 1137983488 | 138914 | 36.17 | 4630.40 | 1.724 | 1.030 | M:\IOTestfile.dat (10240MB) 4 | 1166639104 | 142412 | 37.09 | 4747.00 | 1.682 | 0.998 | M:\IOTestfile.dat (10240MB) 5 | 1160880128 | 141709 | 36.90 | 4723.56 | 1.690 | 1.017 | M:\IOTestfile.dat (10240MB) 6 | 1169096704 | 142712 | 37.16 | 4757.00 | 1.678 | 0.990 | M:\IOTestfile.dat (10240MB) 7 | 1168007168 | 142579 | 37.13 | 4752.56 | 1.680 | 1.032 | M:\IOTestfile.dat (10240MB) ------------------------------------------------------------------------------- --------------------- total: 9200328704 | 1123087 | 292.47 | 37435.68 | 1.706 | 1.331
Write IO thread | bytes | I/Os | MB/s | I/O per s | AvgLat | atStdDev | file ------------------------------------------------------------------------------- --------------------- 0 | 0 | 0 | 0.00 | 0.00 | 0.000 | N/A | M:\IOTestfile.dat (10240MB) 1 | 0 | 0 | 0.00 | 0.00 | 0.000 | N/A | M:\IOTestfile.dat (10240MB) 2 | 0 | 0 | 0.00 | 0.00 | 0.000 | N/A | M:\IOTestfile.dat (10240MB) 3 | 0 | 0 | 0.00 | 0.00 | 0.000 | N/A | M:\IOTestfile.dat (10240MB) 4 | 0 | 0 | 0.00 | 0.00 | 0.000 | N/A | M:\IOTestfile.dat (10240MB) 5 | 0 | 0 | 0.00 | 0.00 | 0.000 | N/A | M:\IOTestfile.dat (10240MB) 6 | 0 | 0 | 0.00 | 0.00 | 0.000 | N/A | M:\IOTestfile.dat (10240MB) 7 | 0 | 0 | 0.00 | 0.00 | 0.000 | N/A | M:\IOTestfile.dat (10240MB) ------------------------------------------------------------------------------- --------------------- total: 0 | 0 | 0.00 | 0.00 | 0.000 | N/A
Latency:
%-ile | Read (ms) | Write (ms) | Total (ms) ---------------------------------------------- min | 0.251 | N/A | 0.251 25th | 1.461 | N/A | 1.461 50th | 1.581 | N/A | 1.581 75th | 1.724 | N/A | 1.724 90th | 1.940 | N/A | 1.940 95th | 2.418 | N/A | 2.418 99th | 3.880 | N/A | 3.880 3-nines | 14.621 | N/A | 14.621 4-nines | 44.379 | N/A | 44.379 5-nines | 134.738 | N/A | 134.738 6-nines | 139.410 | N/A | 139.410 7-nines | 139.454 | N/A | 139.454 8-nines | 139.454 | N/A | 139.454 9-nines | 139.454 | N/A | 139.454 max | 139.454 | N/A | 139.454