How to Use DiskSpd to Benchmark Your Storage for SQL Server

If you’ve been working with SQL Server for a while,  chances are that you have worked with the SQLIO utility in the past to benchmark storage for your SQL Server implementation. As many are already aware, the SQLIO utility has been retired and no longer distributed or maintained. Microsoft has introduced and recommends the use of a new tool called DiskSpd as a replacement.

In today’s blog post, I’ll cover the basics of using DiskSpd for your storage device and demonstrate results from a recent DiskSpd benchmark test that we ran on the 6th generation of the K2 all-flash storage. Feel free to compare to your existing disk-based or all-flash storage. In fact, we encourage it 🙂

Overall, DiskSpd is quite similar to SQLIO. If you were familiar working with SQLIO, DiskSpd should be fairly straightforward and easy to use. Below is a standard test you can use for simple storage benchmarking:

Usage:

Random Read Thruput (R-TP):

Diskspd.exe -b64K -o16 –d90 -Sh –r4k -t16 -w0   #1 > output.txt

Randon Read IOPS (R-IO):

Diskspd.exe -b8K  -o32 –d90 -Sh –r4k -t64 -w0   #1 > output.txt

Random Write Thruput (W-TP):

Diskspd.exe -b64K -o16 –d90 -Sh –r4k -t16 -w100 #1 > output.txt

Randon Write IOPS (W-IO):

Diskspd.exe -b8K  -o32 –d90 -Sh –r4k -t64 -w100 #1 > output.txt

 

Parameters Used:

-b        :        Block size in KiB, MiB, or GiB

-o        :        Number of outstanding I/O requests per target per thread

-d        :        Duration of measurement period in seconds

-Sh     :        Disable both software caching and hardware write caching

-L        :        Measure latency statistics

-r         :        Random I/O aligned by number of <align> bytes or KiB, MiB, GiB

-t         :        Number of threads per target

-w       :        Percentage of write requests to issue

#          :        Test target is Physical Drive number (e.g. ‘Disk 1’)

 

We recently published a benchmark running DiskSpd against K2 and you can obtain the formal results by contacting us. Below is an example of running the workload from 3 hosts against a single K-Block (two active-active controllers) K2 Gen6 system. Keep in mind that the truly active-active shared scale-out capabilities of the K2 allow for linear scale of performance as we add more scale out K-Blocks, all with the flexibility of scaling capacity independently of performance.

Interested in learning why K2 is the ideal storage platform for your SQL Server environment? Download this report that covers a set of compression tests and performance tests that quantify the benefits of Kaminario’s K2 All-Flash Array storage for Microsoft SQL Server.

—-