Gather Storage Subsystem Performance Metrics Using the Diskspd Utility for SQL Server Applications


By:   |   Updated: 2017-03-24   |   Comments (2)   |   Related: More > Hardware

Problem

Whether you are implementing a new storage infrastructure or performing an upgrade/update to your existing hardware it's always good to have a tool that you can use to get a baseline measurement of the storage subsystem performance, so you can compare this baseline with the performance after you've made your changes. This tip will look at using the DiskSpd utility to gather these performance metrics.

Solution

There are many different tools you could use to gather these performance metrics, but the DiskSpd utility is good because it's well documented and really easy to use. I also like the fact that it's a command line tool so it makes it really easy to run multiple tests with different parameters if that is something you require. If you haven't used it before you can download it from Microsoft Technet.

Once you've downloaded the software you can extract the appropriate executable and copy it to the server you want to run the test against. Let's first run the utility from the command prompt with the "-?" switch.

C:\>diskspd.exe -?

This command will output a complete listing of all the parameters available along with a short description. Below is a brief description of each of the parameters we will use for this test but you can get a complete description from the documentation that is included in the .zip file you downloaded earlier.

Parameter

Description

-b Block size for reads/writes. For this test we will use 64K since this is mainly what SQL Server would use to read data. You could run multiple tests using different block sizes to simulate other SQL Server read/write operations
-d Test duration in seconds
-Suw Disables hardware and software buffering
-L Gather disk latency statistics
-t Number of threads per target. I keep this value to the number of cores on my server
-W Warmup duration. Number of seconds test runs before gathering statistics
-w Percentage of write requests, i.e. if set to 30% other 70% of IO test will be reads
-c Creates a test file of the specified size
> diskperf.out Output file to save generated statistics. If omitted statistics will be displayed on your screen

Now that we know what each parameter does we can use the following command at the windows command prompt to start our test.

C:\>diskspd.exe -b64K -d600 -Suw -L -t8 -W30 -w20 -c10G C:\diskperftestfile.dat > diskperf.out

Once the test completes we can take a look at the results by opening the output file we specified above. The output itself is fairly self-explanatory, but let's go through and give an overview of each section individually.

The first section of the output displays the command used to initiate this test along with a summary of the commands.

Command Line: diskspd.exe -b64K -d600 -Suw -L -t8 -W30 -w20 -c10G C:\diskperftestfile.dat

Input parameters:

timespan:   1
-------------
duration: 600s
warm up time: 30s
cool down time: 0s
measuring latency
random seed: 0
path: 'C:\diskperftestfile.dat'
think time: 0ms
burst size: 0
software cache disabled
hardware write cache disabled, writethrough on
performing mix test (read/write ratio: 80/20)
block size: 65536
using sequential I/O (stride: 65536)
number of outstanding I/O operations: 2
thread stride size: 0
threads per file: 8
using I/O Completion Ports
IO priority: normal

This next section gives you an overview of your CPU usage during your test. This will tell you if your storage is not the issue and there is a CPU bottleneck of some sort causing you not to get the best performance out of your storage subsystem. This is definitely not the issue in our case as you can see the CPU is ~98% idle.

actual test time: 600.00s
thread count: 8
proc count: 8

CPU |  Usage |  User  |  Kernel |  Idle
-------------------------------------------
   0|   1.65%|   0.61%|    1.04%|  98.35%
   1|   1.40%|   0.49%|    0.91%|  98.60%
   2|   1.64%|   0.61%|    1.02%|  98.36%
   3|   4.11%|   0.32%|    3.79%|  95.89%
   4|   1.48%|   0.44%|    1.04%|  98.52%
   5|   1.23%|   0.27%|    0.96%|  98.77%
   6|   1.43%|   0.43%|    1.00%|  98.57%
   7|   1.19%|   0.31%|    0.88%|  98.81%
-------------------------------------------
avg.|   1.77%|   0.44%|    1.33%|  98.23%

This next section is the meat and potatoes of the report. Here we get a breakdown of the IOPS, MB/s and latency statistics for each thread in our test along with a summary. Note there is a separate table for Reads, Writes and Total IO.

Total IO
thread |       bytes     |     I/Os     |     MB/s   |  I/O per s |  AvgLat  | LatStdDev |  file
----------------------------------------------------------------------------------------------------------------------------
     0 |      6983254016 |       106556 |      11.10 |     177.59 |   11.259 |     2.446 | C:\diskperftestfile.dat (10240MB)
     1 |      6918569984 |       105569 |      11.00 |     175.95 |   11.364 |     2.598 | C:\diskperftestfile.dat (10240MB)
     2 |      6918766592 |       105572 |      11.00 |     175.95 |   11.364 |     2.770 | C:\diskperftestfile.dat (10240MB)
     3 |      7065501696 |       107811 |      11.23 |     179.68 |   11.128 |     2.204 | C:\diskperftestfile.dat (10240MB)
     4 |      7078346752 |       108007 |      11.25 |     180.01 |   11.108 |     2.184 | C:\diskperftestfile.dat (10240MB)
     5 |      6915096576 |       105516 |      10.99 |     175.86 |   11.370 |     2.332 | C:\diskperftestfile.dat (10240MB)
     6 |      6920536064 |       105599 |      11.00 |     176.00 |   11.361 |     2.417 | C:\diskperftestfile.dat (10240MB)
     7 |      6908018688 |       105408 |      10.98 |     175.68 |   11.381 |     2.377 | C:\diskperftestfile.dat (10240MB)
----------------------------------------------------------------------------------------------------------------------------
total:       55708090368 |       850038 |      88.55 |    1416.73 |   11.291 |     2.424

Read IO
thread |       bytes     |     I/Os     |     MB/s   |  I/O per s |  AvgLat  | LatStdDev |  file
----------------------------------------------------------------------------------------------------------------------------
     0 |      5587075072 |        85252 |       8.88 |     142.09 |   11.042 |     2.391 | C:\diskperftestfile.dat (10240MB)
     1 |      5529665536 |        84376 |       8.79 |     140.63 |   11.184 |     2.562 | C:\diskperftestfile.dat (10240MB)
     2 |      5520490496 |        84236 |       8.77 |     140.39 |   11.179 |     2.837 | C:\diskperftestfile.dat (10240MB)
     3 |      5650841600 |        86225 |       8.98 |     143.71 |   10.913 |     2.176 | C:\diskperftestfile.dat (10240MB)
     4 |      5655363584 |        86294 |       8.99 |     143.82 |   10.854 |     2.091 | C:\diskperftestfile.dat (10240MB)
     5 |      5527568384 |        84344 |       8.79 |     140.57 |   11.202 |     2.345 | C:\diskperftestfile.dat (10240MB)
     6 |      5527896064 |        84349 |       8.79 |     140.58 |   11.186 |     2.440 | C:\diskperftestfile.dat (10240MB)
     7 |      5529731072 |        84377 |       8.79 |     140.63 |   11.218 |     2.379 | C:\diskperftestfile.dat (10240MB)
----------------------------------------------------------------------------------------------------------------------------
total:       44528631808 |       679453 |      70.78 |    1132.42 |   11.096 |     2.414

Write IO
thread |       bytes     |     I/Os     |     MB/s   |  I/O per s |  AvgLat  | LatStdDev |  file
----------------------------------------------------------------------------------------------------------------------------
     0 |      1396178944 |        21304 |       2.22 |      35.51 |   12.125 |     2.468 | C:\diskperftestfile.dat (10240MB)
     1 |      1388904448 |        21193 |       2.21 |      35.32 |   12.079 |     2.616 | C:\diskperftestfile.dat (10240MB)
     2 |      1398276096 |        21336 |       2.22 |      35.56 |   12.094 |     2.350 | C:\diskperftestfile.dat (10240MB)
     3 |      1414660096 |        21586 |       2.25 |      35.98 |   11.986 |     2.103 | C:\diskperftestfile.dat (10240MB)
     4 |      1422983168 |        21713 |       2.26 |      36.19 |   12.114 |     2.256 | C:\diskperftestfile.dat (10240MB)
     5 |      1387528192 |        21172 |       2.21 |      35.29 |   12.037 |     2.155 | C:\diskperftestfile.dat (10240MB)
     6 |      1392640000 |        21250 |       2.21 |      35.42 |   12.056 |     2.193 | C:\diskperftestfile.dat (10240MB)
     7 |      1378287616 |        21031 |       2.19 |      35.05 |   12.036 |     2.250 | C:\diskperftestfile.dat (10240MB)
----------------------------------------------------------------------------------------------------------------------------
total:       11179458560 |       170585 |      17.77 |     284.31 |   12.066 |     2.305

Finally we have a summary table of per percentile latencies from our test. For this section the higher nine percentiles will sometimes show up as they do for the 6-nines row and above in this example. This is because there was not enough data from the test we performed to be able to differentiate these statistics.

  %-ile |  Read (ms) | Write (ms) | Total (ms)
----------------------------------------------
    min |      0.135 |      1.112 |      0.135
   25th |      9.696 |     10.647 |      9.861
   50th |     10.940 |     11.912 |     11.122
   75th |     12.260 |     13.275 |     12.490
   90th |     13.563 |     14.634 |     13.832
   95th |     14.415 |     15.507 |     14.726
   99th |     16.426 |     17.637 |     16.794
3-nines |     32.362 |     28.716 |     31.543
4-nines |     58.255 |     45.651 |     56.318
5-nines |    141.810 |    130.510 |    140.661
6-nines |    189.805 |    183.253 |    189.805
7-nines |    189.805 |    183.253 |    189.805
8-nines |    189.805 |    183.253 |    189.805
9-nines |    189.805 |    183.253 |    189.805
    max |    189.805 |    183.253 |    189.805

Now that you have a good baseline to refer back to you can rerun the same test any time you suspect there might be an issue with your storage hardware or after any sort of storage subsystem maintenance to confirm if in fact the performance has changed.

Next Steps


Last Updated: 2017-03-24


get scripts

next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Friday, March 24, 2017 - 11:12:52 AM - Greg Robidoux Back To Top

Thanks Zach. This has been fixed.

-Greg


Friday, March 24, 2017 - 10:52:29 AM - Zach Back To Top

Your "DiskSpd" hyperlink (first sentence under Solution section) links to another random blog post. 



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools