Porting from SQLIO to DISKSPD: How to Test Disk Performance for SQL Server


Original post related to SQLIO took from brentozar blog.

diskspd -w100 -t8 -d60 -o32 -r -b8K -Sh -L -c20G C:\test.dat
diskspd -w0 -t8 -d60 -o32 -r -b8K -Sh -L -c20G C:\test.dat
diskspd -w100 -t8 -d60 -o32 -si -b64K -Sh -L -c20G C:\test.dat
diskspd -w0 -t8 -d60 -o32 -si -b64K -Sh -L -c20G C:\test.dat

What Do the DISKSPD Parameters Mean?

While we’re looking at that set of commands, here’s a quick breakdown:

  • -w100 and -w0: means we’re testing writes (100% writes) or reads (0% writes).
  • -t8 and -o32: means 8 threads with up to 32 outstanding requests at once.
    DISKSPD isn’t CPU-bound at all, and you can use more threads than you have processors.
    The more load we throw at storage, the faster it goes – to a point.
  • -d60: means the test will last 60 seconds
  • -b8K and -b64K: the size of our IO requests in kilobytes.
    SQL Server does a lot of random stuff in 8KB chunks,
    and we’re also testing sequential stuff in 64KB chunks.
  • -r and -s: random versus sequential access.
    Many queries jump around randomly in the database, whereas things like backups,
    bulk loads, and table scans generally work sequentially.
  • -L: measure latency statistics.
  • -Sh: equivalent -Suw, disable software caching, equivalent to FILE_FLAG_NO_BUFFERING and enable writethrough (no hardware write caching), equivalent to FILE_FLAG_WRITE_THROUGH.
  • -c20G: create a file of 20GB. Size can be stated in bytes or KiB/MiB/GiB/blocks.

You’ll notice I’m using a lot of general terms here about how some application patterns work.
Every SQL Server database has its own access patterns – random, sequential, big chunks, small pieces, and so on.
The four test lines you see above are shorthand examples of how some SQL Server IO patterns work.
We’re going to run a quick 4-part test first, and then come back to run much more in-depth tests shortly.