I’ve been working with a few customers recently that have been using the Microsoft DiskSPD Tool for doing some initial basic tests of their VM storage subsystem. DiskSPD is the tool that replaced SQLIO. Now although it isn’t a completely valid way to test a system that will be used for SQL Server, it can give an indication of how things will perform under certain conditions. But it’s basically just a synthetic IO generator like many others, so it is limited in terms of actual real world applicability to applications. Storage performance is but one factor to consider when designing a system to support a database, and max performance isn’t necessarily indicative of what is required for sustained workloads. But if you want to use it, and to avoid some traps that will invalidate your testing, here are some tips.

Firstly, just like SQLIO, DiskSPD by default will generate zeros if you don’t tell it otherwise. On some new storage systems this will overstate the peak performance numbers as they just acknowledge 0’s and it’s a NOOP at the back end. So be aware of this. In SQLIO we had to use Make-A-File.exe to generate a test file to remove the problem around zero filled data files. This is no longer required with DiskSPD.

To ensure you are always generating real data use the -Z <seed size> parameter. Such as -Z 1G for example. If you use -Z by itself then it will generate just zeros. If you pass it a value such as 1G, it’ll generate a repeating pattern of 1G of data.

To get the most out of your testing I would recommend that you test different IO patterns, different IO sizes, different amount of outstanding IO, and across a single and multiple drive letters.

Be aware that specifying too many outstanding IO operations will just overload the operating system queues and does not allow you to measure the underlying storage subsystem at all. Usually the SCSI driver will have a limited queue depth per drive of 32, potentially higher depending on if you are in a virtual or physical environment and what driver you are using. The Windows Storport driver has a queue depth limit per drive of 255. If you exceed any of these limits your performance suffers and your latency spikes.

SQL Server can drive lots of outstanding IO and can drive lots of queue depth. This is one of the reasons it’s recommended to split very large databases over multiple files and multiple drives. This tries to reduce the queue depth contention that would otherwise result.

For SQL Server typically I would test 8K IO (single page IO) and 64KB IO, and potentially large sequential IO of 512K or 1MB. This will allow you to cover your bases in terms of some of the common IO sizes that you will see in your database. It’s very common to see random 64KB IO sizes with SQL Server.

Here are some example commands with DiskSPD:

\Diskspd-v2.0.15\amd64fre\diskspd.exe -c5000G -d120 -r -w40 -t1 -o16 -b64k -h -L -Z1G f:\TestFile1.dat

\Diskspd-v2.0.15\amd64fre\diskspd.exe -c5000G -d120 -r -w50 -t1 -o16 -b64k -h -L -Z1G f:\TestFile1.dat

\Diskspd-v2.0.15\amd64fre\diskspd.exe -c5000G -d120 -r -w0 -t1 -o16 -b64k -h -L -Z1G f:\TestFile1.dat

The above generate a 5TB file (-c5000G), run for 120 seconds (-d120) use random IO (-r), and have different ratios of write IO (-w). We use a single thread per file (-t1) to generate the IO, and use 16 outstanding IO’s per thread (-o16). The IO size is set to 64kb in this case (-b64k). If you have a striped volume made up of a number of physical disk devices you can use more threads per file. But I’ve found that with a single file on a single drive using more than 1 thread will cause thread contention on the file and impact the validity of testing. Also be aware that the number of outstanding IO’s is per thread, so if you specify that to be too many again you will overload the queues in the OS and invalidate your test. We disable hardware and software caching with -h, and we measure latency statistics with -L.

When testing on large systems with KGroups > 1 and > 64 processors you will get a warning that DiskSPD can’t gather full CPU metrics. You will need to use another tool to gather CPU performance data, such as Perfmon.

When doing sequential tests with the -s option instead of -r you should not use multiple outstanding IO’s, as by definition that makes the test random and not sequential.

If you want to make your testing more realistic you might want to grab the details from using a tool such as procmon in Windows, or if in a VMware environment vscsiStats.

Final Word

As I wrote in my book “Virtualizing SQL Server with VMware: Doing IT Right (VMware Press 2014)“, it’s always a good idea to have a baseline, and to have valid application level tests of performance that take different factors into account, including CPU / RAM usage and NUMA topology impact etc. But to get an indication of storage performance, tools such as DiskSPD are useful, even though they do not give an accurate or full picture of performance that will impact applications. Once you have a baseline you can use it as a point of comparison against any changes or upgrades for your system, and when changing platforms. But to ensure validity of testing you should limit the number of variables between tests and data sets. In addition to using tools such as DiskSPD you should also consider using DB level benchmarks, such as HammerDB, DVD Store (Note: Latest DVD Store benchmark has moved to Github) and others that not only use the database engine, but simulate more of a real world application.

—

This post first appeared on the Long White Virtual Clouds blog at longwhiteclouds.com. By Michael Webster +. Copyright © 2012 – 2015 – IT Solutions 2000 Ltd and Michael Webster +. All rights reserved. Not to be reproduced for commercial purposes without written permission.