SQLIO vs. DiskSpd

Microsoft formally announced that SQLIO has now been fully deprecated, in favor of DiskSpd. In case you haven’t migrated yet, this content will explain basic use cases and commands using DiskSpd.

While that tool was great in the days of spinning rust (read: hard drives), SQLIO really had nothing to do with SQL Server. It was a storage benchmarking tool, much like IOmeter, CrystalDiskMark, ATTO, HDTune, and probably 20 or 30 more.

SQLIO vs. DiskSpd?

After SQLIO was retired, Microsoft now advocates for the use of DiskSpd. Both SQLIO and DiskSpd are great for performing basic plumbing tests, where you are trying to find the maximum data throughput you will obtain from your system. For a more realistic simulation of your production environment, though, you will need to use something else. Mimicking your actual workload with DiskSpd will be a very, very time consuming task, if at all possible. The reason is that SQL Server never actually uses a fixed IO block size. It can issue IOs as tiny as a few hundred bytes (typically your sector size for transaction log flushes), or a large as several megabytes in size (the case of backups and restores). You can certainly try to kick off multiple DiskSpd threads at different IO block sizes and see what kind of behavior you’d get from your system, but in the end this continues to be just a synthetic test.

In data reducing arrays like the Pure Storage FlashArray, there’s nothing like testing with your actual workload, and looking at how your actual production IO patterns behave on your target system.

Distributed Replay

So, what’s a great tool for this? You might wonder. My favorite, by far, is Distributed Replay. It might not be the friendliest tool in the world (take note, Microsoft!) but it certainly is one of the best (if not _the_ best) tools for determining whether a target system is suitable for a given workload. Note how I didn’t specifically say that it’s great for testing storage subsystems; it is, but that’s one of the many scenarios where you would use Distributed Replay. You should use every time you do things like:

Upgrade to a new build of SQL Server, to make sure you won’t be suffering from issues like query optimizer plan regressions.

• Move to new hardware, or are working on consolidation of workloads onto new infrastructure.
• Plan to implement Always On Availability Groups, or even old fashioned Database Mirroring, to understand how your workload will behave under log replication.
• Want to test the effect of configuration settings on your workload, for example: forced parameterization, database compatibility mode, recovery modes, trace flags, etc.

Examples of DiskSpd

DiskSpd is used in a Microsoft Server environment with SQL Server installed. It can also be used in Azure for cloud-based environments. You first need to download it from Microsoft’s official DiskSpd Github repository. DiskSpd is a command-line tool, so you can store it in any directory on your server. You don’t even need to deal with DLLs!

Here is an example of using DiskSpd to test performance and send results to a test.txt file:

diskspd.exe -b4M -d90 -o32 -h -L -t56 -W -w0 c:\data.dat > test.txt

Here is an explanation of the parameters used here, but you can see a full list of DiskSpd parameters and your options in the DiskSpd Wiki.

-b4M – Use a 4 MB I/O size. You want to use an I/O size that simulates your own SQL Server performance. 

-d90 – Runs the test for 90 seconds. 

-o32 – Sets your queue for 32 requIn 2015,  ests. 

-h – This disables both hardware and software buffering to simulate a SQL Server environment. 

-L – Get disk latency metrics.

-t56 – Use 56 threads per file. Match this number to the number of cores when you are only testing with one file.

-W – Warm up the workload for 5 seconds.

-w0 – Test only reads, no writers. SQL Server writes are already high-speed, but we want to ensure that reads are optimized. 

c:\test.dat – The sample file being used for the test. 

> test.txt – Output file to capture results. 

When Should You Run DiskSpd?

DiskSpd is necessary when you want to ensure that your newly installed storage device won’t be a bottleneck. If you’re replacing SQLIO, then you use DiskSpd when you add or replace a storage disk on a SQL Server. You can use DiskSpd on all storage devices to identify if any one is a bottleneck, but you can also use it to identify issues with SQL Server storage. It can be used to decide if you need additional storage, or you can use it to identify if you need to replace an older disk.